Analysis Office Get Calendar Week for SAPSetFilter

A few weeks ago, a colleague of mine, ask me how he can get the calendar week to use it in the Analysis Office API SAPSetFilter. I developed a short macro which defines the calendar week to use it in 0CALWEEK. Maybe someone has another solution for this.

First we need different variables:

1
2
3
4

Dim lresult As Long
Dim week As Integer
Dim today As Date
Dim yearCalWeek As String

After this, I needed the actual date and year, so I used the DateValue-function.

1
2

today = DateValue(Now)
yearCalWeek = year(today)

Now I need the week, for this I used the WorksheetFunction IsoWeekNum.

1

week = Application.WorksheetFunction.IsoWeekNum(today)

So I calculated the week and the year and now we have to put it into the SAPSetFilter.

1

lresult = Application.Run("SAPSetFilter", "DS_1", "0CALWEEK", week & yearCalWeek, "INPUT_STRING")

 As you can see it is very easy to get the week from the actual date. Here is the complete source code:

I hope this idea helps someone to build a dynamic report.

These posts might also be interesting:

• Analysis Office Insert Product Image
• Analysis Office 2.4 Table Design features
• Analysis Office Restrictions
• Analysis Office 2.5 DataSource Information

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.