BERT add-in availability at launch

[APfi]

Hello, I use a software that runs data from a database to Excel ("Workbook"). I have a macro in the Workbook that runs some code ("Code") at the startup:

Private Sub Workbook_Open()

    Call RefreshData

End Sub

and

Sub RefreshData()

    Application.Run "BERT.Call", "setwd", "Z:/Folder/Sub_folder/Sub_sub_folder"

    Application.Run "BERT.Call", "source", "Regression.R"

End Sub

The Code works well when I just simply open the Workbook from Windows Explorer. When I run the data from the software to the Workbook I get the following error message:

Run-time error '1004': Cannot run the macro 'BERT.Call'. The macro may not be available in this workbook or all macros may be disabled.

I think the issue is that the BERT add-in has not yet been loaded or is not available. If I click "End" to the VB error message and run the Code again, it works perfectly.

Any idea whether BERT add-in loading time would be the issue and whether can it be changed or could it be something else?

Thanks!

[duncanwerner]

Load time is the issue. For whatever reason, Excel runs the workbook_open method before running the add-in's load method. It looks like you can force it to load by toggling the Connect property, although there's no way to check if it has already been loaded (I suppose you could call a function and check for an error). The following will work:

Private Sub Workbook_Open()
    Dim ai As COMAddIn
    For Each ai In Application.COMAddIns
        If Left(ai.Description, 4) = "BERT" Then
            ai.Connect = False
            ai.Connect = True
            DoEvents
            Application.Run "BERT.Exec", "2+2"
        End If
    Next ai
End Sub

[APfi]

The above code ("FixCode") stops at this point when I run data from the software to the Workbook:

        Application.Run "BERT.Exec", "2+2"

It gives the following error:

Run-time error '287': Application-defined or object-defined error

Different error this time.

When I open the Workbook from Windows Explorer the FixCode stops at this point:

        ai.Connect = True

and gives also error:

Run-time error '287': Application-defined or object-defined error

[APfi]

As an update, I tried Workbook_Activate method but it did not seem to work either.

[MN3638]

@APfi did you find a solution for this issue. I am in a similar situation now.

Thanks

[MN3638]

@duncanwerner I tried the above code ("FixCode"), but I have the same issue as declared above :-( Thanks