Analysis for Office and Excel VBA – Lessons learnt
After sharing a basic method of automating the updating and distribution of your Analysis for Office files with Excel, this blog covers some of my learnings since my initial posts.
- Post 1 : Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript
- Post 2 : Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript – Part 2
- Post 3 : Analysis for Office Variables and Filters via VBA
I’ve continued to improve and modify the main template in the past few weeks, primarily related to my specific needs at work, but you may also find it useful.
I few weeks ago I purchased SAP BusinessObjects Analysis Office – The Comprehensive Guide by Tobias Meyer and found it to be very useful.
As I progress further on my journey with Analysis I will certainly be drawing on many of the points mentioned in his book. Particularly for manipulating the visual display of data within the various queries.
His website also has a bunch of good blog posts about Analysis. Check it out.
A big thanks to Ivan Bondarenko as well. He pointed out a couple of things I was missing to help improve the template. Primarily the use of CDO to simplify the emailing side of things, and better use of one of the filter functions.
Ivan also has an excellent solution to BOA Automation himself, available on github. It’s certainly worth talking a look at.
So lets move on to the improvements.
Note all of these are incorporated in the new version of the template at the bottom of this blog post. Skip to the bottom if you just want to grab the latest version and work out how it was done yourself.
- Technical Improvement #1 : For a cleaner list of filters, use SAPListOfDynamicFilters instead of SAPListOfEffectiveFilters
- Technical Improvement #2 : Use CDO for emailing within Excel
- Technical Improvement #3 : Correctly determining the number of dimensions in an array
- Functional Improvement #1 : Distributing individual sheets within the AO file
- Functional Improvement #2 : Refreshing any pivots in the file
- Functional Improvement #3 : More dynamic date calculations
Technical Improvement #1 : For a cleaner list of filters, use SAPListOfDynamicFilters instead of SAPListOfEffectiveFilters
This one was courtesy of Ivan Bondarenko.
EffectiveFilters also shows the Variables again (since the variables are a form of filter). We don’t actually want that, as we are handling the variables seperately. There is no point double-handling everything.
So instead of this in our Get_Active_Variables_and_Filters subroutine :
tempFiltersArray = xlApp.Application.Run("SAPListOfEffectiveFilters", thedatasource, "INPUT_STRING")
We now use this :
tempFiltersArray = xlApp.Application.Run("SAPListOfDynamicFilters", thedatasource, "INPUT_STRING")
Technical Improvement #2 : Use CDO for emailing within Excel
This one was also courtesy of Ivan Bondarenko.
CDO has been a round for a while and is best summed up on this page : Ron de Bruin Excel Automation
All it really needs is to be pointed to a smtp server. It doesn’t need Outlook installed or any other 3rd party software like SwithMail.
It will even work happily with Gmail’s SSL (check out the example file on the Ron de Bruin Excel Automation page).
In our template we now allow the user to choose CDO and then call the following subroutine (note CDO_Server is a constant we have defined at the top of our module) :
Private Sub Send_Email_CDO(therecipient As String, thesubject As String, thebody As String, _ theattachment As String) ' This code is from http://www.rondebruin.nl/win/s1/cdo.htm Dim iMsg As Object Dim iConf As Object Dim Flds As Variant Set iMsg = CreateObject("CDO.Message") Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 ' CDO Source Defaults Set Flds = iConf.Fields With Flds .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = CDO_Server .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Update End With With iMsg Set .Configuration = iConf .to = therecipient .CC = "" .BCC = "" .From = "firstname.lastname@example.org" .Subject = thesubject .TextBody = thebody .AddAttachment theattachment .Send End With End Sub
A third technical improvement is in the handling of Arrays, particularly the part where we work out how many dimensions it has so we can treat it accordingly.
Technical Improvement #3 : Correctly determining the number of dimensions in an array
The previous code was a hack. It was basic and worked initially, but has since been shown to be unreliable and produces a ‘Runtime error 13 Type Mismatch’ in certain instances (thanks go to Carolyne for showing that it was happening to her too).
The following function returns the number of Dimensions in an array (from http://stackoverflow.com/questions/24613101/vba-check-if-array-is-one-dimensional) :
Public Function NumberOfArrayDimensions(arr As Variant) As Integer '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' NumberOfArrayDimensions ' This function returns the number of dimensions of an array. An unallocated dynamic array ' has 0 dimensions. This condition can also be tested with IsArrayEmpty. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim Ndx As Integer Dim Res As Integer On Error Resume Next ' Loop, increasing the dimension index Ndx, until an error occurs. ' An error will occur when Ndx exceeds the number of dimension ' in the array. Return Ndx - 1. Do Ndx = Ndx + 1 Res = UBound(arr, Ndx) Loop Until Err.Number <> 0 NumberOfArrayDimensions = Ndx - 1 End Function
Add that function in to the bottom of your module.
Then in the Get_Active_Variables_and_Filters subroutine make the following changes.
Add to the top of the routine :
Dim NumberOfDimensions as Integer
The instead of :
array_start = 1 array_end = Application.CountA(tempVariablesArray) / 2 If array_end = 1 Then ' We have only 1 real record, so we know VariablesArray is ONE dimensional techname = xlApp.Application.Run("SAPGetVariable", thedatasource, tempVariablesArray(1), "TECHNICALNAME") VariablesArray(UBound(VariablesArray)) = techname Else ' We have more than 1 record, so we know VariablesArray is TWO dimensional For arrayloop = array_start To array_end techname = xlApp.Application.Run("SAPGetVariable", thedatasource, tempVariablesArray(arrayloop, 1), "TECHNICALNAME") VariablesArray(UBound(VariablesArray)) = techname Next arrayloop End If
Replace it all with :
NumberOfDimensions = NumberOfArrayDimensions(tempVariablesArray) If NumberOfDimensions = 1 Then ' We have only 1 real record, so we know VariablesArray is ONE dimensional techname = xlApp.Application.Run("SAPGetVariable", thedatasource, tempVariablesArray(1), "TECHNICALNAME") VariablesArray(UBound(VariablesArray)) = techname Else ' We have more than 1 record, so we know VariablesArray is TWO dimensional For arrayloop = LBound(tempVariablesArray) To UBound(tempVariablesArray) techname = xlApp.Application.Run("SAPGetVariable", thedatasource, tempVariablesArray(arrayloop, 1), "TECHNICALNAME") VariablesArray(UBound(VariablesArray)) = techname Next arrayloop End If
Do the same for the filters section in the same subroutine. It will be more reliable, and is now making proper use of the LBound and UBound functionality for looping through the arrays.
Functional Improvement #1 : Distributing individual sheets within the AO file
A real-world scenario for me was a single AO file that had seperate sheets for different areas of the business.
The seperate sheets were all drawing their data from the same shared BW query on another sheet in that file.
I needed to refresh the file from BW, update the pivots on all of the respective sheets then email out each individual sheet in PDF form to different email addresses.
To email each sheet, I’ve added a new column to the parameters file.
When saving or emailing, if the Sheet column is populated it will do the following :
1. Save the whole file to a temporary filename.
2. Load the new file and delete any sheet on it that isn’t the sheet we want.
3. Save and/or email the new file.
Why do we save the whole thing to a temp file first? Because we still want to do more with the original file.
If I have 10 sheets to work through, I want the original refreshed file to still be available to me after I am done with the current sheet.
Functional Improvement #2 : Refreshing any pivots in the file
A new action has been added, “RefreshAllPivots”.
If thats been chosen in the parameter file, it does the following in the VBA code :
All it does is refresh any pivots in the AO file.
Functional Improvement #3 : More dynamic date calculations
The current list of dynamic date calculations is now as follows.
TODAY : Today date. Format DD.MM.YYYY
YESTERDAY : Yesterdays date. Format DD.MM.YYYY
CURRENTDAY : Todays date, but just the day. Format DD
CURRENTMONTH : Current Calendar Month. Format MM.YYYY
CURRENTYEAR : Current Calendar Year. Format YYYY
LASTMONTH : Previous Calendar Month when compared to today. Format MM.YYYY. Same as PREVIOUSCALENDARMONTH.
ENDOFLASTMONTH : The date of the final day of the previous calendar month. Format DD.MM.YYYY
PREVIOUSCALENDARWEEK : The calendar week number for last week. Based on a week starting on Mondays. Format WW.YYYY
PREVIOUSCALENDARWEEKLASTYEAR : Same as PREVIOUSCALENDARWEEK but for the previous year. Format WW.YYYY
PREVIOUSCALENDARMONTH : Same as LASTMONTH. Format MM.YYYY
PREVIOUSCALENDARMONTHLASTYEAR : Same as PREVIOUSCALENDARMONTH but for the previous year. Format MM.YYYY
LASTSUNDAY : The date of the most recent sunday. Format DD.MM.YYYY
LASTSUNDAYLASTYEAR : Same as LASTSUNDAY but for the previous year. Format DD.MM.YYYY
FINANCIALYEAR : The current Fiscal year. Format YYYY.
FIRSTDAYOFFINANCIALYEAR : First day of the current financial year (assuming a July to June financial year). Format DD.MM.YYYY
FIRSTDAYOFFINANCIALYEARLASTYEAR : Same as FIRSTDAYOFFINANCIALYEAR but for the previous year. Format DD.MM.YYYY
You are probably looking at some of them wondering when they would ever be used. Here is an example.
I needed to get current Year to Date figures, but to the end of the most recent week (week finishing on a Sunday). Then needed to also compare that with the same for the previous financial year.
I can’t use Calendar weeks for this as one of the weeks begins before the start of the financial year.
Instead I used the variables in the parameter file as follows :
FIRSTDAYOFFINANCIALYEAR – LASTSUNDAY; FIRSTDAYOFFINANCIALYEARLASTYEAR – LASTSUNDAYLASTYEAR
The VBA code converts this to :
01.07.2016 – 19.02.2017; 01.07.2015 – 19.02.2016
There are still things to learn and improve on, but the changes above help round things out a bit more and improve the stability of it.
Below is version 0.7 of the template along with an example parameter file. I’ve zipped both up for a single download.
thanks for mentioning. A really cool thing are the dynamic date calculations. I build for me the ActualCalendarWeek and the ActualCalendarWeekLastYear to compare revenue of stores in the same week a year before.
Thanks for sharing. This is very useful information!
thanks for sharing your learnings! Could you reupload your file? It seems it had been removed.
Link now updated and should be working again.
I had use MarketXLS. It works great for me.
I can't see how a commercial graphing solution is related to auto-updating of AO files?
Did you post this on the wrong blog?
I have the following crosstab based on Bex Query (and Composite in the backend):
in the Delta% field I need to calculate the difference between Value B of the current date and the Value B of the previous date (the dates are not consecutive), divided by Value B of previous date. In short words (F-J)/J.
The above formula returns correct values only for the first column. The second column formula is automatically filled with (J4-J4)/J4, the next one is (N4-J4)/J4 and so on.
Can you please give me a hint on how I can solve this?
Thanks in advance,
This question might be better posted in the main question area. This blog is primarily about the automation of Analysis for Office files in Excel rather than specific formula questions.
Great work! I do have an issue that perhaps you have seen and can shed some light on.
Excel has been throwing an automation error 429. I think this may be due to patches that my org puts onto my machine which are beyond my control.
Have you encountered this, and, if so, do you have any tips on how to deal with it?
Again, great work!
I haven't come across automation error 429 myself.
If you are using AO, make sure you are on the latest version/patch level.
I found there was a big difference in the stability and reliability between the older and newer versions (now using 2.4.4 but not yet 2.5).
Thanks to your post from series 1 I was able to proof the auto refresh is working within my company (with the vbs file). I also used your "Refresh and Distribute" worksheet successfully - but it works only for one report. If I add a second line, I always get the following Excel error after the refresh of the first workbook: "Microsoft Excel is waiting for another application to complete an OLE action."
It looks to me, that Excel is still busy closing the first workbook in the background, while it tries to open another session for the 2nd report to resfresh.
Any suggestion how to overcome this issue?
Thanks in advance!
Normally the routine will complete the first line before attempting any steps on the subsequent lines.
I've come across the 'waiting to complete an OLE action' message myself when the data it is trying to refresh takes too long to come back from the server. Excel seems to have a specific period it waits before it thinks the other end isn't working any more (even if the second instance of Excel is actually working but just waiting for the data from the BW server).
The only way I was able to get around this (lots of recommendations on the internet didn't work for me) was to restructure the file I was refreshing to be more efficient in its prompts/filters.
Grabbing 'Year to Date' figures across a wide range of the business was one area that took longer than Excel OLE was comfortable with (even though nothing was really wrong with the file itself).
The second file you are trying to refresh, put that in as the first line and see if it still gives you the error. That may then confirm what I've written above.
March 2018 : Note any download link in the blog post points to Dropbox which unfortunately keeps changing the link URL.
The final version of this blog series is 0.7 and is also available at the more permanent link on gt.tt : http://ge.tt/92Kodvo2
Great job!! this automation will help me on BW update tasks.
Could you send it by email or an updated link?
I have a requirement. I have a analysis for office query. There is a dimension, It can be based on Row or column. I want to write a VBA to hide the column/row via lookup the member of dimension.
Let assume there is a dimension “Posting period”. Its member is “April”. If the member is April that April’s Row/Column should hide automatically. And the thing is to remember this action should execute on the start up of the query.
Please help !
Very interesting article thank you !
while implementing your code I have the following error : error 438
in the following code line, can you please help me.
Very nice blog.
Very helpful for SAP consultants with just basic VBA knowledge.
Thank you very much for sharing.