E
EagleOne
2003 & 2007
I wish to thank all who have helped me (no formal Access VBA training) accomplish my goals of
automating Excel via five different Access applications. This Office marriage is excellent because
Access can accomplish the heavy-lifting of data-matching, or comparison, when file size (records
over 65,000 or even 1,000,000 in Excel 2007) is an issue. Therefore, I offer my comments as help to
others to return the favors.
Since May 2008, I have been utilizing Office Automation Access > Excel.
With about five different applications, I noted an annoying and unpredictable pattern.
Usually, I waited until all data is transferred into an Excel file(s) and then performed worksheet
formatting (columns), totals, cell-formatting etc.
Sometimes (10%) the formatting would complete properly, (80%) partially complete, (10%) not complete
at all. The same code line would work one time and the next time it would not. Interestingly, the
error would not trigger an error-handling event. Therefore, sometimes a variable would be filled
with a -0- when 0 was not at all correct.
The same line of code which errored, if executed in the Immediate Window, would show "server not
available ...." Again, this did not signal an error to the level necessary to trigger error
handling.
I noticed the following tendencies:
1) successful completion almost always occurred if I cleaned-started Access while Excel was not
open.
2) Phantom (residual) instances of Excel still open after Access completed would assure incomplete
formatting for the next execution of the Access routine.
3) These phantom instances hanging-on were/are completely unpredictable as to when an error would
occur. The only consistency is/was: If a phantom (Excel confirmed only in Task Manager) instance of
Excel remained after execution, then there was a 100% chance that Excel formatting did NOT occur
properly.
In a what-the-hell moment, I decided to .Save; .Close then re-Open the Excel file before the
formatting procedures occurred. This technique is working 100% correctly ever since (so far)
My guess is Excel is not as stable and predictable in Access Automation. Also, file size seemed to
cause instability. Not just that larger was worse but that there was a tendency that a repeated
running of a file, if successful once, then there was a tendency that same file would repeat
successfully the next run.
So, if your Automation setup is like:
Dim oXL As Object ' Excel.Application
Dim oWbk1 As Object ' Excel.Workbook
Dim oSht As Object 'Excel.Worksheet
Dim xlOpen As Boolean
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err.Number = 0 Then
xlOpen = True
Else
xlOpen = False
Set oXL = CreateObject("Excel.Application")
End If
oXL.Visible = False
Set oWbk1 = oXL.Workbooks.Open(myPath & myFileName)
then consider periodically performing, while processing, the following steps in VBA:
oWbk1.Save
oWbk1.Close
Set oWbk1 = oXL.Workbooks.Open(myPath & myFileName)
Thanks and G/L, EagleOne
I wish to thank all who have helped me (no formal Access VBA training) accomplish my goals of
automating Excel via five different Access applications. This Office marriage is excellent because
Access can accomplish the heavy-lifting of data-matching, or comparison, when file size (records
over 65,000 or even 1,000,000 in Excel 2007) is an issue. Therefore, I offer my comments as help to
others to return the favors.
Since May 2008, I have been utilizing Office Automation Access > Excel.
With about five different applications, I noted an annoying and unpredictable pattern.
Usually, I waited until all data is transferred into an Excel file(s) and then performed worksheet
formatting (columns), totals, cell-formatting etc.
Sometimes (10%) the formatting would complete properly, (80%) partially complete, (10%) not complete
at all. The same code line would work one time and the next time it would not. Interestingly, the
error would not trigger an error-handling event. Therefore, sometimes a variable would be filled
with a -0- when 0 was not at all correct.
The same line of code which errored, if executed in the Immediate Window, would show "server not
available ...." Again, this did not signal an error to the level necessary to trigger error
handling.
I noticed the following tendencies:
1) successful completion almost always occurred if I cleaned-started Access while Excel was not
open.
2) Phantom (residual) instances of Excel still open after Access completed would assure incomplete
formatting for the next execution of the Access routine.
3) These phantom instances hanging-on were/are completely unpredictable as to when an error would
occur. The only consistency is/was: If a phantom (Excel confirmed only in Task Manager) instance of
Excel remained after execution, then there was a 100% chance that Excel formatting did NOT occur
properly.
In a what-the-hell moment, I decided to .Save; .Close then re-Open the Excel file before the
formatting procedures occurred. This technique is working 100% correctly ever since (so far)
My guess is Excel is not as stable and predictable in Access Automation. Also, file size seemed to
cause instability. Not just that larger was worse but that there was a tendency that a repeated
running of a file, if successful once, then there was a tendency that same file would repeat
successfully the next run.
So, if your Automation setup is like:
Dim oXL As Object ' Excel.Application
Dim oWbk1 As Object ' Excel.Workbook
Dim oSht As Object 'Excel.Worksheet
Dim xlOpen As Boolean
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err.Number = 0 Then
xlOpen = True
Else
xlOpen = False
Set oXL = CreateObject("Excel.Application")
End If
oXL.Visible = False
Set oWbk1 = oXL.Workbooks.Open(myPath & myFileName)
then consider periodically performing, while processing, the following steps in VBA:
oWbk1.Save
oWbk1.Close
Set oWbk1 = oXL.Workbooks.Open(myPath & myFileName)
Thanks and G/L, EagleOne