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