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:
After this, I needed the actual date and year, so I used the DateValue-function.
Now I need the week, for this I used the WorksheetFunction IsoWeekNum.
So I calculated the week and the year and now we have to put it into the SAPSetFilter.
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: