How to call a VBA routine in an add-in from a VB app?

[Camden]

I have an Excel XLA add-in, written in VBA: call it "MyAddin.xla". Say
it has a module "AModule" and a Public Sub called "SomeRoutine()". I
want to be able to write an app in VB that will start up Excel,
activate the add-in, and then call SomeRoutine().

I've been able to do the first couple parts of that: I can call up
Excel from a VB app, and then step through the AddIns collection, find
my add-in, and make sure .Installed = True. All well and good. I can
add a workbook, then a worksheet, then change the name of the
worksheet, no problems.

Problem is, I haven't a clue as to how to call SomeRoutine() from my
VB app. I have an Object, addinMine, that I set equal to the Addins
item named "MyAddin.xla". After that I've tried:

- addinMine.Commands.SomeRoutine
- addinMine.SomeRoutine
- appExcel.Run addinMine.FullName & "!SomeRoutine"
- appExcel.Run addinMine.FullName & "!Commands.SomeRoutine"

Yes, those last two only work for workbook macros, but I still had to
try.


I've dug around in the docs I have, and dug around using Google, but
no luck. Can any of you folks tell me how to do what I want to do?
Failing that, can anyone tell me why it cannot be done? Thanks for any
help you can offer.

 

[Tom Ogilvy]

When you start excel through automation, addins are not loaded. You can
load your addin with this code posted by KeepItCool;

code from KeepItCool (Oct 21, 2004)
Sub LoadXLwithAddins()
Dim xl As Object
Dim ai As Object

Set xl = CreateObject("Excel.Application")

For Each ai In Application.AddIns
If ai.Installed Then
xl.Workbooks.Open(ai.FullName).RunAutoMacros 1
End If
Next

xl.Visible = True
Set xl = Nothing
End Sub

Once the addin in loaded, then you should be able to run a macro in it by
using the RUN command.

. . . Run "MyAddin.xla!SomeRoutine"