Automation to Excel leaves Excel open until Access closed

  • Thread starter EagleOne@microsoftdiscussiongroups
  • Start date
E

EagleOne@microsoftdiscussiongroups

2003

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 as below:

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Sub sTestXL()
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

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

objXL.Application.workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

With objActiveWkb
.Worksheets(1).Cells(1, 1) = "Hello World"
strWhat = .Worksheets(1).Cells(1, 1).value
End With

objActiveWkb.Close savechanges:=False

If boolXL Then objXL.Application.Quit

Set objActiveWkb = Nothing: Set objXL = Nothing
MsgBox strWhat
End Sub
'************ Code End **********
 
K

Klatuu

That is not your actual code. To be able to see where the problem is, I
would have to see the real code.
What you are experiencing is most often caused by incomplete object
referecing. When using the Access Object Model, you can get away with some
slop, because Access pretty much knows what you really mean, but since the
Excel object model is an extenal reference, your object referencing has to be
exact.

What happens is when you reference an Excel object and the reference is not
fully qualified, Access doesn't know what object it belongs to so it creates
another instance of Excel you don't know about. So when you Quit the
instance you created, the one Access created is still running. I have seen
this on numerous occastions.

If you can post the real code, then perhaps I can help find where the
reference is incomplete.
 
E

EagleOne

My code follows:

Every reference to objects remain. I stripped all Excel w/s formatting code although I did leave
the Dim statements for the formatting and objects.

TIA !!! EagleOne
 
E

EagleOne

Woops!


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

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