How can I launch an Access add-in (not COM add-in) from VBA code?

In Microsoft Access I have an add-in (*.accda) function that I would like to call from VBA just as if I clicked the item from the Database Tools -> Add-ins menu.

In the registry, there is a key that lists the library path and expression, but I haven't found an place where the ability to invoke this is exposed to VBA.

The closest I have come is to add a reference to the library, then immediately remove the reference. This loads the VBE project and allows me to call the function without adding a persistent reference to the project.

With References
    .AddFromFile Environ$("AppData") & "\Microsoft\AddIns\Version Control.accda"
    .Remove references("MSAccessVCS")
End With
Run "MSAccessVCS.AddInMenuItemLaunch"

The problem is that if I close the CurrentDB, the library is no longer loaded. On the other hand, if I invoke the add-in through the add-ins menu, it is persisted even when the current database is closed.

Since the add-in needs to perform functions that include the closing of the current database, it only works if I launch the add-in through the menu.

Has anyone found a system command, WizHook reference, API call, or other function that can launch an add-in in the same way as clicking on the link in the add-ins menu? The end goal here is to create an automated DevOps process where the database opens and invokes the add-in function without any user intervention (i.e. menu clicks) required.

Working Solution

A huge thank you to Victor K for pointing me in the right direction! It turns out that you can actually include a file path in the Application.Run function to load an add-in at the application level, which is what happens when you launch the add-in from a menu command.

The syntax is similar to how you would load an add-in in Microsoft Excel, but as of yet I have not figured out how to successfully add the procedure name to the file path to call the add-in function in a single call. Instead, I take a two-step approach where the first call loads the library, and the second call runs my add-in function. I have implemented this as a function in my VBA code.

Hopefully no one is actually using 30 named arguments in their add-in function, :-) but I went ahead and added all of them in the second example to fully mirror the functionality of Application.Run.

' Example of calling an add-in function.
Public Sub ShowVersionControl()
    RunAddin Environ$("AppData") & "\Microsoft\AddIns\Version Control.accda", "MSAccessVCS.AddInMenuItemLaunch"
End Sub


' Simple function to demonstrate concept:
Public Sub RunAddin(strPath As String, strFunction As String)
    ' The following lines will load the add-in at the application level,
    ' but will not actually call the function. Ignore the error of function not found.
    On Error Resume Next
    Application.Run strPath & "!DummyFunction"
    Application.Run strFunction
End Sub


' More robust function with full parameter support for .Run()
Public Sub RunAddin2(strPath As String, strFunction As String, _
    Optional Arg1, Optional Arg2, Optional Arg3, Optional Arg4, Optional Arg5, _
    Optional Arg6, Optional Arg7, Optional Arg8, Optional Arg9, Optional Arg10, _
    Optional Arg11, Optional Arg12, Optional Arg13, Optional Arg14, Optional Arg15, _
    Optional Arg16, Optional Arg17, Optional Arg18, Optional Arg19, Optional Arg20, _
    Optional Arg21, Optional Arg22, Optional Arg23, Optional Arg24, Optional Arg25, _
    Optional Arg26, Optional Arg27, Optional Arg28, Optional Arg29, Optional Arg30)
    
    Dim lngError As Long
    
    ' Trap and ignore expected error.
    On Error Resume Next
    
    ' The following lines will load the add-in at the application level,
    ' but will not actually call the function. Ignore the error of function not found.
    Application.Run strPath & "!DummyFunction"
    
    ' Check returned error just in case it was something else.
    lngError = Err.Number
    If Err Then Err.Clear
    On Error GoTo 0
    If lngError <> 2517 Then Err.Raise lngError
    
    ' Now that the library is loaded, we can call the function.
    Application.Run strFunction, _
        Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, _
        Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, _
        Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30

End Sub

Now I can successfully call a function in my add-in in such a way that it is loaded at the application level. This allows the add-in to close the current database and rebuild it from source code in a fully automated process.

Note: When using this approach, you may need to add a trusted location to allow the add-in to run without a security warning.

In order to load an add-in or a file that contains macros you can use Application.Run with a full path to the file like this:

In the add-in create an empty subroutine, for example:

Public Sub Ping()
End Sub

Call this subroutine from the project that needs to load the add-in:

Application.Run "'X:\ExamplePath\Addins\MyAddIn.accda'!Ping"

In fact you can normally use it to call whatever subroutine you want right away:

Application.Run "'X:\ExamplePath\AddIns\MyAddIn.accda'!SubIWnatToRunInTheAddIn"

You can also use this approach in a similar way to late binding (plus you can pass arguments as well):

In add-in:

Public Function NewAddInClass() as AddInClass
Set NewAddInClass = New AddInClass
End Function

In calling code:

Public Sub CallAddInClass()
Dim TestClass as Object
Set TestClass = Applcation.Run ("'X:\AddinPath\MyAddIn.accda'!NewAddInClass")
TestClass.ExampleMethod
End Sub

I don't work as much with Access, but it appears that Access has CurrentProject object that you can perhaps return a reference to from the add-in in a similar way that could give you access to AllMacros, AllModules, etc.

Working Solution

Recommended topics

Hot tags