Fix: Annoying inconsistent errors in Excel via Access automation

[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

 

[Klatuu]

FYI,
you might try adding the CopyFromRecordset method. That is what I use when
doing automation. I have never encountered a formatting or data type
problem using it. It also is a very straightforward way to get data
exactly where you want it.

 

[Clif McIrvin]

(comments in-line ....)

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.

(In your code sample below, you never turn off the Resume Next ... that
would hide / disable error handling.)

in my case, I'm attempting to automate Access from Excel. Every time I
have seen the "server not available" error it was, in fact, trapped by
error handling. I mostly wrote it off as not having adequately
researched automation and lived with it -- especially as the process in
question is supposed to be temproary and will go away once my Access app
development is completed. Since I last worked on the code, I have
noticed that if I bring my open Access app to the top before I launch
the Excel macro I have fewer strange problems.

Here's a bit of my code: (this is an Excel 2003 macro)
Sub RunAccessMacro()

' Error: 462 The remote server machine does not exist or is unavailable
' Error: 7867 You already have the database open.

Const comNA As Integer = 462
Const dbOpen As Integer = 7867

Dim appAcc As Access.Application
Dim AccWasOpen As Boolean

'With Access.Application throws 424 Object required errors
'on some statements but not others

Set appAcc = Access.Application
With appAcc

' Testing so far shows Remote Server Not Available error
' every other launch of the macro. I have absolutely no
' clue why; nor why a re-launch works. (5/02/08)
' Testing shows that if Access is closed, or open without
' a database then .CurrentDb returns Nothing

On Error Resume Next
AccWasOpen = (.CurrentDb Is Nothing)
If Err.Number = comNA Then GoTo ExitSub
On Error GoTo 0

' Interesting: after putting in the above test I've not seen
' the exit triggered!

========== end code snippet ============

That last remark is interesting in context of Eagle's observations.

I noticed the following tendencies:
1) successful completion almost always occurred if I cleaned-started
Access while Excel was not
open.

I noticed a difference in behavior if I clicked into the open Access
wndow just before launcing the Excel macro. If Access is minimized on
the task bar (or even open but not on top for some 'extended' period of
time ) I would frequently (but not always) see my open Access app turn
into a 'phantom' instance, similiar to what Eagle describes with Excel.
Since I changed my procedure to bring Access to the top, then return to
Excel and launch the macro I have not seen Access turn into a 'phantom'
instance.

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

As I noted above, the On Error Resume Next is still active at this
point, so any further errors will effectively be 'hidden' from error
handlers.

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

Eagle, thanks for sharing your observations with the community!