Skip to Content
0
Former Member
Jun 29, 2015 at 05:07 PM

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

613 Views

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