Ghost References to Excel - long

[Colleyville Alan]

I am working on an automation project between Access and Excel. I have read
about the problem of "Ghost" copies of Excel being resident in memory when a
program is run multiple times and the errors that it can cause. I have read
the Microsoft explanations about the two causes:

1- an improper reference that is not fully qualified - e.g.

xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello"
NOTE: The error occurs because the code refers to the Cell's
method without preceding the call with the xlSheet object variable.


2 - an improper with - end with construct that is not fully qualified.

Use this:
myExcel.Workbooks(1).Worksheets(1).Cells(1,1).Value = "Hello"
myExcel.Workbooks(1).Worksheets(1).Cells(2,1).Value = "World"

Instead of:
With myExcel.Workbooks(1).Worksheets(1)
.Cells(1,1).Value = "Hello"
.Cells(2,1).Value = "World"
End With
****************************************************************************

I query an Access database, load the result set into an array of custom type
and loop through the array to write to Excel. As far as I know, everything
is fully qualified. I have looked at all of my code and the only
questionable part is that array writing loop, an example of which follows:

for i from 1 to Nrecords:
xlSheet.Range("I" & N) = PerfArray(i).QTD
xlSheet.Range("I" & N) = PerfArray(i).YTD
...
end for

Is it possible that these lines are creating additional instances of Excel
in memory? I removed every "with" statement, and I have checked to make
sure that the object variables are released when the sub ends. I am
definitely having some sort of memory corruption problem - the code runs
fine about 3 times, they begins acting strangely. Then Excel will be almost
impossible to exit (usually have to shut it down from the Task Mgr). If it
is not multiple instances of Excel that are causing the problem, where else
would I look? Thanks.

p.s. sorry for the multiple post - I accidentally posted it to the wrong NG
the first time.

 

[Dick Kusleika]

Alan

Did you ever find an answer to this? Here are some thoughts, although I'm
not an expert on multiple instance problems.

for i from 1 to Nrecords:
xlSheet.Range("I" & N) = PerfArray(i).QTD
xlSheet.Range("I" & N) = PerfArray(i).YTD
..
end for

That shouldn't cause a problem, I don't think. You may want to write it
like this

xlSheet.Range("I" & N).Value = PerfArray(i).QTD

I wouldn't think that omitting the Value property would cause the problem,
but you never know. Value is the default property for the Range object, but
just maybe Excel internally has to create an instance to resolve what the
default property is when you omit it. Either way, it's good practice to
include the property even if it's the default (although I'm sure some would
disagree with that).

- the code runs
fine about 3 times, they begins acting strangely.

I've had some problems with automation when the code runs a bunch of times.
You might consider not creating and destroying the automation variables each
time the sub runs, but rather create them once, run the sub a bunch of
times, then destroy them. Instead of

Sub Run3Times()

Dim xlApp as Excel.Application
Dim xlSheet as Excel.Worksheet

Set xlApp = New Excel.Application
Set xlSheet = xlApp.Workbooks.Open("C:\Mybook.xls").Worksheets(1)

'Other stuff

xlSheet.Parent.Close False
Set xlSheet = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

you might set it up like this

Dim xlApp = Excel.Application
Dim xlSheet = Excel.Worksheeet

Sub MyInitialize()

Dim i as Long

Set xlApp = New Excel.Application
Set xlSheet = xlApp.Workbooks.Open("C:\Mybook.xls").Worksheets(1)

For i = 1 to 3
Run3Times
Next i

xlSheet.Parent.Close False
Set xlSheet = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

This construct sets up the automation variables one time, keeps them in
memory and uses them (because they are declared at the module level) in
Run3Times without creating and destroying them each time. This method may
not work depending on how you run the sub, how often and how close together.
In general, the more you can minimize creating and destroying automation
variables, the less problems you will have. At least in my (limited)
experience, that's been true.