AO 2.0 Addin VBA Functions

[reyemsaibot]

Hi everyone,

I use the VBA-Code to load the AO 2.0 Addin

Private Sub EnableAnalysisOffice()

    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

This works perfect, but when I want to use the Analysis Functions like =SAPListOF or something else, I get an error that there are no macros available.

When I look in the functions I see the following picture.

There is no Analysis Functions. The same Coding works in AO 1.4 perfect.

Did I miss something? Any suggestions?

Thanks for your help.

Tobias

[reyemsaibot · Accepted solution]

The solution is to make always a refresh after the loading of the addin.

[sebastian_keim]

I have the same issue: in AO 1.4 running  "SAPLogon" after activating the AddIn as described above worked fine. But after installing AO 2.1 the "Macro" error pops up.

The scenario I use is starting the Workbook using MS Task Scheduler and it is essential to have all the functions available right after start-up.

Again, in 1.4 -> no Problem as Tobias described.

Regards

Sebastian

[Former Member]

Hi,

we have a VB.net Application which uses the Analysis Addin (Version 2.1.2.49735).

Activation of the Addin only works, when excel is visible while a workbook is added.

This works:

Dim objExcel = CType(CreateObject("Excel.Application"), Excel.Application)
objExcel.Visible = True
objExcel.Workbooks.Add()


// Now activate the addin

This doesnt work:

Dim objExcel = CType(CreateObject("Excel.Application"), Excel.Application)
objExcel.Visible = False
objExcel.Workbooks.Add()


// Now activate the addin

When the LoadBehavior (Registry Path HKEY_CURRENT_USER/Software/Microsoft/Office/Excel/Addins/SapExcelAddIn) is set to 3,

activation of the addin only works the following way:

For Each addin In objExcel.COMAddIns
   If addin.ProgId = "SapExcelAddIn" Then
       If addin.Connect = False Then
           addin.Connect = True
           Exit For
       ElseIf addin.Connect = True Then
          'This shouldnt be necessary, but otherwhise the analysis tab is not shown in excel
          addin.Connect = False
          addin.Connect = True
          Exit For
       End If
   End If
Next

[former_member110741]

Hi Tobias Meyer,

Analysis Office Functions will load whenever you perform any analysis Office operation

Initially analysis office loads only the add-in(plugin) but not analysis functions

This is because to improve the performance, to make you understand

1. launch analysis office

2. check for the functions - it will not be available

Here only plugin is activated but analysis functions are not loaded

Try this :

1. launch analysis office

2. Just click on Insert analysis (or )click on File-.>analysis->About analysis (or) perform any AO operation(connect the system)

3. Now check for the analysis functions

After Step 2 Analysis Functions will get loaded

Based on this, for your issue, i can understand you have just activated plugin and you are accessing analysis office functions before it get loaded. To solve it, implement your code such a way that after plugin activated perform any AO operation(for ex.Connecting to system) and then try Analysis office functions

PS : let me know once you are done, i am curious to know the result 🙂

Regards,

Subhash

[reyemsaibot]

HI Subhash,

you are right. When I click for example on File->analysis->About analysis. I get the functions. But the problem is I want to automize a workbook refresh. When I use AO1.4 this work with the following my VBA-Code

Private Sub EnableAnalysisOffice()

    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

Dim lresult As Long

lresult = Application.Run("SAPListOF", "DATASOURCES", , "DESCRIPTION")

End Sub

In AO 2.0 I get the following error, that the macros are not in the workbook

Yes I think you are right about the activation. How do I activate it? Which operation activate the AO Plugin? The connection like SAPLogon doesn't work, same error like the picture below.

[Former Member]

Hi Tobias,

the solution for your Problem is that you Need a Refresh for one (or every) DS in your Excel:

Next

Dim lresult As Long

Put in here:

lresult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")

before you do:

lresult = Application.Run("SAPListOF", "DATASOURCES", , "DESCRIPTION")

If you have many datasources, you can do it with "do ... loop" for dynamic: "DS_" & i)

The "Refresh"-Problem is well-known and also exists at the Ideaplace already.

--> You always need to do the refresh, first.

Best regards, Martin

[reyemsaibot]

Hi Martin,

thanks for your Post, but if you read my previous post, it doesn't matter what command I suppose. Excel says the macro does not exist. See the following screenshot. The problem is how Subhash has been found, it is loaded but not activated. My actual problem is how to activate it.

Tobias

[former_member110741]

Hi Tobias Meyer,

i feel there is no any scenario after addin is activated immediately performing any analysis functions(sap list..).May be you can mention your complete scenario to try.

FYI..

After add-in is activated you want to use SAPListof, but in order to use SAPList of you need DS_1(data source)

Once you connect to Data source/system, then AO loads the functions automatically. you can use your VBA's

Regards,

Subhash

[Former Member]

Hi Tobias,

when the error occurs, was it with your code or mine?

Have you clicked on Debug to see in which line?

If it was in your coding, I assume it was on line:

lresult = Application.Run("SAPListOF", "DATASOURCES", , "DESCRIPTION")

If you put a Refresh upfront, the error should not appear.

The problem with AO is that in some versions something works as expected and in the next it doesn't anymore ... obviously SAP thinks "doesn't matter, we provide the next patch soon, anyway"...

But the issue is, we as customers are not able to roll-out every version to our users... so we skip one or two Releases and can just hope that everything works in the one we are using.

Also @ Sebastian: So even if it worked in 1.4 and not in 2.0, you can complain and hope for the next version or you just try to work with it.

Might be I misunderstood something, since I did not rebuild your case in Excel... but Subash told you once again, what I said before?! Refresh = Connect to Datasource/System.

Regards, Martin

[reyemsaibot]

Hi Martin,

it works in 2.0 SP0 but SP3 does not work. Maybe I just wait and use SP0

You are right about this:

The problem with AO is that in some versions something works as expected and in the next it doesn't anymore ... obviously SAP thinks "doesn't matter, we provide the next patch soon, anyway"...

Thanks for your help

Tobias