0

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
Community
  • 1
  • 1
bendwalk
  • 1
  • 2
  • Maybe you can explain exactly what you mean by "behaves differently". i.e. what is the actual problem you're having ? – Tim Williams Feb 09 '16 at 17:52
  • 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 Feb 09 '16 at 17:56
  • Difficult to make any suggestions here without knowing the inner workings of your add-in. – Tim Williams Feb 09 '16 at 19:02
  • 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 Feb 09 '16 at 19:16
  • http://blogs.msdn.com/b/accelerating_things/archive/2010/09/16/loading-excel-add-ins-at-runtime.aspx Maybe try AddIns.Add instead of RegisterXLL, in case there is some difference in behaviour ? – Tim Williams Feb 09 '16 at 19:24
  • That's a clever idea. I've tried a few permutations based on that, but nothing has worked yet. I'll try a few more, but it's not looking promising. – bendwalk Feb 09 '16 at 20:19
  • Where are you launching this new excel instance from? An existing Excel instance ? – Tim Williams Feb 09 '16 at 20:58
  • In the final iteration, I'd launch it from a .vbs script using windows scheduler. But yes, in my recent tests, I've been launching it from an existing Excel instance since I figured that was the best place to troubleshoot it. – bendwalk Feb 09 '16 at 21:29

1 Answers1

0

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

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks, Tim. I have been adding each .xll file explicitly within MyMacro. The Add-In seems to be present, but it behaves differently when loaded via CreateObject. I've updated my original question to indicate that. – bendwalk Feb 09 '16 at 17:50