Thread: Add-In not working when Excel called from command line

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:

Private Sub Workbook_Open()
Dim CmdRaw As Long
Dim EqualLocation As Integer
Dim CmdLine As String
Dim QueueName As String
Dim addIn As COMAddIn
Dim automationobject As Object

    Application.COMAddIns.Update
    MsgBox Application.COMAddIns.Item("AddIn").Description
    Set addIn = Application.COMAddIns("AddIn")
    Set automationobject = addIn.Object
    automationobject.Startup
    MsgBox "After addin call"

End Sub

Any help greatly appreciated!

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?

Instead of

automationobject.Startup

Try :

Application.Run "startup"

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

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.

Startup is a method in the Add-In

What do you mean by 'method' ?

Originally Posted by snb

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

Originally Posted by OnErrorGoto0

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.

What about workbook_activate, or worksheet_activate ?

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)