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