Add-In not working when Excel called from command line

[tweaver60]

I have developed an Excel Add-In in VS that has worked beautifully till this morning. The add-in works fine when you startup Excel normally, and manually load an xslm file the calls a method in the add-in, from the Open menu item. It also works fine debugging in VS - Excel is fired automatically, Add-In is loaded, and can load an xlsm file that calls a method in the Add-In. It also works when running excel from the command line – BUT it you run excel from the command line with the xxxx.xlsm as the parameter, it does not. Same results if I try to fire excel from a C# app using the process.Start() - which is what I need to ultimately do. If I double click on the .xlsm from Windows Explorer, is works fine as well. The error I’m getting is in the VBA function Workbook_Open() in the .xlsm I'm using – it blows when it attempts to access any method from the add-in.

I get the old ‘Object variable or With block variable not set’ when it hits the following line automationobject.Startup or any method in the add-in – it’s like it’s not loading the add-in properly, so it’s not available to call. I've put in automationobject.COMAddIns.Update before I set an object to it, tried some waits as well - to no avail.

Workbook_Open method here:

Please Login or Register  to view this content.

Any help greatly appreciated!

[OnErrorGoto0]

Have you tried toggling the add in's Installed property to False and back to True again? That is the usual way of loading adding when automating Excel (though I do not believe it should be necessary when shelling the exe or using process.start)

I take it that all calls to the addin fail, not just ones in Workbook_Open?

[tweaver60]

Appreciate the reply!

Have you tried toggling the add in's Installed property to False and back to True again?

This is my first attempt at an Add-In - do you mean in Excel/Advanced Options/Add-Ins etc - checking/unchecking the checkbox - next to the Add-In name? - If that is it, yes, I've done that many times.

And yes, all the calls fail. This just make no sense to me.

[snb]

Instead of

automationobject.Startup

Try :

Please Login or Register  to view this content.

[tweaver60]

Thanks for your reply.

Startup is a method (function) in the Add-In, it is not a macro. Although I did try your suggestion, but it could not find the 'Macro' Startup

[OnErrorGoto0]

I meant to do it in code, not manually, but forgot that for comaddins you need to set the connect property to True, rather than using the Installed property as with xlam addins.

[snb]

Startup is a method in the Add-In

What do you mean by 'method' ?

[tweaver60]

What do you mean by 'method' ?

This Add-In is a COM Add-In (written in .Net C#), essentially like a DLL that has functions (methods inside Classes inside the object) you call

[tweaver60]

I take it that all calls to the addin fail, not just ones in Workbook_Open?

Actually. I just realized, any call to the Add-In works AFTER the Workbook_Open function completes - within Workbook_Open, no calls to the Add-In work. Seems like there is some initialization of the add-in that is not completing by the time it gets into Workbook_Open, but completes after the Open function completes. Unfortunately, I need something called in Workbook_Open to initialize something.

[snb]

What about workbook_activate, or worksheet_activate ?

[OnErrorGoto0]

You could also use application.ontime with a delay of say a second to call the code you want (after moving that code to a normal routine)

[tweaver60]

You could also use application.ontime with a delay of say a second to call the code you want (after moving that code to a normal routine)

WOW - timing is everything (in many ways). I just spoke with a colleague, and he suggested the same solution - and it worked!

I will also try workbook_activate, etc

Many thanks to all!