Excel Add-In API: How to update a report by a given date interval?

[Former Member]

Dear all,

I tried to create a VBA macro which does the following:

1. Collect two selection parameters from two excel cells (both parameters are dates, e.g. A1="01.01.2015" and B1="31.01.2015")
2. Update multiple reports in the excel workbook by the given parameters

Important is, that I want to combine both values in a single selection parameter (e.g. posting date = [01.01.2015 .. 31.01.2015].

Moreover, the second step should be done by using the ComVisible functions in the Analytics Add-In.

Here's my samlple VBA code for updating the first report:

Sub UpdateData()

    Dim addinWrapper As AnalyticsAddin
    Dim report As Object
    Dim params As Object
    Dim paramsCollection As Object
    Dim parameter As Object

    Set addinWrapper = New AnalyticsAddin
    Set report = addinWrapper.getReport(Sheets("ReportSheet").Range("A1"))
    Set params = report.getParameters
    Set paramsCollection = params.GetCollection
    Set parameter = paramsCollection(12) ' Posting Date



    ' TODO: To be replaced (dates) by the value of an excel cell
    Call parameter.SetDateValue("01.01.2015", "0") ' Low value
    Call parameter.SetDateValue("31.01.2015", "1") ' High value

    report.Refresh False


End Sub

Unfortunately, I have no idea how I can pass the information about the Inclusion/Exclusion Code [I/E] and the Option [EQ,NE,LE,LT,GE,GT,BT] to the Analytics Add-In.

Does anybody have an idea how to do this?

Thank you very much.

Kai

[Former Member · Accepted solution]

Hi all,

Finally, I have been able to update the parameter values by my VBA coding.

I changed the following lines:

' TODO: To be replaced (dates) by the value of an excel cell  
Call parameter.SetDateValue("01.01.2015", "0") ' Low value  
Call parameter.SetDateValue("31.01.2015", "1") ' High value  

To:

Call Parameter.setSingleValue("01.01.2015", ParameterValueType_LOW)
Call Parameter.setSingleValue("31.01.2015", ParameterValueType_HIGH)

Regards,

Kai

[ivan_bondarenko · Accepted solution]

Great job, Kai! Thank you very much for sharing your experience!

[ivan_bondarenko]

Hi Kai!

Sorry for stupid question. Where did you find AnalyticsAddin class? Is it reference to standard .dll - which one?

Thanks, Ivan

[Former Member]

Hi Ivan,

Thanks for reply. There are no stupid questions

I took part in an Analytics Workshop at SAP in October 2013. After this event, Rainer Feser-Tehranian published an exemplarily Excel Document with Macros. Within this document I found the Analytics Add-In Class (please see the attachment. you would have to create a new class in VBA since I can only upload .txt files).

Moreover, you can call much more functions of the Excel Add-In, but you have to know their names (works with lazy binding). As far as I know, there is no possibility to add a reference to a SAP Excel Add-In .dll within your project. For more information please see also following websites:

• Decompiling .dll files to get source code (i.e. the function names and parameters): Free .NET decompiler :: JetBrains dotPeek
• Communicating with VSTO Add-Ins in VBA: VSTO Add-ins, COMAddIns and RequestComAddInAutomationService - Andrew Whitechapel - Site Home - MSD...

Please do not hesitate to contact me, if you have further questions.

Regards,

Kai

AnalyticsAddin.cls.txt.zip

[ivan_bondarenko]

Thank you very much Kai.

This can help a lot in some solutions. Exactly what I was looking for.

I read about decompilation around two years ago , however haven't found time to make own tests. In addition, no one publish 'ready-to-use' byd-reporting solutions in the internet. Hope community will grow and more info shared.