Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Analysis for Office and Excel VBA - Lessons learnt

reganmacdonald
Participant
‎2017 Feb 20 9:53 AM
29,261
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.

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 reyemsaibot 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.

18 Comments
Labels in this area
Popular Blog Posts