Access automation leaves Excel open which in turn locks 2nd automation attempts

Discussion in 'Microsoft Access' started by EagleOne@discussions.microsoft.com, Jun 25, 2008.

  1. Guest

    2003

    2003 up to date


    If I use Access Automation to export Access data to Excel files, there is
    always one last instance of Excel in memory unti I close Access.

    Normally not a problem, unless I wish to do another export.

    What then occurs is that Access Automation cannot open any Excel files
    attached to that instance of Excel. In addition, Access VBA procedures which
    veryify Error status do not run.

    In fact, if I start the Access Automation macro without previously closing
    all instances of Excel, fail in the same manner as above.

    In short, to be successful with Access to Excel Automation, I must first
    close all Excel files and Excel itself. Also, if certain procedures in the
    Automation macros did not complete (do to error status check), I must close
    and restart Access.

    Thus, both Access must be "rebooted" and Excel must be created 1st time in
    that session.

    BTW I am using the Dev Ashish code somewhat tailored as below:

    Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    myPath As String, myDate As String, myTime As String)
    '
    'Basic Code Courtesy of Dev Ashish
    '

    Dim objXL As Object
    Dim strWhat As String, boolXL As Boolean
    Dim MainSiteName As String
    Dim objActiveWkb As Object
    Dim Wks As Object
    Dim FullSheetRng As Object
    Dim OneColFilteredRng As Object
    Dim FullSheetFilteredRng As Object
    Dim ResizedRngToCopy As Object
    Dim myCell4OffHold As Object
    Dim myCell4Offset As Object
    Dim TempShtRng As Object
    Dim ResizedRngToDelete As Object
    Dim myCell As Object
    Dim ThisWorkbook As Object
    Dim myRowsToProcess As Long
    Dim myColumnsToProcess As Long
    Dim MaxRows As Long
    Dim MaxColumns As Long
    'Following necessary for Sort sequences in Excel/Access 2003
    Dim xlSortOnValues As Long
    Dim xlAscending As Long
    Dim xlSortNormal As Long
    xlSortOnValues = 0
    xlAscending = 1
    xlSortNormal = 0


    If fIsAppRunning("Excel") Then
    Set objXL = GetObject(, "Excel.Application")
    boolXL = False
    Else
    Set objXL = CreateObject("Excel.Application")
    boolXL = True
    End If

    With objXL.Application
    '.Visible = True
    .Visible = False
    'Open the Workbook
    .Workbooks.Open myPathFile
    '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    End With
    Set objActiveWkb = objXL.Application.ActiveWorkbook

    With objActiveWkb
    Set Wks = Nothing
    For Each Wks In .Worksheets

    Wks.Activate

    '
    '
    '
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    '
    '
    '

    Sheets("Temp").Paste
    objXL.CutCopyMode = False

    objXL.DisplayAlerts = False

    objXL.DisplayAlerts = True


    Next wks

    objActiveWkb.Close savechanges:=True

    objXL.Application.Quit
    Set objActiveWkb = Nothing: Set objXL = Nothing

    'Next section crude attempt to blow away extra Excel sessions

    If fIsAppRunning("Excel") Then
    Do While fIsAppRunning("Excel")
    On Error Resume Next
    Set objXL = GetObject(, "Excel.Application")
    On Error Resume Next
    Set objActiveWkb = objXL.Application.ActiveWorkbook
    Err.Clear
    If Not objActiveWkb Is Nothing Then
    objXL.Application.Visible = True
    Do While Not objActiveWkb Is Nothing
    On Error Resume Next
    Set objActiveWkb = objXL.Application.ActiveWorkbook
    On Error Resume Next
    objActiveWkb.Close savechanges:=True
    If objActiveWkb Is Nothing Then
    objXL.Application.Visible = False
    Exit Do
    End If
    Loop
    End If
    objXL.Application.Quit
    If objActiveWkb Is Nothing Then
    Set objActiveWkb = Nothing: Set objXL = Nothing
    Exit Do
    End If
    Loop
    End If

    End Sub


    Any suggestions as correct the challenge?

    TIA EagleOne
     
    , Jun 25, 2008
    #1
    1. Advertisements

  2. EagleOne,
    here is part of the code I use successfully to format files in excel.
    Below is the opening of excel and closing of excel part of the code.

    In your code, you first test to see if excel is already running.
    In your closing part of the code you also can check to see if you opened a
    new instance of excel.
    If you didn't need to open a new instance of excel, you don't need to close
    it.

    'opening excel here
    If fIsAppRunning("excel", False) Then 'yes it is running
    ' Get a reference to currently running Excel window
    Set objXLApp = GetObject(, "Excel.Application")
    blnExcelExists = True
    Else
    ' Excel is not currently running so create a new instance
    Set objXLApp = CreateObject("Excel.Application")
    End If


    'closing excel here
    'close the instance of Excel created by code
    If Not blnExcelExists Then
    objXLApp.Quit
    End If

    If Not objActiveWkb Is Nothing Then
    Set objActiveWkb = Nothing
    End If
    If Not objXLApp Is Nothing Then
    Set objXLApp = Nothing
    End If
    If Not db Is Nothing Then
    Set db = Nothing
    End If



    Jeanette Cunningham -- Melbourne Victoria Australia


    <> wrote in message
    news:...
    > 2003
    >
    > 2003 up to date
    >
    >
    > If I use Access Automation to export Access data to Excel files, there is
    > always one last instance of Excel in memory unti I close Access.
    >
    > Normally not a problem, unless I wish to do another export.
    >
    > What then occurs is that Access Automation cannot open any Excel files
    > attached to that instance of Excel. In addition, Access VBA procedures
    > which
    > veryify Error status do not run.
    >
    > In fact, if I start the Access Automation macro without previously closing
    > all instances of Excel, fail in the same manner as above.
    >
    > In short, to be successful with Access to Excel Automation, I must first
    > close all Excel files and Excel itself. Also, if certain procedures in
    > the
    > Automation macros did not complete (do to error status check), I must
    > close
    > and restart Access.
    >
    > Thus, both Access must be "rebooted" and Excel must be created 1st time in
    > that session.
    >
    > BTW I am using the Dev Ashish code somewhat tailored as below:
    >
    > Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    > myPath As String, myDate As String, myTime As String)
    > '
    > 'Basic Code Courtesy of Dev Ashish
    > '
    >
    > Dim objXL As Object
    > Dim strWhat As String, boolXL As Boolean
    > Dim MainSiteName As String
    > Dim objActiveWkb As Object
    > Dim Wks As Object
    > Dim FullSheetRng As Object
    > Dim OneColFilteredRng As Object
    > Dim FullSheetFilteredRng As Object
    > Dim ResizedRngToCopy As Object
    > Dim myCell4OffHold As Object
    > Dim myCell4Offset As Object
    > Dim TempShtRng As Object
    > Dim ResizedRngToDelete As Object
    > Dim myCell As Object
    > Dim ThisWorkbook As Object
    > Dim myRowsToProcess As Long
    > Dim myColumnsToProcess As Long
    > Dim MaxRows As Long
    > Dim MaxColumns As Long
    > 'Following necessary for Sort sequences in Excel/Access 2003
    > Dim xlSortOnValues As Long
    > Dim xlAscending As Long
    > Dim xlSortNormal As Long
    > xlSortOnValues = 0
    > xlAscending = 1
    > xlSortNormal = 0
    >
    >
    > If fIsAppRunning("Excel") Then
    > Set objXL = GetObject(, "Excel.Application")
    > boolXL = False
    > Else
    > Set objXL = CreateObject("Excel.Application")
    > boolXL = True
    > End If
    >
    > With objXL.Application
    > '.Visible = True
    > .Visible = False
    > 'Open the Workbook
    > .Workbooks.Open myPathFile
    > '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    > End With
    > Set objActiveWkb = objXL.Application.ActiveWorkbook
    >
    > With objActiveWkb
    > Set Wks = Nothing
    > For Each Wks In .Worksheets
    >
    > Wks.Activate
    >
    > '
    > '
    > '
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > '
    > '
    > '
    >
    > Sheets("Temp").Paste
    > objXL.CutCopyMode = False
    >
    > objXL.DisplayAlerts = False
    >
    > objXL.DisplayAlerts = True
    >
    >
    > Next wks
    >
    > objActiveWkb.Close savechanges:=True
    >
    > objXL.Application.Quit
    > Set objActiveWkb = Nothing: Set objXL = Nothing
    >
    > 'Next section crude attempt to blow away extra Excel sessions
    >
    > If fIsAppRunning("Excel") Then
    > Do While fIsAppRunning("Excel")
    > On Error Resume Next
    > Set objXL = GetObject(, "Excel.Application")
    > On Error Resume Next
    > Set objActiveWkb = objXL.Application.ActiveWorkbook
    > Err.Clear
    > If Not objActiveWkb Is Nothing Then
    > objXL.Application.Visible = True
    > Do While Not objActiveWkb Is Nothing
    > On Error Resume Next
    > Set objActiveWkb = objXL.Application.ActiveWorkbook
    > On Error Resume Next
    > objActiveWkb.Close savechanges:=True
    > If objActiveWkb Is Nothing Then
    > objXL.Application.Visible = False
    > Exit Do
    > End If
    > Loop
    > End If
    > objXL.Application.Quit
    > If objActiveWkb Is Nothing Then
    > Set objActiveWkb = Nothing: Set objXL = Nothing
    > Exit Do
    > End If
    > Loop
    > End If
    >
    > End Sub
    >
    >
    > Any suggestions as correct the challenge?
    >
    > TIA EagleOne
     
    Jeanette Cunningham, Jun 26, 2008
    #2
    1. Advertisements

  3. Re: Access automation leaves Excel open which in turn locks 2nd au

    Thank you Jeannette,

    I wish I knew why, the Access MVP's avoided this challenge for the last two
    days. One actually started to - but dropped the ball. A challenge like this
    would have been answered 5 times in the Excel newsgroup.

    Again thanks!


    "Jeanette Cunningham" wrote:

    > EagleOne,
    > here is part of the code I use successfully to format files in excel.
    > Below is the opening of excel and closing of excel part of the code.
    >
    > In your code, you first test to see if excel is already running.
    > In your closing part of the code you also can check to see if you opened a
    > new instance of excel.
    > If you didn't need to open a new instance of excel, you don't need to close
    > it.
    >
    > 'opening excel here
    > If fIsAppRunning("excel", False) Then 'yes it is running
    > ' Get a reference to currently running Excel window
    > Set objXLApp = GetObject(, "Excel.Application")
    > blnExcelExists = True
    > Else
    > ' Excel is not currently running so create a new instance
    > Set objXLApp = CreateObject("Excel.Application")
    > End If
    >
    >
    > 'closing excel here
    > 'close the instance of Excel created by code
    > If Not blnExcelExists Then
    > objXLApp.Quit
    > End If
    >
    > If Not objActiveWkb Is Nothing Then
    > Set objActiveWkb = Nothing
    > End If
    > If Not objXLApp Is Nothing Then
    > Set objXLApp = Nothing
    > End If
    > If Not db Is Nothing Then
    > Set db = Nothing
    > End If
    >
    >
    >
    > Jeanette Cunningham -- Melbourne Victoria Australia
    >
    >
    > <> wrote in message
    > news:...
    > > 2003
    > >
    > > 2003 up to date
    > >
    > >
    > > If I use Access Automation to export Access data to Excel files, there is
    > > always one last instance of Excel in memory unti I close Access.
    > >
    > > Normally not a problem, unless I wish to do another export.
    > >
    > > What then occurs is that Access Automation cannot open any Excel files
    > > attached to that instance of Excel. In addition, Access VBA procedures
    > > which
    > > veryify Error status do not run.
    > >
    > > In fact, if I start the Access Automation macro without previously closing
    > > all instances of Excel, fail in the same manner as above.
    > >
    > > In short, to be successful with Access to Excel Automation, I must first
    > > close all Excel files and Excel itself. Also, if certain procedures in
    > > the
    > > Automation macros did not complete (do to error status check), I must
    > > close
    > > and restart Access.
    > >
    > > Thus, both Access must be "rebooted" and Excel must be created 1st time in
    > > that session.
    > >
    > > BTW I am using the Dev Ashish code somewhat tailored as below:
    > >
    > > Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    > > myPath As String, myDate As String, myTime As String)
    > > '
    > > 'Basic Code Courtesy of Dev Ashish
    > > '
    > >
    > > Dim objXL As Object
    > > Dim strWhat As String, boolXL As Boolean
    > > Dim MainSiteName As String
    > > Dim objActiveWkb As Object
    > > Dim Wks As Object
    > > Dim FullSheetRng As Object
    > > Dim OneColFilteredRng As Object
    > > Dim FullSheetFilteredRng As Object
    > > Dim ResizedRngToCopy As Object
    > > Dim myCell4OffHold As Object
    > > Dim myCell4Offset As Object
    > > Dim TempShtRng As Object
    > > Dim ResizedRngToDelete As Object
    > > Dim myCell As Object
    > > Dim ThisWorkbook As Object
    > > Dim myRowsToProcess As Long
    > > Dim myColumnsToProcess As Long
    > > Dim MaxRows As Long
    > > Dim MaxColumns As Long
    > > 'Following necessary for Sort sequences in Excel/Access 2003
    > > Dim xlSortOnValues As Long
    > > Dim xlAscending As Long
    > > Dim xlSortNormal As Long
    > > xlSortOnValues = 0
    > > xlAscending = 1
    > > xlSortNormal = 0
    > >
    > >
    > > If fIsAppRunning("Excel") Then
    > > Set objXL = GetObject(, "Excel.Application")
    > > boolXL = False
    > > Else
    > > Set objXL = CreateObject("Excel.Application")
    > > boolXL = True
    > > End If
    > >
    > > With objXL.Application
    > > '.Visible = True
    > > .Visible = False
    > > 'Open the Workbook
    > > .Workbooks.Open myPathFile
    > > '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    > > End With
    > > Set objActiveWkb = objXL.Application.ActiveWorkbook
    > >
    > > With objActiveWkb
    > > Set Wks = Nothing
    > > For Each Wks In .Worksheets
    > >
    > > Wks.Activate
    > >
    > > '
    > > '
    > > '
    > > 'Many VBA lines of w/s formating
    > > 'Many VBA lines of w/s formating
    > > 'Many VBA lines of w/s formating
    > > 'Many VBA lines of w/s formating
    > > 'Many VBA lines of w/s formating
    > > 'Many VBA lines of w/s formating
    > > 'Many VBA lines of w/s formating
    > > '
    > > '
    > > '
    > >
    > > Sheets("Temp").Paste
    > > objXL.CutCopyMode = False
    > >
    > > objXL.DisplayAlerts = False
    > >
    > > objXL.DisplayAlerts = True
    > >
    > >
    > > Next wks
    > >
    > > objActiveWkb.Close savechanges:=True
    > >
    > > objXL.Application.Quit
    > > Set objActiveWkb = Nothing: Set objXL = Nothing
    > >
    > > 'Next section crude attempt to blow away extra Excel sessions
    > >
    > > If fIsAppRunning("Excel") Then
    > > Do While fIsAppRunning("Excel")
    > > On Error Resume Next
    > > Set objXL = GetObject(, "Excel.Application")
    > > On Error Resume Next
    > > Set objActiveWkb = objXL.Application.ActiveWorkbook
    > > Err.Clear
    > > If Not objActiveWkb Is Nothing Then
    > > objXL.Application.Visible = True
    > > Do While Not objActiveWkb Is Nothing
    > > On Error Resume Next
    > > Set objActiveWkb = objXL.Application.ActiveWorkbook
    > > On Error Resume Next
    > > objActiveWkb.Close savechanges:=True
    > > If objActiveWkb Is Nothing Then
    > > objXL.Application.Visible = False
    > > Exit Do
    > > End If
    > > Loop
    > > End If
    > > objXL.Application.Quit
    > > If objActiveWkb Is Nothing Then
    > > Set objActiveWkb = Nothing: Set objXL = Nothing
    > > Exit Do
    > > End If
    > > Loop
    > > End If
    > >
    > > End Sub
    > >
    > >
    > > Any suggestions as correct the challenge?
    > >
    > > TIA EagleOne

    >
    >
    >
     
    EagleOne@microsoftdiscussiongroups, Jun 26, 2008
    #3
  4. Re: Access automation leaves Excel open which in turn locks 2nd au

    Glad I could help.
    When we volunteers answer questions, it is easier to pick something that we
    either know off by heart, or something that we are working on at the moment
    and are very familiar with. Many of us also have limited time, so just do a
    couple of questions. Many MVP's also answer questions on the other access
    forums around.


    Jeanette Cunningham -- Melbourne Victoria Australia

    "EagleOne@microsoftdiscussiongroups"
    <> wrote in
    message news:...
    > Thank you Jeannette,
    >
    > I wish I knew why, the Access MVP's avoided this challenge for the last
    > two
    > days. One actually started to - but dropped the ball. A challenge like
    > this
    > would have been answered 5 times in the Excel newsgroup.
    >
    > Again thanks!
    >
    >
    > "Jeanette Cunningham" wrote:
    >
    >> EagleOne,
    >> here is part of the code I use successfully to format files in excel.
    >> Below is the opening of excel and closing of excel part of the code.
    >>
    >> In your code, you first test to see if excel is already running.
    >> In your closing part of the code you also can check to see if you opened
    >> a
    >> new instance of excel.
    >> If you didn't need to open a new instance of excel, you don't need to
    >> close
    >> it.
    >>
    >> 'opening excel here
    >> If fIsAppRunning("excel", False) Then 'yes it is running
    >> ' Get a reference to currently running Excel window
    >> Set objXLApp = GetObject(, "Excel.Application")
    >> blnExcelExists = True
    >> Else
    >> ' Excel is not currently running so create a new instance
    >> Set objXLApp = CreateObject("Excel.Application")
    >> End If
    >>
    >>
    >> 'closing excel here
    >> 'close the instance of Excel created by code
    >> If Not blnExcelExists Then
    >> objXLApp.Quit
    >> End If
    >>
    >> If Not objActiveWkb Is Nothing Then
    >> Set objActiveWkb = Nothing
    >> End If
    >> If Not objXLApp Is Nothing Then
    >> Set objXLApp = Nothing
    >> End If
    >> If Not db Is Nothing Then
    >> Set db = Nothing
    >> End If
    >>
    >>
    >>
    >> Jeanette Cunningham -- Melbourne Victoria Australia
    >>
    >>
    >> <> wrote in message
    >> news:...
    >> > 2003
    >> >
    >> > 2003 up to date
    >> >
    >> >
    >> > If I use Access Automation to export Access data to Excel files, there
    >> > is
    >> > always one last instance of Excel in memory unti I close Access.
    >> >
    >> > Normally not a problem, unless I wish to do another export.
    >> >
    >> > What then occurs is that Access Automation cannot open any Excel files
    >> > attached to that instance of Excel. In addition, Access VBA procedures
    >> > which
    >> > veryify Error status do not run.
    >> >
    >> > In fact, if I start the Access Automation macro without previously
    >> > closing
    >> > all instances of Excel, fail in the same manner as above.
    >> >
    >> > In short, to be successful with Access to Excel Automation, I must
    >> > first
    >> > close all Excel files and Excel itself. Also, if certain procedures in
    >> > the
    >> > Automation macros did not complete (do to error status check), I must
    >> > close
    >> > and restart Access.
    >> >
    >> > Thus, both Access must be "rebooted" and Excel must be created 1st time
    >> > in
    >> > that session.
    >> >
    >> > BTW I am using the Dev Ashish code somewhat tailored as below:
    >> >
    >> > Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    >> > myPath As String, myDate As String, myTime As String)
    >> > '
    >> > 'Basic Code Courtesy of Dev Ashish
    >> > '
    >> >
    >> > Dim objXL As Object
    >> > Dim strWhat As String, boolXL As Boolean
    >> > Dim MainSiteName As String
    >> > Dim objActiveWkb As Object
    >> > Dim Wks As Object
    >> > Dim FullSheetRng As Object
    >> > Dim OneColFilteredRng As Object
    >> > Dim FullSheetFilteredRng As Object
    >> > Dim ResizedRngToCopy As Object
    >> > Dim myCell4OffHold As Object
    >> > Dim myCell4Offset As Object
    >> > Dim TempShtRng As Object
    >> > Dim ResizedRngToDelete As Object
    >> > Dim myCell As Object
    >> > Dim ThisWorkbook As Object
    >> > Dim myRowsToProcess As Long
    >> > Dim myColumnsToProcess As Long
    >> > Dim MaxRows As Long
    >> > Dim MaxColumns As Long
    >> > 'Following necessary for Sort sequences in Excel/Access 2003
    >> > Dim xlSortOnValues As Long
    >> > Dim xlAscending As Long
    >> > Dim xlSortNormal As Long
    >> > xlSortOnValues = 0
    >> > xlAscending = 1
    >> > xlSortNormal = 0
    >> >
    >> >
    >> > If fIsAppRunning("Excel") Then
    >> > Set objXL = GetObject(, "Excel.Application")
    >> > boolXL = False
    >> > Else
    >> > Set objXL = CreateObject("Excel.Application")
    >> > boolXL = True
    >> > End If
    >> >
    >> > With objXL.Application
    >> > '.Visible = True
    >> > .Visible = False
    >> > 'Open the Workbook
    >> > .Workbooks.Open myPathFile
    >> > '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    >> > End With
    >> > Set objActiveWkb = objXL.Application.ActiveWorkbook
    >> >
    >> > With objActiveWkb
    >> > Set Wks = Nothing
    >> > For Each Wks In .Worksheets
    >> >
    >> > Wks.Activate
    >> >
    >> > '
    >> > '
    >> > '
    >> > 'Many VBA lines of w/s formating
    >> > 'Many VBA lines of w/s formating
    >> > 'Many VBA lines of w/s formating
    >> > 'Many VBA lines of w/s formating
    >> > 'Many VBA lines of w/s formating
    >> > 'Many VBA lines of w/s formating
    >> > 'Many VBA lines of w/s formating
    >> > '
    >> > '
    >> > '
    >> >
    >> > Sheets("Temp").Paste
    >> > objXL.CutCopyMode = False
    >> >
    >> > objXL.DisplayAlerts = False
    >> >
    >> > objXL.DisplayAlerts = True
    >> >
    >> >
    >> > Next wks
    >> >
    >> > objActiveWkb.Close savechanges:=True
    >> >
    >> > objXL.Application.Quit
    >> > Set objActiveWkb = Nothing: Set objXL = Nothing
    >> >
    >> > 'Next section crude attempt to blow away extra Excel sessions
    >> >
    >> > If fIsAppRunning("Excel") Then
    >> > Do While fIsAppRunning("Excel")
    >> > On Error Resume Next
    >> > Set objXL = GetObject(, "Excel.Application")
    >> > On Error Resume Next
    >> > Set objActiveWkb = objXL.Application.ActiveWorkbook
    >> > Err.Clear
    >> > If Not objActiveWkb Is Nothing Then
    >> > objXL.Application.Visible = True
    >> > Do While Not objActiveWkb Is Nothing
    >> > On Error Resume Next
    >> > Set objActiveWkb = objXL.Application.ActiveWorkbook
    >> > On Error Resume Next
    >> > objActiveWkb.Close savechanges:=True
    >> > If objActiveWkb Is Nothing Then
    >> > objXL.Application.Visible = False
    >> > Exit Do
    >> > End If
    >> > Loop
    >> > End If
    >> > objXL.Application.Quit
    >> > If objActiveWkb Is Nothing Then
    >> > Set objActiveWkb = Nothing: Set objXL = Nothing
    >> > Exit Do
    >> > End If
    >> > Loop
    >> > End If
    >> >
    >> > End Sub
    >> >
    >> >
    >> > Any suggestions as correct the challenge?
    >> >
    >> > TIA EagleOne

    >>
    >>
    >>
     
    Jeanette Cunningham, Jun 27, 2008
    #4
  5. Guest

    Jeanette,

    FYI, your approach/logic is definitely leaves less "open instances" of Excel.

    That said, there is still one remaining "phantom" instance held open by Access.

    The reason this is important, is if I run my Format Excel worksheets macro via Access
    automation a 2nd time in the same instance of Access (where there is still one remaining "phantom"
    instance held open by Access) the formatting fails because Excel is still holding onto the objects
    or some other object-related issue.

    The reason I say this is I can "see" the instance of Excel in Task Manager. If I do not close the
    1st Access instance (at which point the "phantom" instance of Excel disappears from Task Manager)
    and then re-open Access, the Excel formatting fails.

    I believe that the Access programmers need to figure out how to provide end users a VBA command to
    kill all instances of Excel opened via Access automation.

    There is no doubt in my mind that this Access automation bug is exactly why the Access Automation
    MVP's ignored this issue - direct or indirect pressure from MS to not give the bug legs.

    EagleOne

    "Jeanette Cunningham" <> wrote:

    >EagleOne,
    >here is part of the code I use successfully to format files in excel.
    >Below is the opening of excel and closing of excel part of the code.
    >
    >In your code, you first test to see if excel is already running.
    >In your closing part of the code you also can check to see if you opened a
    >new instance of excel.
    >If you didn't need to open a new instance of excel, you don't need to close
    >it.
    >
    >'opening excel here
    > If fIsAppRunning("excel", False) Then 'yes it is running
    > ' Get a reference to currently running Excel window
    > Set objXLApp = GetObject(, "Excel.Application")
    > blnExcelExists = True
    > Else
    > ' Excel is not currently running so create a new instance
    > Set objXLApp = CreateObject("Excel.Application")
    > End If
    >
    >
    >'closing excel here
    > 'close the instance of Excel created by code
    > If Not blnExcelExists Then
    > objXLApp.Quit
    > End If
    >
    > If Not objActiveWkb Is Nothing Then
    > Set objActiveWkb = Nothing
    > End If
    > If Not objXLApp Is Nothing Then
    > Set objXLApp = Nothing
    > End If
    > If Not db Is Nothing Then
    > Set db = Nothing
    > End If
    >
    >
    >
    >Jeanette Cunningham -- Melbourne Victoria Australia
    >
    >
    ><> wrote in message
    >news:...
    >> 2003
    >>
    >> 2003 up to date
    >>
    >>
    >> If I use Access Automation to export Access data to Excel files, there is
    >> always one last instance of Excel in memory unti I close Access.
    >>
    >> Normally not a problem, unless I wish to do another export.
    >>
    >> What then occurs is that Access Automation cannot open any Excel files
    >> attached to that instance of Excel. In addition, Access VBA procedures
    >> which
    >> veryify Error status do not run.
    >>
    >> In fact, if I start the Access Automation macro without previously closing
    >> all instances of Excel, fail in the same manner as above.
    >>
    >> In short, to be successful with Access to Excel Automation, I must first
    >> close all Excel files and Excel itself. Also, if certain procedures in
    >> the
    >> Automation macros did not complete (do to error status check), I must
    >> close
    >> and restart Access.
    >>
    >> Thus, both Access must be "rebooted" and Excel must be created 1st time in
    >> that session.
    >>
    >> BTW I am using the Dev Ashish code somewhat tailored as below:
    >>
    >> Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    >> myPath As String, myDate As String, myTime As String)
    >> '
    >> 'Basic Code Courtesy of Dev Ashish
    >> '
    >>
    >> Dim objXL As Object
    >> Dim strWhat As String, boolXL As Boolean
    >> Dim MainSiteName As String
    >> Dim objActiveWkb As Object
    >> Dim Wks As Object
    >> Dim FullSheetRng As Object
    >> Dim OneColFilteredRng As Object
    >> Dim FullSheetFilteredRng As Object
    >> Dim ResizedRngToCopy As Object
    >> Dim myCell4OffHold As Object
    >> Dim myCell4Offset As Object
    >> Dim TempShtRng As Object
    >> Dim ResizedRngToDelete As Object
    >> Dim myCell As Object
    >> Dim ThisWorkbook As Object
    >> Dim myRowsToProcess As Long
    >> Dim myColumnsToProcess As Long
    >> Dim MaxRows As Long
    >> Dim MaxColumns As Long
    >> 'Following necessary for Sort sequences in Excel/Access 2003
    >> Dim xlSortOnValues As Long
    >> Dim xlAscending As Long
    >> Dim xlSortNormal As Long
    >> xlSortOnValues = 0
    >> xlAscending = 1
    >> xlSortNormal = 0
    >>
    >>
    >> If fIsAppRunning("Excel") Then
    >> Set objXL = GetObject(, "Excel.Application")
    >> boolXL = False
    >> Else
    >> Set objXL = CreateObject("Excel.Application")
    >> boolXL = True
    >> End If
    >>
    >> With objXL.Application
    >> '.Visible = True
    >> .Visible = False
    >> 'Open the Workbook
    >> .Workbooks.Open myPathFile
    >> '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    >> End With
    >> Set objActiveWkb = objXL.Application.ActiveWorkbook
    >>
    >> With objActiveWkb
    >> Set Wks = Nothing
    >> For Each Wks In .Worksheets
    >>
    >> Wks.Activate
    >>
    >> '
    >> '
    >> '
    >> 'Many VBA lines of w/s formating
    >> 'Many VBA lines of w/s formating
    >> 'Many VBA lines of w/s formating
    >> 'Many VBA lines of w/s formating
    >> 'Many VBA lines of w/s formating
    >> 'Many VBA lines of w/s formating
    >> 'Many VBA lines of w/s formating
    >> '
    >> '
    >> '
    >>
    >> Sheets("Temp").Paste
    >> objXL.CutCopyMode = False
    >>
    >> objXL.DisplayAlerts = False
    >>
    >> objXL.DisplayAlerts = True
    >>
    >>
    >> Next wks
    >>
    >> objActiveWkb.Close savechanges:=True
    >>
    >> objXL.Application.Quit
    >> Set objActiveWkb = Nothing: Set objXL = Nothing
    >>
    >> 'Next section crude attempt to blow away extra Excel sessions
    >>
    >> If fIsAppRunning("Excel") Then
    >> Do While fIsAppRunning("Excel")
    >> On Error Resume Next
    >> Set objXL = GetObject(, "Excel.Application")
    >> On Error Resume Next
    >> Set objActiveWkb = objXL.Application.ActiveWorkbook
    >> Err.Clear
    >> If Not objActiveWkb Is Nothing Then
    >> objXL.Application.Visible = True
    >> Do While Not objActiveWkb Is Nothing
    >> On Error Resume Next
    >> Set objActiveWkb = objXL.Application.ActiveWorkbook
    >> On Error Resume Next
    >> objActiveWkb.Close savechanges:=True
    >> If objActiveWkb Is Nothing Then
    >> objXL.Application.Visible = False
    >> Exit Do
    >> End If
    >> Loop
    >> End If
    >> objXL.Application.Quit
    >> If objActiveWkb Is Nothing Then
    >> Set objActiveWkb = Nothing: Set objXL = Nothing
    >> Exit Do
    >> End If
    >> Loop
    >> End If
    >>
    >> End Sub
    >>
    >>
    >> Any suggestions as correct the challenge?
    >>
    >> TIA EagleOne

    >
     
    , Jun 28, 2008
    #5
  6. EagleOne,
    I have seen this problem when using either OutputTo or TransferSpreadsheet
    to export to excel then using automation to format the excel file.
    If you are using the above process, finish the export completely before
    starting any code that uses automation to format the exported file.

    The other usual reason for this problem, is related to how you structure the
    code that uses automation to format the exported file.
    This can be caused by closing the instance of excel, then accidentally
    opening it again by referencing something from the automation code.

    If you post all your code that performs the export and automation, we can
    probably spot where the problem is.


    Jeanette Cunningham -- Melbourne Victoria Australia


    <> wrote in message
    news:...
    > Jeanette,
    >
    > FYI, your approach/logic is definitely leaves less "open instances" of
    > Excel.
    >
    > That said, there is still one remaining "phantom" instance held open by
    > Access.
    >
    > The reason this is important, is if I run my Format Excel worksheets macro
    > via Access
    > automation a 2nd time in the same instance of Access (where there is still
    > one remaining "phantom"
    > instance held open by Access) the formatting fails because Excel is still
    > holding onto the objects
    > or some other object-related issue.
    >
    > The reason I say this is I can "see" the instance of Excel in Task
    > Manager. If I do not close the
    > 1st Access instance (at which point the "phantom" instance of Excel
    > disappears from Task Manager)
    > and then re-open Access, the Excel formatting fails.
    >
    > I believe that the Access programmers need to figure out how to provide
    > end users a VBA command to
    > kill all instances of Excel opened via Access automation.
    >
    > There is no doubt in my mind that this Access automation bug is exactly
    > why the Access Automation
    > MVP's ignored this issue - direct or indirect pressure from MS to not give
    > the bug legs.
    >
    > EagleOne
    >
    > "Jeanette Cunningham" <> wrote:
    >
    >>EagleOne,
    >>here is part of the code I use successfully to format files in excel.
    >>Below is the opening of excel and closing of excel part of the code.
    >>
    >>In your code, you first test to see if excel is already running.
    >>In your closing part of the code you also can check to see if you opened
    >>a
    >>new instance of excel.
    >>If you didn't need to open a new instance of excel, you don't need to
    >>close
    >>it.
    >>
    >>'opening excel here
    >> If fIsAppRunning("excel", False) Then 'yes it is running
    >> ' Get a reference to currently running Excel window
    >> Set objXLApp = GetObject(, "Excel.Application")
    >> blnExcelExists = True
    >> Else
    >> ' Excel is not currently running so create a new instance
    >> Set objXLApp = CreateObject("Excel.Application")
    >> End If
    >>
    >>
    >>'closing excel here
    >> 'close the instance of Excel created by code
    >> If Not blnExcelExists Then
    >> objXLApp.Quit
    >> End If
    >>
    >> If Not objActiveWkb Is Nothing Then
    >> Set objActiveWkb = Nothing
    >> End If
    >> If Not objXLApp Is Nothing Then
    >> Set objXLApp = Nothing
    >> End If
    >> If Not db Is Nothing Then
    >> Set db = Nothing
    >> End If
    >>
    >>
    >>
    >>Jeanette Cunningham -- Melbourne Victoria Australia
    >>
    >>
    >><> wrote in message
    >>news:...
    >>> 2003
    >>>
    >>> 2003 up to date
    >>>
    >>>
    >>> If I use Access Automation to export Access data to Excel files, there
    >>> is
    >>> always one last instance of Excel in memory unti I close Access.
    >>>
    >>> Normally not a problem, unless I wish to do another export.
    >>>
    >>> What then occurs is that Access Automation cannot open any Excel files
    >>> attached to that instance of Excel. In addition, Access VBA procedures
    >>> which
    >>> veryify Error status do not run.
    >>>
    >>> In fact, if I start the Access Automation macro without previously
    >>> closing
    >>> all instances of Excel, fail in the same manner as above.
    >>>
    >>> In short, to be successful with Access to Excel Automation, I must first
    >>> close all Excel files and Excel itself. Also, if certain procedures in
    >>> the
    >>> Automation macros did not complete (do to error status check), I must
    >>> close
    >>> and restart Access.
    >>>
    >>> Thus, both Access must be "rebooted" and Excel must be created 1st time
    >>> in
    >>> that session.
    >>>
    >>> BTW I am using the Dev Ashish code somewhat tailored as below:
    >>>
    >>> Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    >>> myPath As String, myDate As String, myTime As String)
    >>> '
    >>> 'Basic Code Courtesy of Dev Ashish
    >>> '
    >>>
    >>> Dim objXL As Object
    >>> Dim strWhat As String, boolXL As Boolean
    >>> Dim MainSiteName As String
    >>> Dim objActiveWkb As Object
    >>> Dim Wks As Object
    >>> Dim FullSheetRng As Object
    >>> Dim OneColFilteredRng As Object
    >>> Dim FullSheetFilteredRng As Object
    >>> Dim ResizedRngToCopy As Object
    >>> Dim myCell4OffHold As Object
    >>> Dim myCell4Offset As Object
    >>> Dim TempShtRng As Object
    >>> Dim ResizedRngToDelete As Object
    >>> Dim myCell As Object
    >>> Dim ThisWorkbook As Object
    >>> Dim myRowsToProcess As Long
    >>> Dim myColumnsToProcess As Long
    >>> Dim MaxRows As Long
    >>> Dim MaxColumns As Long
    >>> 'Following necessary for Sort sequences in Excel/Access 2003
    >>> Dim xlSortOnValues As Long
    >>> Dim xlAscending As Long
    >>> Dim xlSortNormal As Long
    >>> xlSortOnValues = 0
    >>> xlAscending = 1
    >>> xlSortNormal = 0
    >>>
    >>>
    >>> If fIsAppRunning("Excel") Then
    >>> Set objXL = GetObject(, "Excel.Application")
    >>> boolXL = False
    >>> Else
    >>> Set objXL = CreateObject("Excel.Application")
    >>> boolXL = True
    >>> End If
    >>>
    >>> With objXL.Application
    >>> '.Visible = True
    >>> .Visible = False
    >>> 'Open the Workbook
    >>> .Workbooks.Open myPathFile
    >>> '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    >>> End With
    >>> Set objActiveWkb = objXL.Application.ActiveWorkbook
    >>>
    >>> With objActiveWkb
    >>> Set Wks = Nothing
    >>> For Each Wks In .Worksheets
    >>>
    >>> Wks.Activate
    >>>
    >>> '
    >>> '
    >>> '
    >>> 'Many VBA lines of w/s formating
    >>> 'Many VBA lines of w/s formating
    >>> 'Many VBA lines of w/s formating
    >>> 'Many VBA lines of w/s formating
    >>> 'Many VBA lines of w/s formating
    >>> 'Many VBA lines of w/s formating
    >>> 'Many VBA lines of w/s formating
    >>> '
    >>> '
    >>> '
    >>>
    >>> Sheets("Temp").Paste
    >>> objXL.CutCopyMode = False
    >>>
    >>> objXL.DisplayAlerts = False
    >>>
    >>> objXL.DisplayAlerts = True
    >>>
    >>>
    >>> Next wks
    >>>
    >>> objActiveWkb.Close savechanges:=True
    >>>
    >>> objXL.Application.Quit
    >>> Set objActiveWkb = Nothing: Set objXL = Nothing
    >>>
    >>> 'Next section crude attempt to blow away extra Excel sessions
    >>>
    >>> If fIsAppRunning("Excel") Then
    >>> Do While fIsAppRunning("Excel")
    >>> On Error Resume Next
    >>> Set objXL = GetObject(, "Excel.Application")
    >>> On Error Resume Next
    >>> Set objActiveWkb = objXL.Application.ActiveWorkbook
    >>> Err.Clear
    >>> If Not objActiveWkb Is Nothing Then
    >>> objXL.Application.Visible = True
    >>> Do While Not objActiveWkb Is Nothing
    >>> On Error Resume Next
    >>> Set objActiveWkb = objXL.Application.ActiveWorkbook
    >>> On Error Resume Next
    >>> objActiveWkb.Close savechanges:=True
    >>> If objActiveWkb Is Nothing Then
    >>> objXL.Application.Visible = False
    >>> Exit Do
    >>> End If
    >>> Loop
    >>> End If
    >>> objXL.Application.Quit
    >>> If objActiveWkb Is Nothing Then
    >>> Set objActiveWkb = Nothing: Set objXL = Nothing
    >>> Exit Do
    >>> End If
    >>> Loop
    >>> End If
    >>>
    >>> End Sub
    >>>
    >>>
    >>> Any suggestions as correct the challenge?
    >>>
    >>> TIA EagleOne

    >>
     
    Jeanette Cunningham, Jun 28, 2008
    #6
  7. Guest

    Thanks for the offer.

    Note that I have included the original code (before changes to your method)

    Based upon your comments, I guess that my error is the sequence that I use in the 1st Sub next.


    Sub Export_Files_Macro()
    '
    Dim myPath As String
    Dim myDate As String
    Dim myTime As String
    Dim myFileName As String
    Dim myPathFile As String

    myPath = CurrentProject.Path & "\"
    myDate = Replace(Date, "/", "-")
    myTime = Format(Time(), "hhmm")
    myFileName = "ABC_AAASITE_TBL " & myDate & " " & myTime & ".xls"
    myPathFile = myPath & myFileName
    On Error Resume Next ' in case that the file does not exist
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
    "ABC_AAASITE_TBL", myPath & myFileName, True
    If Not Err.Number > 0 Then
    Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
    End If
    Err.Clear
    myFileName = "XYZ_AAASITE_TBL " & myDate & " " & myTime & ".xls"
    myPathFile = myPath & myFileName
    On Error Resume Next
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
    "XYZ_AAASITE_TBL", myPath & myFileName, True
    If Not Err.Number > 0 Then
    Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
    End If
    Err.Clear

    End Sub

    Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    myPath As String, myDate As String, myTime As String)
    '
    'Basic Code Courtesy of Dev Ashish
    '

    Dim objXL As Object
    Dim strWhat As String, boolXL As Boolean
    Dim MainSiteName As String
    Dim objActiveWkb As Object
    Dim Wks As Object
    Dim FullSheetRng As Object
    Dim OneColFilteredRng As Object
    Dim FullSheetFilteredRng As Object
    Dim ResizedRngToCopy As Object
    Dim myCell4OffHold As Object
    Dim myCell4Offset As Object
    Dim TempShtRng As Object
    Dim ResizedRngToDelete As Object
    Dim myCell As Object
    Dim ThisWorkbook As Object
    Dim myRowsToProcess As Long
    Dim myColumnsToProcess As Long
    Dim MaxRows As Long
    Dim MaxColumns As Long
    'Following necessary for Sort sequences in Excel/Access 2003
    Dim xlSortOnValues As Long
    Dim xlAscending As Long
    Dim xlSortNormal As Long
    xlSortOnValues = 0
    xlAscending = 1
    xlSortNormal = 0


    If fIsAppRunning("Excel") Then
    Set objXL = GetObject(, "Excel.Application")
    boolXL = False
    Else
    Set objXL = CreateObject("Excel.Application")
    boolXL = True
    End If

    With objXL.Application
    '.Visible = True
    .Visible = False
    'Open the Workbook
    .Workbooks.Open myPathFile
    '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    End With
    Set objActiveWkb = objXL.Application.ActiveWorkbook

    With objActiveWkb
    Set Wks = Nothing
    For Each Wks In .Worksheets

    Wks.Activate

    '
    '
    '
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    '
    '
    '

    Sheets("Temp").Paste
    objXL.CutCopyMode = False

    objXL.DisplayAlerts = False

    objXL.DisplayAlerts = True


    Next wks

    objActiveWkb.Close savechanges:=True

    objXL.Application.Quit
    Set objActiveWkb = Nothing: Set objXL = Nothing

    'Next section crude attempt to blow away extra Excel sessions

    If fIsAppRunning("Excel") Then
    Do While fIsAppRunning("Excel")
    On Error Resume Next
    Set objXL = GetObject(, "Excel.Application")
    On Error Resume Next
    Set objActiveWkb = objXL.Application.ActiveWorkbook
    Err.Clear
    If Not objActiveWkb Is Nothing Then
    objXL.Application.Visible = True
    Do While Not objActiveWkb Is Nothing
    On Error Resume Next
    Set objActiveWkb = objXL.Application.ActiveWorkbook
    On Error Resume Next
    objActiveWkb.Close savechanges:=True
    If objActiveWkb Is Nothing Then
    objXL.Application.Visible = False
    Exit Do
    End If
    Loop
    End If
    objXL.Application.Quit
    If objActiveWkb Is Nothing Then
    Set objActiveWkb = Nothing: Set objXL = Nothing
    Exit Do
    End If
    Loop
    End If

    End Sub


    "Jeanette Cunningham" <> wrote:

    >EagleOne,
    >I have seen this problem when using either OutputTo or TransferSpreadsheet
    >to export to excel then using automation to format the excel file.
    >If you are using the above process, finish the export completely before
    >starting any code that uses automation to format the exported file.
    >
    >The other usual reason for this problem, is related to how you structure the
    >code that uses automation to format the exported file.
    >This can be caused by closing the instance of excel, then accidentally
    >opening it again by referencing something from the automation code.
    >
    >If you post all your code that performs the export and automation, we can
    >probably spot where the problem is.
    >
    >
    >Jeanette Cunningham -- Melbourne Victoria Australia
    >
    >
    ><> wrote in message
    >news:...
    >> Jeanette,
    >>
    >> FYI, your approach/logic is definitely leaves less "open instances" of
    >> Excel.
    >>
    >> That said, there is still one remaining "phantom" instance held open by
    >> Access.
    >>
    >> The reason this is important, is if I run my Format Excel worksheets macro
    >> via Access
    >> automation a 2nd time in the same instance of Access (where there is still
    >> one remaining "phantom"
    >> instance held open by Access) the formatting fails because Excel is still
    >> holding onto the objects
    >> or some other object-related issue.
    >>
    >> The reason I say this is I can "see" the instance of Excel in Task
    >> Manager. If I do not close the
    >> 1st Access instance (at which point the "phantom" instance of Excel
    >> disappears from Task Manager)
    >> and then re-open Access, the Excel formatting fails.
    >>
    >> I believe that the Access programmers need to figure out how to provide
    >> end users a VBA command to
    >> kill all instances of Excel opened via Access automation.
    >>
    >> There is no doubt in my mind that this Access automation bug is exactly
    >> why the Access Automation
    >> MVP's ignored this issue - direct or indirect pressure from MS to not give
    >> the bug legs.
    >>
    >> EagleOne
    >>
    >> "Jeanette Cunningham" <> wrote:
    >>
    >>>EagleOne,
    >>>here is part of the code I use successfully to format files in excel.
    >>>Below is the opening of excel and closing of excel part of the code.
    >>>
    >>>In your code, you first test to see if excel is already running.
    >>>In your closing part of the code you also can check to see if you opened
    >>>a
    >>>new instance of excel.
    >>>If you didn't need to open a new instance of excel, you don't need to
    >>>close
    >>>it.
    >>>
    >>>'opening excel here
    >>> If fIsAppRunning("excel", False) Then 'yes it is running
    >>> ' Get a reference to currently running Excel window
    >>> Set objXLApp = GetObject(, "Excel.Application")
    >>> blnExcelExists = True
    >>> Else
    >>> ' Excel is not currently running so create a new instance
    >>> Set objXLApp = CreateObject("Excel.Application")
    >>> End If
    >>>
    >>>
    >>>'closing excel here
    >>> 'close the instance of Excel created by code
    >>> If Not blnExcelExists Then
    >>> objXLApp.Quit
    >>> End If
    >>>
    >>> If Not objActiveWkb Is Nothing Then
    >>> Set objActiveWkb = Nothing
    >>> End If
    >>> If Not objXLApp Is Nothing Then
    >>> Set objXLApp = Nothing
    >>> End If
    >>> If Not db Is Nothing Then
    >>> Set db = Nothing
    >>> End If
    >>>
    >>>
    >>>
    >>>Jeanette Cunningham -- Melbourne Victoria Australia
    >>>
    >>>
    >>><> wrote in message
    >>>news:...
    >>>> 2003
    >>>>
    >>>> 2003 up to date
    >>>>
    >>>>
    >>>> If I use Access Automation to export Access data to Excel files, there
    >>>> is
    >>>> always one last instance of Excel in memory unti I close Access.
    >>>>
    >>>> Normally not a problem, unless I wish to do another export.
    >>>>
    >>>> What then occurs is that Access Automation cannot open any Excel files
    >>>> attached to that instance of Excel. In addition, Access VBA procedures
    >>>> which
    >>>> veryify Error status do not run.
    >>>>
    >>>> In fact, if I start the Access Automation macro without previously
    >>>> closing
    >>>> all instances of Excel, fail in the same manner as above.
    >>>>
    >>>> In short, to be successful with Access to Excel Automation, I must first
    >>>> close all Excel files and Excel itself. Also, if certain procedures in
    >>>> the
    >>>> Automation macros did not complete (do to error status check), I must
    >>>> close
    >>>> and restart Access.
    >>>>
    >>>> Thus, both Access must be "rebooted" and Excel must be created 1st time
    >>>> in
    >>>> that session.
    >>>>
    >>>> BTW I am using the Dev Ashish code somewhat tailored as below:
    >>>>
    >>>> Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    >>>> myPath As String, myDate As String, myTime As String)
    >>>> '
    >>>> 'Basic Code Courtesy of Dev Ashish
    >>>> '
    >>>>
    >>>> Dim objXL As Object
    >>>> Dim strWhat As String, boolXL As Boolean
    >>>> Dim MainSiteName As String
    >>>> Dim objActiveWkb As Object
    >>>> Dim Wks As Object
    >>>> Dim FullSheetRng As Object
    >>>> Dim OneColFilteredRng As Object
    >>>> Dim FullSheetFilteredRng As Object
    >>>> Dim ResizedRngToCopy As Object
    >>>> Dim myCell4OffHold As Object
    >>>> Dim myCell4Offset As Object
    >>>> Dim TempShtRng As Object
    >>>> Dim ResizedRngToDelete As Object
    >>>> Dim myCell As Object
    >>>> Dim ThisWorkbook As Object
    >>>> Dim myRowsToProcess As Long
    >>>> Dim myColumnsToProcess As Long
    >>>> Dim MaxRows As Long
    >>>> Dim MaxColumns As Long
    >>>> 'Following necessary for Sort sequences in Excel/Access 2003
    >>>> Dim xlSortOnValues As Long
    >>>> Dim xlAscending As Long
    >>>> Dim xlSortNormal As Long
    >>>> xlSortOnValues = 0
    >>>> xlAscending = 1
    >>>> xlSortNormal = 0
    >>>>
    >>>>
    >>>> If fIsAppRunning("Excel") Then
    >>>> Set objXL = GetObject(, "Excel.Application")
    >>>> boolXL = False
    >>>> Else
    >>>> Set objXL = CreateObject("Excel.Application")
    >>>> boolXL = True
    >>>> End If
    >>>>
    >>>> With objXL.Application
    >>>> '.Visible = True
    >>>> .Visible = False
    >>>> 'Open the Workbook
    >>>> .Workbooks.Open myPathFile
    >>>> '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    >>>> End With
    >>>> Set objActiveWkb = objXL.Application.ActiveWorkbook
    >>>>
    >>>> With objActiveWkb
    >>>> Set Wks = Nothing
    >>>> For Each Wks In .Worksheets
    >>>>
    >>>> Wks.Activate
    >>>>
    >>>> '
    >>>> '
    >>>> '
    >>>> 'Many VBA lines of w/s formating
    >>>> 'Many VBA lines of w/s formating
    >>>> 'Many VBA lines of w/s formating
    >>>> 'Many VBA lines of w/s formating
    >>>> 'Many VBA lines of w/s formating
    >>>> 'Many VBA lines of w/s formating
    >>>> 'Many VBA lines of w/s formating
    >>>> '
    >>>> '
    >>>> '
    >>>>
    >>>> Sheets("Temp").Paste
    >>>> objXL.CutCopyMode = False
    >>>>
    >>>> objXL.DisplayAlerts = False
    >>>>
    >>>> objXL.DisplayAlerts = True
    >>>>
    >>>>
    >>>> Next wks
    >>>>
    >>>> objActiveWkb.Close savechanges:=True
    >>>>
    >>>> objXL.Application.Quit
    >>>> Set objActiveWkb = Nothing: Set objXL = Nothing
    >>>>
    >>>> 'Next section crude attempt to blow away extra Excel sessions
    >>>>
    >>>> If fIsAppRunning("Excel") Then
    >>>> Do While fIsAppRunning("Excel")
    >>>> On Error Resume Next
    >>>> Set objXL = GetObject(, "Excel.Application")
    >>>> On Error Resume Next
    >>>> Set objActiveWkb = objXL.Application.ActiveWorkbook
    >>>> Err.Clear
    >>>> If Not objActiveWkb Is Nothing Then
    >>>> objXL.Application.Visible = True
    >>>> Do While Not objActiveWkb Is Nothing
    >>>> On Error Resume Next
    >>>> Set objActiveWkb = objXL.Application.ActiveWorkbook
    >>>> On Error Resume Next
    >>>> objActiveWkb.Close savechanges:=True
    >>>> If objActiveWkb Is Nothing Then
    >>>> objXL.Application.Visible = False
    >>>> Exit Do
    >>>> End If
    >>>> Loop
    >>>> End If
    >>>> objXL.Application.Quit
    >>>> If objActiveWkb Is Nothing Then
    >>>> Set objActiveWkb = Nothing: Set objXL = Nothing
    >>>> Exit Do
    >>>> End If
    >>>> Loop
    >>>> End If
    >>>>
    >>>> End Sub
    >>>>
    >>>>
    >>>> Any suggestions as correct the challenge?
    >>>>
    >>>> TIA EagleOne
    >>>

    >
     
    , Jun 29, 2008
    #7
  8. Hi,
    I tested your code in a test database.
    Here is the code that worked well.
    It worked if Excel was closed before the export and also if excel was in use
    before the export.
    I suggest that you create a new test form in your database, copy and paste
    this code and try it just as it is with all the formatting code commented
    out.
    See if it runs OK and doesn't leave an instance of excel hanging.

    Now try it with all the formatting code in action and see if the formatting
    code is causing the problem of leaving an instance of excel hanging.




    Here is the code.
    -------------------------Option Compare Database
    Option Explicit

    Private Sub cmdExport_Click()
    Dim myPath As String
    Dim myDate As String
    Dim myTime As String
    Dim myFileName As String
    Dim myPathFile As String

    myPath = CurrentProject.Path & "\"
    myDate = Replace(Date, "/", "-")
    myTime = Format(Time(), "hhmm")
    myFileName = "EagleOne " & myDate & " " & myTime & ".xls"
    myPathFile = myPath & myFileName
    On Error Resume Next ' in case that the file does not exist
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
    "qryEmpTYDH", myPath & myFileName, True
    If Not Err.Number > 0 Then
    Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
    End If


    Err.Clear
    myFileName = "EagleTwo " & myDate & " " & myTime & ".xls"
    myPathFile = myPath & myFileName
    On Error Resume Next
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
    "qryEmpTYDH", myPath & myFileName, True
    If Not Err.Number > 0 Then
    Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
    End If
    Err.Clear

    End Sub

    Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    myPath As String, myDate As String, myTime As String)
    '
    'Basic Code Courtesy of Dev Ashish
    '

    Dim objXL As Object
    Dim strWhat As String, boolXL As Boolean
    Dim MainSiteName As String
    Dim objActiveWkb As Object
    Dim Wks As Object
    Dim FullSheetRng As Object
    Dim OneColFilteredRng As Object
    Dim FullSheetFilteredRng As Object
    Dim ResizedRngToCopy As Object
    Dim myCell4OffHold As Object
    Dim myCell4Offset As Object
    Dim TempShtRng As Object
    Dim ResizedRngToDelete As Object
    Dim myCell As Object
    Dim ThisWorkbook As Object
    Dim myRowsToProcess As Long
    Dim myColumnsToProcess As Long
    Dim MaxRows As Long
    Dim MaxColumns As Long
    'Following necessary for Sort sequences in Excel/Access 2003
    Dim xlSortOnValues As Long
    Dim xlAscending As Long
    Dim xlSortNormal As Long
    xlSortOnValues = 0
    xlAscending = 1
    xlSortNormal = 0


    If fIsAppRunning("Excel") Then
    Set objXL = GetObject(, "Excel.Application")
    boolXL = False
    Else
    Set objXL = CreateObject("Excel.Application")
    boolXL = True
    End If

    With objXL.Application
    '.Visible = True
    .Visible = False
    'Open the Workbook
    .Workbooks.Open myPathFile
    '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    End With
    Set objActiveWkb = objXL.Application.ActiveWorkbook

    With objActiveWkb
    Set Wks = Nothing
    For Each Wks In .Worksheets

    Wks.Activate

    '
    '
    '
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    'Many VBA lines of w/s formating
    '
    '
    '

    ' Sheets("Temp").Paste
    objXL.CutCopyMode = False

    objXL.DisplayAlerts = False

    objXL.DisplayAlerts = True


    Next Wks
    End With

    objActiveWkb.Close savechanges:=True

    'close the instance of Excel created by code
    If boolXL = True Then
    objXL.Quit
    End If
    Set objActiveWkb = Nothing: Set objXL = Nothing

    End Sub
    -------------------------


    Jeanette Cunningham -- Melbourne Victoria Australia



    <> wrote in message
    news:...
    > Thanks for the offer.
    >
    > Note that I have included the original code (before changes to your
    > method)
    >
    > Based upon your comments, I guess that my error is the sequence that I use
    > in the 1st Sub next.
    >
     
    Jeanette Cunningham, Jun 29, 2008
    #8
  9. Guest

    Thank you so much for your time and knowledge!

    I'll attempt the changes 1st thing in my morning. If available, check back in about 12+ hours.

    The reason I left all of the Dim statements applicable to the formatting procedures was if you saw
    any clue that my Dim statements did not inappropriately declare objects.

    Your idea is best, ascertain if the sequencing did not already solve the challenge.

    EagleOne

    "Jeanette Cunningham" <> wrote:

    >Hi,
    >I tested your code in a test database.
    >Here is the code that worked well.
    >It worked if Excel was closed before the export and also if excel was in use
    >before the export.
    >I suggest that you create a new test form in your database, copy and paste
    >this code and try it just as it is with all the formatting code commented
    >out.
    >See if it runs OK and doesn't leave an instance of excel hanging.
    >
    >Now try it with all the formatting code in action and see if the formatting
    >code is causing the problem of leaving an instance of excel hanging.
    >
    >
    >
    >
    >Here is the code.
    >-------------------------Option Compare Database
    >Option Explicit
    >
    >Private Sub cmdExport_Click()
    > Dim myPath As String
    > Dim myDate As String
    > Dim myTime As String
    > Dim myFileName As String
    > Dim myPathFile As String
    >
    > myPath = CurrentProject.Path & "\"
    > myDate = Replace(Date, "/", "-")
    > myTime = Format(Time(), "hhmm")
    > myFileName = "EagleOne " & myDate & " " & myTime & ".xls"
    > myPathFile = myPath & myFileName
    > On Error Resume Next ' in case that the file does not exist
    > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
    > "qryEmpTYDH", myPath & myFileName, True
    > If Not Err.Number > 0 Then
    > Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
    > End If
    >
    >
    > Err.Clear
    > myFileName = "EagleTwo " & myDate & " " & myTime & ".xls"
    > myPathFile = myPath & myFileName
    > On Error Resume Next
    > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
    > "qryEmpTYDH", myPath & myFileName, True
    > If Not Err.Number > 0 Then
    > Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
    > End If
    > Err.Clear
    >
    >End Sub
    >
    > Sub FormatXLSheets(myPathFile As String, myFileName As String, _
    > myPath As String, myDate As String, myTime As String)
    > '
    > 'Basic Code Courtesy of Dev Ashish
    > '
    >
    > Dim objXL As Object
    > Dim strWhat As String, boolXL As Boolean
    > Dim MainSiteName As String
    > Dim objActiveWkb As Object
    > Dim Wks As Object
    > Dim FullSheetRng As Object
    > Dim OneColFilteredRng As Object
    > Dim FullSheetFilteredRng As Object
    > Dim ResizedRngToCopy As Object
    > Dim myCell4OffHold As Object
    > Dim myCell4Offset As Object
    > Dim TempShtRng As Object
    > Dim ResizedRngToDelete As Object
    > Dim myCell As Object
    > Dim ThisWorkbook As Object
    > Dim myRowsToProcess As Long
    > Dim myColumnsToProcess As Long
    > Dim MaxRows As Long
    > Dim MaxColumns As Long
    > 'Following necessary for Sort sequences in Excel/Access 2003
    > Dim xlSortOnValues As Long
    > Dim xlAscending As Long
    > Dim xlSortNormal As Long
    > xlSortOnValues = 0
    > xlAscending = 1
    > xlSortNormal = 0
    >
    >
    > If fIsAppRunning("Excel") Then
    > Set objXL = GetObject(, "Excel.Application")
    > boolXL = False
    > Else
    > Set objXL = CreateObject("Excel.Application")
    > boolXL = True
    > End If
    >
    > With objXL.Application
    > '.Visible = True
    > .Visible = False
    > 'Open the Workbook
    > .Workbooks.Open myPathFile
    > '.ActiveWorkBook.RunAutoMacros xlAutoOpen
    > End With
    > Set objActiveWkb = objXL.Application.ActiveWorkbook
    >
    > With objActiveWkb
    > Set Wks = Nothing
    > For Each Wks In .Worksheets
    >
    > Wks.Activate
    >
    > '
    > '
    > '
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > 'Many VBA lines of w/s formating
    > '
    > '
    > '
    >
    >' Sheets("Temp").Paste
    > objXL.CutCopyMode = False
    >
    > objXL.DisplayAlerts = False
    >
    > objXL.DisplayAlerts = True
    >
    >
    > Next Wks
    > End With
    >
    > objActiveWkb.Close savechanges:=True
    >
    > 'close the instance of Excel created by code
    > If boolXL = True Then
    > objXL.Quit
    > End If
    > Set objActiveWkb = Nothing: Set objXL = Nothing
    >
    > End Sub
    >-------------------------
    >
    >
    >Jeanette Cunningham -- Melbourne Victoria Australia
    >
    >
    >
    ><> wrote in message
    >news:...
    >> Thanks for the offer.
    >>
    >> Note that I have included the original code (before changes to your
    >> method)
    >>
    >> Based upon your comments, I guess that my error is the sequence that I use
    >> in the 1st Sub next.
    >>

    >
     
    , Jun 30, 2008
    #9
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Edward
    Replies:
    1
    Views:
    379
    Martin
    Jul 20, 2005
  2. Guest
    Replies:
    1
    Views:
    267
    Wayne Morgan
    Jan 5, 2006
  3. Guest

    Documentor Leaves Code Window Open

    Guest, Apr 3, 2007, in forum: Microsoft Access
    Replies:
    1
    Views:
    266
    Allen Browne
    Apr 4, 2007
  4. EagleOne@microsoftdiscussiongroups

    Automation to Excel leaves Excel open until Access closed

    EagleOne@microsoftdiscussiongroups, Jun 24, 2008, in forum: Microsoft Access
    Replies:
    4
    Views:
    389
  5. kalice
    Replies:
    2
    Views:
    158
    sergi88
    May 15, 2009
Loading...

Share This Page