strive4peace said:
Hi Roy,
thanks for jumping in ...I have generic code for an Excel
conversation along the line of what you do ...but Nate, an esteemed
Excel MVP, says he does not like to "Hijack" another instance of
Excel, so he does this:
Set xlApp = New Excel.Application 'you used xl for the variable name
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
I'm not entirely sure what the "use early binding, always create new
instance" of yours is directed at, but I'll address some of the
issues, and try to relate it to the OPs challenge.
I always use CreateObject when doing automation, based on both
recommendations from MS and experience.
"When creating an instance of an Microsoft Office application, use
CreateObject instead of New. CreateObject more closely maps to the
creation process used by most Visual C++ clients, and allows for
possible changes in the server's CLSID between versions. CreateObject
can be used with both early-bound and late-bound objects."
http://support.microsoft.com/?kbid=244264
I never, ever use early binding when automating, a praxis which seems
to be recommended by most developers. Since my apps have to work 2000
through 2007, which also seems to be a common enough requirement,
early binding/vtable binding is not possible.
In addition, both since the OP is using late binding, and since the
exixtance and creation of extra and unnecessary instances of Excel
is a large part of the OPs problem, I think you're quite possibly
creating unneeded confusion by your advice.
But first "hi-jacking", as you call it, existing applications - well,
some of us have customers with limited computer recourses. On my
computer, opening three instances of Excel (in the interface) each
with a smallish workbook, uses 141MB (47MB each), while opening the
same three workbooks in the same instance, uses 48,5MB. So, say the
user clicks umpteen different report buttons, they will get umpteen
new instances of Excel, each with their own workbook containing the
report (47MB * umpteen instances -> computer slowing to a halt).
Also, with regards to recourses, seems to me, creating a new instance
of the automated application, takes more time than reusing an already
existing instance, and pretty much regardless of coding skills,
automation is dead slow.
This is why I mentioned, and also demonstrated how to use such, but
really, (re)using an existing instance of the automated application
(as I would say), is entirely outside the scope of the OPs problem,
as that is something you'd work on, or decide upon, with regards to
how to fetch the Excel instance in the first place, not how you use
it within the process.
Then to the problem.
The problem is that the OP first creates one instance of Excel, in
which a workbook is created, then formatted a bit, some sheets are
added etc. Then this workbook is closed, and they quit the Excel
application.
Note - the reference is not released, so quite likely, as it did on
my setup, the instance will be kept in memory.
Further down, a new instance of Excel is instantiated, which is set
to visible. This is what I addressed - there is no need to do this,
and this is actually one of the causes of the OPs problem, and has
nothing to do with whether the initial instance of Excel was a
preexisting instance, or created for this process.
Then, through the GetObject method, the same workbook which was
previously saved and closed, is opened to an existing instance of
Excel - but which of them?
Since most likely two instances of Excel exists in memory (or, with
your advice, they might have even more instances of Excel in memory),
which of these, will "inherit" the opened workbook? As indicated by
the OP, it isn't the last one instantiated (which was set to visible),
but the one created at the start of the routine (or perhaps,
following your advice, an instance of Excel that was open prior to
running this process?).
The two principles here, is primarily usage of the same Excel
instance throughout the whole process, or as I said last reply
"I would say - don't create another instance of Excel, use the same
instance all the time."
Then, it's the matter of object referencing. This is referred to as
"implicit", where one relies on the automated application to do
whatever it is one hoped it should do (quite commonly, and
especially when doing automation, it will do something entirely
else).
Here, there's no guidance regarding which Excel instance GetObject
should assign the newly opened workbook to -> implicit.
I find it imperative to be "explicit", so that there's no doubt
whatsoever, neither in my mind, nor for Access/VBA/the automated
application, which object belongs to which object hierarchy/parent
objects (here, the (correct) application object). Failing to do so,
often results in strange and unpredictable automation errors, 1004,
462, 429, -2147023174, -2147417848, or such as here, two instances
of Excel, one is visible, the other (which isn't visible), contains
the workbook... (are there more instances of Excel in memory? Check
with Task Manager (ctrl+shift+esc))
I must confess I find other object models much more forgiving than
the different Office application objects.
Now, by just using the same instance through the whole routine, which
was my main point, and some smallish changes, this could easily be
avoided. The smallish change, would be to use the .Workbooks.Open
method, and directly assign the workbook to a workbook variable (see
below).
Again, this has nothing to do with reusing an existing instance or
not (or "hi-jacking" as you say), but simply to use the same
instance through the whole process, regardless of whether it is a
separate instance created for this process, or reusing an existing
instance, in addition to explicit object referencing.
Here, rewriting the last piece trying to illustrate the technique,
(without reuse).
Dim xlapp As Object
Dim xlwbk As Object
Dim xlsht As Object
' Initializing - once
Set xlapp = CreateObject("excel.application")
xlapp.Visible = True
Set xlwbk = xlapp.Workbooks.Add
Set xlsht = xlapp.ActiveWorkbook.Sheets(1)
xlsht.Range("a1").Value = "hi"
xlwbk.Worksheets(1).Name = "MySheet"
Set xlsht = xlapp.ActiveWorkbook.Sheets(2)
xlsht.Range("a2").Value = "hi"
xlwbk.Worksheets(2).Name = "MySheet2"
Set xlsht = xlapp.ActiveWorkbook.Sheets("MySheet")
xlsht.Range("b1:d1").Value = "something else"
xlwbk.SaveAs "d:\documents and settings\mgonn\Desktop\mine.xls"
' no quitting and reinstantiating - keeping the
' same instance open
' xlapp.Quit -
' Set xlapp = CreateObject("excel.application")
' xlapp.Visible = True
' to open a workbook, use the .Workbooks.Open method
' Set xlwbk = GetObject("c:\mine.xls")
Set xlwbk = xlapp.Workbooks.Open( _
"d:\documents and settings\mgonn\Desktop\mine.xls")
Set xlsht = xlwbk.Sheets("MySheet2")
xlsht.Range("c1").Value = "another thing"
xlwbk.Save
Stop
' NOTE - close and release all object variables
Set xlsht = Nothing
' if necessary (if you quit, you should close first)
xlwbk.Close
Set xlwbk = Nothing
' if necessary
xlapp.Quit
Set xlapp = Nothing
