PDA

View Full Version : Solved: CreateObject("Excel.Application") Problem



tmckinno43
12-09-2008, 10:07 PM
I have a macro in MS Word VBA which uses the following code to start Excel:

Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Excel.Workbooks.Add

I also have a workbook in the XLStart folder. Formerly, when Excel launched from the Word macro, the workbook in the XLStart document would launch along with it, as it still does when Excel is launched from a Worbook file icon or the Application icon.

But now the workbook in XLStart doesn't launch anymore if Excel is launched via the CreateObject command.

How this happened seems to have something do with my modifying the Excel workbook under the following circumstance:

I launched the Word macro while an instance of Excel was already running.
Excel duly launched, and so did the workbook in XLStart. But since there was an existing instance of Excel already running, the new instance opened the workbook from XLstart in read-only mode.

As you might expect, the XLstart workbook contains macros. It so happened that when the read-only workbook opened, the need arose to modify one of the macros it contained. I did this to the read-only copy and the macro worked fine. Then, when I shut down Excel it offered to save the workbook containing the macro. I did and saved it under the "Copy of [filename].xls" default filename for workbooks when they open as read-only.

I did all of this rather unthinkingly, and when I opened up Excel sometime later, I noticed that two workbooks were open: the one under the original name and one with the "Copy of" prefix.

Now, this seemed weird, so I tried to fix by deleting the old Workbook and renaming the new one by eliminating the "Copy of" prefix, since this was the most recent one.

And ever since I did this, the Workbook in the XLStart folder doesn't launch whenever I use the CreateObject command.

Now I do understand that a potential response to this might be: you should be able to execute the same code directly from the Word VBA macro. My reply to this is, Yes, but for the need I am trying to satisfy this is completely impractical.

Another potential response is that I can just save the workbook created, re-open it and the launch the macros since, as I pointed out earlier, the workbook is available if I launch from a workbook or application icon. Suffice to say: If that were a satisfactory solution, then I wouldn't be posting this.

Does anyone know what, if anything I can do to restore things to their former state, so the Workbook in XLStart duly launches when the CreateObject command is used.

Bob Phillips
12-10-2008, 01:31 AM
It is quite simple, that is expected behaviour.

When Excel is started via Automation, it doesn't load the startup files. This is to minimize the startup time for Excel. If you need your XLStart files, you'll have to do it yourself in the code.

By the way, calling the vriable Excel doesn't seem a good idea to me.

Dr.K
12-10-2008, 09:44 AM
Yeah, if you want 'Excel' in the variable name, objExcel or appExcel is probably a better bet.


To XLD's point, keep in mind that EVERYTHING BEHAVES DIFFERENTLY UNDER AUTOMATION.

Be grateful that under Office most of these differences are well documented... When dealing with other object models, you often have to figure out its behavior through trial and error.

tmckinno43
12-10-2008, 11:47 AM
It is quite simple, that is expected behaviour.

When Excel is started via Automation, it doesn't load the startup files. This is to minimize the startup time for Excel. If you need your XLStart files, you'll have
to do it yourself in the code.

By the way, calling the vriable Excel doesn't seem a good idea to me.
So the prior behavior which I tried to describe in some detail was "unexpected" (and I assure you it was not imaginary). I'm confident there was nothing in the code to provoke the loading of the startup files. It just so happens that in my case not having to call them explicitly was beneficial. And then unfortunately that beneficial behavior disappeared.

Any ideas on what might account for the unexpected loading of the startup files?

Thanks for the tip about the variable naming.

Bob Phillips
12-10-2008, 12:10 PM
None, I would not expect and don't believe I have seen it.

tmckinno43
12-10-2008, 12:22 PM
Thanks for the rapid reply.