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.

General Notes

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 = "sendersaddress@example.com"
        .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 :

xlApp.ActiveWorkbook.RefreshAll

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.