I created my VSTO Excel Plugin and want to call this from VBA Macro Button. But I am facing an issue while clicking on the button
Error: Object variable or with block variable not set.
I add this in my ThisAddin file
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
refreshConnection = new RefreshConnection();
Application.COMAddIns.Item("ProgID").Object = refreshConnection;
}
In place of progID I used my ID I created.
And in another File where my Excel plugin Ribbon button class is there which consist of my excel plugin button click [ComVisible(true)] [ClassInterface(ClassInterfaceType.None)] this I included before my class.
Below is my VBA macro module code
Sub Button1_Click()
On Error GoTo ErrorHandler
Dim addIn As COMAddIn
Dim automationObject As Object
Set addIn = Application.COMAddIns("ProgId")
Set automationObject = addIn.Object
automationObject.RefreshWorkbookConnection
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description
End Sub
Please provide me the step-by-step implementation how can I achieve this.