C
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
****************************************************************************
In my application, 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
My question is this: is the code show above responsible for creating
additional instances of Excel in memory? Is there a way to more fully
qualify this statement:
xlSheet.Range("I" & N) = PerfArray(i).QTD
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.
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
****************************************************************************
In my application, 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
My question is this: is the code show above responsible for creating
additional instances of Excel in memory? Is there a way to more fully
qualify this statement:
xlSheet.Range("I" & N) = PerfArray(i).QTD
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.