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

E

EagleOne

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
 
J

Jeanette Cunningham

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
 
E

EagleOne@microsoftdiscussiongroups

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!
 
J

Jeanette Cunningham

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"
 
E

EagleOne

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
 
J

Jeanette Cunningham

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
 
E

EagleOne

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
 
J

Jeanette Cunningham

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
 
E

EagleOne

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top