CreateObject(“Excel.Application”) instance behaves differently than “normal” (point-click) instance

Question

If I open Excel via double clicking on an Excel file then use VBA to open up MyExcelFile and run MyMacro, things go fine. But if I use VBA to create a new instance of Excel using CreateObject, and use VBA to open up MyExcelFile and run MyMacro, a third-party Add-In I don't have control over behaves differently (wrong).

I'm wondering if there are settings for Excel instances I can control and should set explicitly? I got really excited when I saw this, but it didn't solve my problem: "New" Excel.Application vs Excel.Application

Does opening Excel via point-and-click initiate different settings from CreateObject("Excel.Application")? Maybe different working directories or something?

In summary, for my third-party AddIn:

Works:

Workbooks.Open("MyExcelFile")
Application.Run "MyExcelFile!MyMacro"

Does not work:

Dim ObjXL 
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open("MyExcelFile")
objXL.Application.Run "MyExcelFile!MyMacro"

Within MyMacro, I load the AddIn:

Dim success as Boolean
...
success = False
success = Application.RegisterXLL("PathToAddIn_x64.xll")
If Not (success) Then
    Exit Sub
End If
success = Application.RegisterXLL("PathToOtherAddIn_x64.xll")
If Not (success) Then
    Exit Sub
End If

Maybe you can explain exactly what you mean by "behaves differently". i.e. what is the actual problem you're having ? — Tim Williams
It's a little esoteric, but the AddIn pulls data into the spreadsheet from the third-party's database. I can tell the data to load horizontally (within one row, across columns) or I can tell it to load vertically (within one column, across rows). I have asked it to load the data vertically. When it runs via the CreateObject, it ALWAYS loads horizontally, regardless of what I've request it to do. I really don't want to rewrite all the code/logic to deal with that situation. — bendwalk
Difficult to make any suggestions here without knowing the inner workings of your add-in. — Tim Williams
Yes, thanks for trying so far. It's a 3rd party add in, so I'm a bit blind as well. Since it DOES work if I'm running it manually, I was wondering if there was a way I could solve this problem with the tools I had available to me -- either another way to create an instance of Excel, or settings that I can set/change when I use CreateObject. One note: when I had a breakpoint right after Set objXL = CreateObject("Excel.Application") and then manually opened the file and ran MyMacro, it also failed. Thus it seems that CreateObject makes an Excel instance that differs from a "normal" launch — bendwalk
blogs.msdn.com/b/accelerating_things/archive/2010/09/16/… Maybe try AddIns.Add instead of RegisterXLL, in case there is some difference in behaviour ? — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2016-02-09T17:37:17

Add-ins do not load automatically when you create an Excel instance via automation.

You will need to load any required items using VBA.

See - support.microsoft.com/en-us/kb/213489

CreateObject(“Excel.Application”) instance behaves differently than “normal” (point-click) instance

1 Answers