cancel
Showing results for 
Search instead for 
Did you mean: 

Analysis for Excel Add-in to run automatically when document opens

0 Kudos

Hi Experts,


I have an issue with the Analysis add-in.

I am aware of the registry setting that enables the loading of the add-in at Excel startup (which works great by the way), but this is not desired.

My issue is that whenever a user opens up an analysis for Excel document ,and the add-in is not loaded, the document cannot be refreshed. The user then has to activate the add-in and refresh.

Is there a way to embed a code in the Excel document to load the add-in every time it opens (if the add-in is not loaded)? A button in the excel to activate the add-in would also work great.

I tried this VBA code, but no luck. I guess it has something to do with the add-in being a COM add-in.

Private Sub Workbook_Open()

AddIns("Analysis").Installed = True

End Sub

Thanks,

Ofer

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

Are you looking for something that will do this.

Dim lResult As Long
    Dim addin As COMAddIn

    For Each addin In Application.COMAddIns

        If addin.progID = "SBOP.AdvancedAnalysis.Addin.1" Then

            If addin.Connect = False Then addin.Connect = True

        End If

    Next

0 Kudos

This is it!

Thanks Michael!

Former Member
0 Kudos

Hi Ofer,

Can you please share with us your implemented solution?

Thanks in advance,

Jose

0 Kudos

Hi Jose,

David’s idea sounds great, I haven’t tried it yet.

Michael’s solution works very well.

If you go to your AO Excel template, and add this VBA code to your “Thisworkbook” sheet, the template would open with the Analysis add-in:

Private Sub Workbook_Open()

Dim lResult As Long

    Dim addin As COMAddIn

    For Each addin In Application.COMAddIns

        If addin.progID = "SBOP.AdvancedAnalysis.Addin.1" Then

            If addin.Connect = False Then addin.Connect = True

        End If

    Next

End Sub

Other stuff I did on the template was:

  1. Modify the SAP cell styles to match the customer’s set.
  2. Add logos.
  3. Add VBA code to name the sheet as the data source name.
  4. Create a sheet that summarizes the user’s answered prompts, dynamic filters, date and time of the data refresh and conditional formatting to make it all look nice.

Hope this helps,

Ofer

Peter_Gabriels
Advisor
Advisor
0 Kudos

Thanks Michael, very useful!

However, when I try to disable the addin, the code below closes the excel session completely. Anything that I am missing here?

Dim lResult As Long

    Dim addin As COMAddIn

    For Each addin In Application.COMAddIns

        If addin.progID = "SBOP.AdvancedAnalysis.Addin.1" Then

            If addin.Connect = True Then addin.Connect = False

        End If

    Next

Former Member
0 Kudos

Hi Peter,

I tested the code and it seems to work correctly? Is there anything you are doing in your workbook that I might need to replicate?

Have you tested you can enable the add-in with the code?

Peter_Gabriels
Advisor
Advisor
0 Kudos

Hi,

I have tested the code and I can enable the add-in with the code. yes, thanks.

Now I am looking into which code to use for DISabling the add-in.

When I replace the True and the False in the above code, it closes everything. So the complete application is closed, without even prompting to save.

Any help greatly appreciated,

Peter

michael_simon4
Participant
0 Kudos

Hi Peter,

did you find a solution for your problem? I'm facing the same problem - Excel closes without prompt when I disable the add-in via macro.

Thanks,

Michael

Answers (2)

Answers (2)

0 Kudos

Hi,

I'm not sure if this is related to an Analysis for Office update or just a parameter that is different but in my case the addin progID is "SapExcelAddIn" and not "SBOP.AdvancedAnalysis.Addin.1" which gives the following code:

Private Sub Workbook_Open()

Dim lResult As Long

Dim addin As COMAddIn

For Each addin In Application.COMAddIns

If addin.progID = "SapExcelAddIn"

Then If addin.Connect = False Then addin.Connect = True

End If

Next

End Sub

Regards,

Sébastien

former_member186338
Active Contributor
0 Kudos

Please look on the question date 🙂

david_stocker
Contributor
0 Kudos

If you are using the latest AO and BIP combination, you can save your workbooks using a new, AO specific InfoObject type.  This will start Excel with the AO plugin.  You can then leave your startup state registry setting set not to start Analysis every time Excel starts.

Cheers,

Dave