- SAP Managed Tags:
If you have wanted to auto-update your BW files using a dynamic selection of variables and filters, then this blog may be useful to you.
What I’ll cover is how I’ve been able to do this with Excel VBA and Analysis for Office (AO).
My previous posts have covered automated Refreshing of AO and BEX files, and then the auto-distribution of those refreshed files, all via VBA and VBscript.
This blog builds on that and introduces the ability to control the Variables and Filters in those files too, via an external file containing the values you want to use.
An example of where this may be useful is the daily/weekly/monthly distribution of BW reports for a wide range of people that have a wide range of requirements, even though it may be drawing from the same Query/Report each time.
i.e. a Financial Results Report needs to go to 15 different departments with a different selection criteria for each department.
Here is the simplified version of what we are trying to do :
The Distribution Master File will look through a list of AO workbooks and their associated parameter files and process each one.
It will first load the parameter file and put those parameters in memory, then it will then load and refresh the relevant AO file using the parameters.
Those parameters may result in the same original file being manipulated and distributed multiple times.
In this blog we will start small and build on it.
Each of the steps after the first one are optional. The further you go, the more functionality your solution can have.
As with the previous post, at the end is an example template and any supporting files. If you want to skip the 'how is it done' part you can just jump to the end and start using it.
This was all done with Analysis for Office 2.3 and Excel 2010. It should work happily on higher versions of both of those too.
Variables
Setting them
Clearing them
Filters
Setting them
Clearing them
Refresh your datasource before doing anything with it!
You need to refresh your datasource when you first load the workbook before you can do anything with the Variables and Filters.
Speeding things up
Similar to turning off screen updating to speed up excel macros, AO has some options too.
You can prepare a bunch of Variables or Filters and then have the refresh of your data happen just once, instead of updating after each one has been submitted.
For Variables, put this code before :
And this after :
It will turn off refreshing and also the submitting of your variables until you are done. This will let you set up a lot of variables at one time, then do a single refresh at the end.
For Filters, you can just make use of the SAPSetRefreshBehaviour option.
Before your filter :
After your filter :
Why use INPUT_STRING instead of any other field options?
It gives you plenty of control over what is entered but is clean and simple to understand. Nothing wrong with the others but INPUT_STRING seems to be the most flexible and easy to use for my purposes.
A single input string can contain all entries in an easily readable and usable form.
For example : Jan; Mar – Aug; !May; Dec
That example string will include January, March through to August and December, and exclude May.
The AO Plugin user guide has examples of the various entries and symbols you can make use of.
There is however a misleading entry in the AO Plugin user guide.
If doing a value range, be sure to put spaces either side of the dash. Use 1 - 5 not 1-5
Variables and making use of the right technical name
One of the non-obvious things when making use of Variables is choosing the right technical name.
If your Query design has the characteristic making using of a selection variable, you need to use the technical name of the variable, not the technical name of the characteristic.
For example, in the screenshot below instead of using 0PLANT to submit a value for Site, I need to use the selection variable S_SITE instead.
That tip was mentioned in this thread : https://archive.sap.com/discussions/thread/3242990
For filters its easy, you would just use the 0PLANT technical name in this case.
The easiest way to supply and manage multiple variables and filters is via a standard excel table.
I’ve used the following table in my parameters file. It is formatted as a table and has the name PARAMETERS. It is also on a worksheet named ‘Parameters’.
So in the example table above we are separately updating the same file 4 times (4 loops). That file has two data sources in it. We have the following updates happening.
After each Loop is done you can then do anything you want with the file. Save as a new name or email a copy to someone etc.
With a single parameters table, you could have hundreds of loops each doing a different thing on your BW file.
In a single loop there is also nothing to stop you from updating multiple Data sources, like this :
How do we make use of the table? With the following code that loads it up and then loops through it in memory.
At this stage we have our parameters in memory in the field parametersarray.
Now we load the file in a new instance of Excel and make sure AO is running correctly and that we are connected to BW.
We are ready to go. We have our file opened in xlApp and we are connected to BW. We also have our parameters stored in memory in ‘parametersarray’.
We will be adding more to the code below before we are done, so this initially shows the basics of getting it working.
The code above will loop through your parameters table, and for each ‘loop number’ grouping it will process the Variables and then after that process the Filters.
That’s the basics of using Variables/Filters via a table covered.
By default when supplying the contents of the variable/filter fields we need to be very specific. Especially when it comes to dates.
i.e. I use the format dd.mm.yyyy for specific days and mm.yyyy for specific months. Your format will vary depending on your regional settings and the defaults assigned to the BW account you are using.
This is no good to me if I am wanting to change the dates I use each day (without having to manually change them, defeating the purpose of the automation).
To solve this, we can put text in the value field of our parameters, and then replace them behind the scenes before we pass them on to the AO functions.
i.e. TODAY, YESTERDAY, CURRENTMONTH, LASTMONTH, ENDOFLASTMONTH etc
You can set your VBA code to look for those text strings in your submitted values, and replace them with the correct specific days or months.
In the screenshot above for the Date selector field (DAY_SEL) I am saying use YESTERDAY.
In my code, at runtime it will replace YESTERDAY with yesterdays date.
We initially set up what those dates are :
Then when looking at the Value field, we can make a replacement like this :
In our main processing loop we would do the following :
Same for the filters part :
And in our code we put in the following Text_Replace function :
If you are changing the Variables and Filters on the same AO file over and over, you want to be sure that settings used previously are not still lingering around on the file.
You don’t want a selection you used for one department to still be there when generating the data for the next department.
To solve this we introduce the option of clearing out all the Variables or Filters before it starts with the next set of parameters.
There isn’t a direct way to clear everything that I am aware of, so we can instead clear the things that we know are populated.
To do that we read the populated variables and prompts, and then loop through them setting them to "" (double-quotes with no space in between them).
We can also add this feature to our parameters table, so you have full control over it.
We can put in CLEARFILTERS, CLEARVARIABLES and CLEARALL (which clears variables and filters).
We will modify our main loop with the following :
The new subroutines will do two things. Get a list of all the populated Variables and Filters, then Clean them all up.
So how are we getting those lists? The next 3 sections show the basics of doing that.
Getting a list of all of the VARIABLES populated for your datasource
Note we don't care about all of the variables, just the ones that have been populated.
Then if you need to, you can use SAPGetVariable to get the technical info. If we are doing a cleanup we need the technical name.
Getting a list of all of the FILTERS populated on your datasource
Again, we only want the used filters, not the complete list of filters.
Unfortunately this only gives you the non-technical name of the field (such as ‘Site’ instead of ‘0PLANT’)
You then make use of the overall dimension list to get the technical name associated with that filter field.
We go into more detail on this part in our code later on.
Making use of the Variables list and Filters list without having to first display them on a spare worksheet
A number of forum posts and blogs mention a method where you use the AO functions to write out the data to a spare sheet on your file, then re-read the figures from that sheet.
That is perfectly suitable and will get the job done, but you don’t actually need to do that to make use of the figures.
Instead we can make use of Arrays in VBA.
Getting the list of Variables into an array can be done as follows :
In MyVariablesArray, you will now have all the data that would have traditionally been displayed on the spare sheet.
It can now be manipulated and read like any other array.
So with those three things in mind, here is the code we are using in the Get_Active_Variables_and_Filters subroutine.
Note we have defined a global ‘VariablesArray’ and ‘FiltersArray’ fields previously that this code will use (Dim VariablesArray() As String, FiltersArray() As String).
That gives us VariablesArray and FiltersArray now populated with things we can clear.
Those two arrays only hold the technical name of the Variables and Filters. We haven't tried to keep track of what they have in them currently because we are planning on clearing them out anyway.
We will clear it with the following subroutine, which is basically the same as setting the fields, but this time with "" instead of a value.
In the previous blog I covered a way to save/change format/email the refreshed BW workbooks automatically.
That was controlled via a central Master Table, and only did one thing to one file at a time.
If we are now also using a parameters table that can change the one file multiple times, we need to move our saving and email options to the parameter file.
I’m adding the entries in green below to the parameters file.
In our main loop, we would then detect the ACTION type and do things accordingly.
Below is a template that makes use of all of the Steps mentioned above, along with an example Parameters file.
It builds on what was done in the previous two blogs and adds in the new functionality from this blog.
Our now modified Distribution Master File has a new column called ‘AO Parameter File’.
If that column is populated with a parameter filename (and full path to it), then it will use that Parameter file instead of the rest of the info on that line.
The screenshot below shows just 1 entry with the parameters being used, though you can do it for any of the AO files in the list.
Click the link below for the main template, and then the one below that for an example parameters file.
They should be enough to get you started.
As always any questions, fixes and improvements are welcomed via the comments section below.
Previous Blogs :
Part 1 : Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript
Part 2 : Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript – Part 2
The next part of this topic is available here : Analysis for Office and Excel VBA – Lessons learnt
What I’ll cover is how I’ve been able to do this with Excel VBA and Analysis for Office (AO).
My previous posts have covered automated Refreshing of AO and BEX files, and then the auto-distribution of those refreshed files, all via VBA and VBscript.
This blog builds on that and introduces the ability to control the Variables and Filters in those files too, via an external file containing the values you want to use.
An example of where this may be useful is the daily/weekly/monthly distribution of BW reports for a wide range of people that have a wide range of requirements, even though it may be drawing from the same Query/Report each time.
i.e. a Financial Results Report needs to go to 15 different departments with a different selection criteria for each department.
Here is the simplified version of what we are trying to do :
The Distribution Master File will look through a list of AO workbooks and their associated parameter files and process each one.
It will first load the parameter file and put those parameters in memory, then it will then load and refresh the relevant AO file using the parameters.
Those parameters may result in the same original file being manipulated and distributed multiple times.
In this blog we will start small and build on it.
- First Step – The basics of Variables and Filters via VBA
- Second Step – Supplying Variables and Filters in bulk
- Third Step – Dynamic Variables and Filters
- Fourth Step – Clearing out previous settings before using new ones
- Fifth Step – Saving/Emailing
- Sixth Step – Putting it all together
Each of the steps after the first one are optional. The further you go, the more functionality your solution can have.
As with the previous post, at the end is an example template and any supporting files. If you want to skip the 'how is it done' part you can just jump to the end and start using it.
This was all done with Analysis for Office 2.3 and Excel 2010. It should work happily on higher versions of both of those too.
First Step - The basics of Variables and Filters via VBA
Variables
Setting them
Application.Run("SAPSetVariable", <variable technical name>, <variable value>, "INPUT_STRING", <variable datasource>)Clearing them
Application.Run("SAPSetVariable", <variable technical name>, "", "INPUT_STRING", <variable datasource>)Filters
Setting them
Application.Run("SAPSetFilter", <filter datasource>, <filter technical name>, <filter value>, "INPUT_STRING")Clearing them
Application.Run("SAPSetFilter", <filter datasource>, <filter technical name>, "", "INPUT_STRING")Refresh your datasource before doing anything with it!
You need to refresh your datasource when you first load the workbook before you can do anything with the Variables and Filters.
Application.Run("SAPExecuteCommand", "RefreshData")Speeding things up
Similar to turning off screen updating to speed up excel macros, AO has some options too.
You can prepare a bunch of Variables or Filters and then have the refresh of your data happen just once, instead of updating after each one has been submitted.
For Variables, put this code before :
Call Application.Run("SAPSetRefreshBehaviour", "Off")
Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")And this after :
Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off")
Call Application.Run("SAPSetRefreshBehaviour", "On")It will turn off refreshing and also the submitting of your variables until you are done. This will let you set up a lot of variables at one time, then do a single refresh at the end.
For Filters, you can just make use of the SAPSetRefreshBehaviour option.
Before your filter :
Call Application.Run("SAPSetRefreshBehaviour", "Off")After your filter :
Call Application.Run("SAPSetRefreshBehaviour", "On")Additional points
Why use INPUT_STRING instead of any other field options?
It gives you plenty of control over what is entered but is clean and simple to understand. Nothing wrong with the others but INPUT_STRING seems to be the most flexible and easy to use for my purposes.
A single input string can contain all entries in an easily readable and usable form.
For example : Jan; Mar – Aug; !May; Dec
That example string will include January, March through to August and December, and exclude May.
The AO Plugin user guide has examples of the various entries and symbols you can make use of.
There is however a misleading entry in the AO Plugin user guide.
If doing a value range, be sure to put spaces either side of the dash. Use 1 - 5 not 1-5
Variables and making use of the right technical name
One of the non-obvious things when making use of Variables is choosing the right technical name.
If your Query design has the characteristic making using of a selection variable, you need to use the technical name of the variable, not the technical name of the characteristic.
For example, in the screenshot below instead of using 0PLANT to submit a value for Site, I need to use the selection variable S_SITE instead.
That tip was mentioned in this thread : https://archive.sap.com/discussions/thread/3242990
For filters its easy, you would just use the 0PLANT technical name in this case.
Second Step – Supplying Variables and Filters in bulk
The easiest way to supply and manage multiple variables and filters is via a standard excel table.
I’ve used the following table in my parameters file. It is formatted as a table and has the name PARAMETERS. It is also on a worksheet named ‘Parameters’.
- Loop Number : Each update will use a unique loop number. Anything with the same loop number will be processed as the same group.
- Data Source : The AO data source technical name in the file you are refreshing.
- Type : Says whether we want to update a Variable or a Filter
- Field/Selection Variable : The technical name of the Variable or Filter
- Value : The value you want to use.
So in the example table above we are separately updating the same file 4 times (4 loops). That file has two data sources in it. We have the following updates happening.
- Loop 1 : Update two variables for DS_1
- Loop 2 : Update one variable and one filter for DS_1
- Loop 3 : Update one variable for DS_2
- Loop 4 : Update one variable and one filter for DS_2
After each Loop is done you can then do anything you want with the file. Save as a new name or email a copy to someone etc.
With a single parameters table, you could have hundreds of loops each doing a different thing on your BW file.
In a single loop there is also nothing to stop you from updating multiple Data sources, like this :
How do we make use of the table? With the following code that loads it up and then loops through it in memory.
' Set up some variables first
Dim lresult, mytable As ListObject
Dim paramatersarray As Variant
Dim Field_Value As String, Filter_Value As String, Field_Datasource As String, Field_Type As String
Dim wb As Workbook
' Lets put the PARAMETERS table into memory in an internal array
' We only want the data, not the headings, so we refer to DataBodyRange on the table
' open the parameters file read only
Set wb = Workbooks.Open(<path and filename of your parameter file>, True, True)
' Now take a copy of the parameters table into memory
Set mytable = wb.Sheets("Parameters").ListObjects("PARAMETERS")
paramatersarray = mytable.DataBodyRange
' Now close the parameters file and clear out the memory
wb.Close False
Set wb = Nothing
At this stage we have our parameters in memory in the field parametersarray.
Now we load the file in a new instance of Excel and make sure AO is running correctly and that we are connected to BW.
' Start a new instance of excel, load up the file we want to refresh
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wb2 = xlApp.Workbooks.Open(<the data file you want to refresh>, False, False)
' Force the Analysis for Office addin to be enabled on the second instance of Excel
xlApp.Application.StatusBar = "Making sure Analysis for Office addin is active..."
For Each AddIn In xlApp.Application.COMAddIns
If AddIn.progID = "SapExcelAddIn" Then
If AddIn.Connect = False Then
AddIn.Connect = True
ElseIf AddIn.Connect = True Then
AddIn.Connect = False
AddIn.Connect = True
End If
End If
Next
' Now log in to the BW system
lresult = xlApp.Application.Run("SAPLogon", "DS_1", bw_client, bw_user, bw_password)
' Refresh all of the data sources in the whole workbook so we can start making use of it
lresult = xlApp.Application.Run("SAPExecuteCommand", "RefreshData")
We are ready to go. We have our file opened in xlApp and we are connected to BW. We also have our parameters stored in memory in ‘parametersarray’.
We will be adding more to the code below before we are done, so this initially shows the basics of getting it working.
' Now lets loop through the parameters array.
' LBound = Lower Bound, the lowest record number (in this case 1)
' UBound = Upper Bound, the highest record number
myloop = 0 ' setting this to something that won't match during the first loop, as we know everything starts from 1 in the parameter file
For mainloop = LBound(paramatersarray) To UBound(paramatersarray)
' put the fields into easier variables for the moment
Field_Loopnum = paramatersarray(mainloop, 1)
Field_Datasource = paramatersarray(mainloop, 2)
Field_Type = paramatersarray(mainloop, 3)
Field_Field = paramatersarray(mainloop, 4)
Field_Value = paramatersarray(mainloop, 5)
' We also want to see if there is a 'next' record, to determine if we are on the last record of the current loop.
If (mainloop + 1) > UBound(paramatersarray) Then
Field_Loopnum_next = "No more records" ' Doesn't matter what this is, as long as its not the number of the last loop entry
Else
Field_Loopnum_next = paramatersarray(mainloop + 1, 1)
End If
If Field_Loopnum <> myloop Then
' get ready for a new set of variables
' We are going to process variables first so lets turn off variable submissions to keep the speed up
Call xlApp.Application.Run("SAPSetRefreshBehaviour", "Off")
Call xlApp.Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")
End If
' If we find a VARIABLE field, action it.
If Field_Type = "VARIABLE" Then
lresult = xlApp.Application.Run("SAPSetVariable", Field_Field, Field_Value, "INPUT_STRING", Field_Datasource)
End If
If Field_Loopnum <> Field_Loopnum_next Then
' the next row in our parameters table has a different loop number, so lets unpause things to refresh the data
Call xlApp.Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off")
' At this stage we have set up refreshed the data for variables, but the user may want to then set some filters.
' Loop through the same parameters array from the start again, but this time for the same loop number as the variables and look for filters this time.
For i = LBound(paramatersarray) To UBound(paramatersarray)
Filter_Loopnum = paramatersarray(i, 1)
Filter_DataSource = paramatersarray(mainloop, 2)
Filter_Type = paramatersarray(mainloop, 3)
Filter_Field = paramatersarray(mainloop, 4)
Filter_Value = paramatersarray(mainloop, 5)
If Filter_Loopnum = Field_Loopnum And Filter_Type = "FILTER" Then
lresult = xlApp.Application.Run("SAPSetFilter", Filter_DataSource, Filter_Field, Filter_Value, "INPUT_STRING")
End If
Next i
' We are all done with the filters for this specific loop Now refresh whats on screen
Call xlApp.Application.Run("SAPSetRefreshBehaviour", "On")
' ***************************************************************************
' At this point we now have a refreshed file with all the new variables and filters.
' Now you can save/saveas/email here before you move on to the next loop.
' ***************************************************************************
End If
' make a note of the loop we just finished as we will check it in the next loop
myloop = Field_Loopnum
Next mainloopThe code above will loop through your parameters table, and for each ‘loop number’ grouping it will process the Variables and then after that process the Filters.
That’s the basics of using Variables/Filters via a table covered.
Third Step – Dynamic Variables and Filters
By default when supplying the contents of the variable/filter fields we need to be very specific. Especially when it comes to dates.
i.e. I use the format dd.mm.yyyy for specific days and mm.yyyy for specific months. Your format will vary depending on your regional settings and the defaults assigned to the BW account you are using.
- 20.01.2017
- 01.2017
This is no good to me if I am wanting to change the dates I use each day (without having to manually change them, defeating the purpose of the automation).
To solve this, we can put text in the value field of our parameters, and then replace them behind the scenes before we pass them on to the AO functions.
i.e. TODAY, YESTERDAY, CURRENTMONTH, LASTMONTH, ENDOFLASTMONTH etc
You can set your VBA code to look for those text strings in your submitted values, and replace them with the correct specific days or months.
In the screenshot above for the Date selector field (DAY_SEL) I am saying use YESTERDAY.
In my code, at runtime it will replace YESTERDAY with yesterdays date.
We initially set up what those dates are :
yesterday = Format(Date - 1, "dd.mm.yyyy")
today = Format(Date, "dd.mm.yyyy")
currentmonth = Format(Date, "mm.yyyy")
lastmonth = Format(Application.WorksheetFunction.EoMonth(Date, -1), "mm.yyyy")
endoflastmonth = Format(DateSerial(Year(Date), Month(Date), 0), "dd.mm.yyyy")Then when looking at the Value field, we can make a replacement like this :
If <value field> Like "*TODAY*" Then
<value field> = Replace(<value field>, "TODAY", today)
End IfIn our main processing loop we would do the following :
If Field_Type = "VARIABLE" Then
' see if we need to replace some date texts with actual dates
Field_Value = Text_Replace(Field_Value)
lresult = xlApp.Application.Run("SAPSetVariable", Field_Field, Field_Value, "INPUT_STRING", Field_Datasource)
End IfSame for the filters part :
If Filter_Loopnum = Field_Loopnum And Filter_Type = “FILTER” Then
' see if we need to replace some date texts with actual dates
Filter_Value = Text_Replace(Filter_Value)
lresult = xlApp.Application.Run("SAPSetFilter", Filter_DataSource, Filter_Field, Filter_Value, "INPUT_STRING")
End IfAnd in our code we put in the following Text_Replace function :
Public Function Text_Replace(thetext As String) As String
If thetext Like "*TODAY*" Then
thetext = Replace(thetext, "TODAY", today)
End If
If thetext Like "*YESTERDAY*" Then
thetext = Replace(thetext, "YESTERDAY", yesterday)
End If
If thetext Like "*CURRENTMONTH*" Then
thetext = Replace(thetext, "CURRENTMONTH", currentmonth)
End If
If thetext Like "*ENDOFLASTMONTH*" Then
thetext = Replace(thetext, "ENDOFLASTMONTH", endoflastmonth)
End If
If thetext Like "*LASTMONTH*" Then
thetext = Replace(thetext, "LASTMONTH", lastmonth)
End If
Text_Replace = thetext
End FunctionFourth Step – Clearing out previous settings before using new ones
If you are changing the Variables and Filters on the same AO file over and over, you want to be sure that settings used previously are not still lingering around on the file.
You don’t want a selection you used for one department to still be there when generating the data for the next department.
To solve this we introduce the option of clearing out all the Variables or Filters before it starts with the next set of parameters.
There isn’t a direct way to clear everything that I am aware of, so we can instead clear the things that we know are populated.
To do that we read the populated variables and prompts, and then loop through them setting them to "" (double-quotes with no space in between them).
We can also add this feature to our parameters table, so you have full control over it.
We can put in CLEARFILTERS, CLEARVARIABLES and CLEARALL (which clears variables and filters).
We will modify our main loop with the following :
If Field_Loopnum <> myloop Then
' get ready for a new set of variables for potentially a new DataSource too
' Lets see if we need to undo any variables and filters first
Get_Active_Variables_and_Filters Field_Datasource
Cleanup_the_Datasource Field_Type, Field_Datasource
Call xlApp.Application.Run("SAPSetRefreshBehaviour", "Off")
Call xlApp.Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")
End IfThe new subroutines will do two things. Get a list of all the populated Variables and Filters, then Clean them all up.
So how are we getting those lists? The next 3 sections show the basics of doing that.
Getting a list of all of the VARIABLES populated for your datasource
Note we don't care about all of the variables, just the ones that have been populated.
Application.Run(“SAPListOfVariables”, <datasource>, "INPUT_STRING", "ALL_FILLED")Then if you need to, you can use SAPGetVariable to get the technical info. If we are doing a cleanup we need the technical name.
Application.Run(“SAPGetVariable”, <datasource>, <non-technical name>, "TECHNICALNAME")Getting a list of all of the FILTERS populated on your datasource
Again, we only want the used filters, not the complete list of filters.
Application.Run(“SAPListOfEffectiveFilters”, <datasource>, "INPUT_STRING")Unfortunately this only gives you the non-technical name of the field (such as ‘Site’ instead of ‘0PLANT’)
You then make use of the overall dimension list to get the technical name associated with that filter field.
Application.Run("SAPListOfDimensions", <datasource>)We go into more detail on this part in our code later on.
Making use of the Variables list and Filters list without having to first display them on a spare worksheet
A number of forum posts and blogs mention a method where you use the AO functions to write out the data to a spare sheet on your file, then re-read the figures from that sheet.
That is perfectly suitable and will get the job done, but you don’t actually need to do that to make use of the figures.
Instead we can make use of Arrays in VBA.
Getting the list of Variables into an array can be done as follows :
Dim MyVariablesArray As Variant
MyVariablesArray = Application.Run("SAPListOfVariables", <datasource>)In MyVariablesArray, you will now have all the data that would have traditionally been displayed on the spare sheet.
It can now be manipulated and read like any other array.
So with those three things in mind, here is the code we are using in the Get_Active_Variables_and_Filters subroutine.
Note we have defined a global ‘VariablesArray’ and ‘FiltersArray’ fields previously that this code will use (Dim VariablesArray() As String, FiltersArray() As String).
Sub Get_Active_Variables_and_Filters(ByVal thedatasource As String)
Dim tempVariablesArray As Variant, tempFiltersArray As Variant
Dim DimensionArray As Variant, x As Long
Dim techname As String, mysource As String
' If there is only 1 Variable, then SAPListOfVariables returns a one dimensional array. LBound = 1, UBound = 2
' If there is more than 1 Variable, SAPListOfVariables returns a two dimensional array.
' Same thing happens for SAPListOfEffectiveFilters
' Lets start by grabbing the Variables, Filters and Dimensions into seperate internal arrays.
tempVariablesArray = xlApp.Application.Run("SAPListOfVariables", thedatasource)
tempFiltersArray = xlApp.Application.Run("SAPListOfEffectiveFilters", thedatasource, "INPUT_STRING")
DimensionArray = xlApp.Application.Run("SAPListOfDimensions", thedatasource)
' We want to add to the correct global arrays, so start from row 1
Erase VariablesArray
Erase FiltersArray
ReDim VariablesArray(1 To 1) As String
ReDim FiltersArray(1 To 1) As String
' We grab the Dimensions info because that also holds the technical names of the filters.
' There is no SAPGetFilter function, so we'll make use of the Dimension list instead
'************
' VARIABLES
'************
' Lets count how many records there are in the Variables array
' We know each row has 2 fields, so we divide by 2 later to get the actual number of rows
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")
' add the variable technical name to our global VariableArray
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")
' add the variable technical name to our global VariableArray
VariablesArray(UBound(VariablesArray)) = techname
Next arrayloop
End If
'**********
' FILTERS
'**********
' Lets count how many records there are in the Filters array
' We know each row has 2 fields, so we divide by 2 later to get the actual number of rows
array_start = 1
array_end = Application.CountA(tempFiltersArray) / 2
If array_end = 1 Then
' We have only 1 real record, so we know FiltersArray is ONE dimensional
' Now lets get the technical name of the filter from the DimensionArray by looping through it
' until we find a match.
techname = ""
For dimensionloop = LBound(DimensionArray) To UBound(DimensionArray)
If DimensionArray(dimensionloop, 2) = FiltersArray(1) Then
techname = DimensionArray(dimensionloop, 1)
Exit For
End If
Next dimensionloop
' add the filter to our global FiltersArray
FiltersArray(UBound(FiltersArray)) = techname
Else
' We have more than 1 real record, so we know FiltersArray is TWO dimensional
For arrayloop = array_start To array_end
' Now lets get the technical name of the filter from the DimensionArray by looping through it
' until we find a match.
techname = ""
For dimensionloop = LBound(DimensionArray) To UBound(DimensionArray)
If DimensionArray(dimensionloop, 2) = tempFiltersArray(arrayloop, 1) Then
techname = DimensionArray(dimensionloop, 1)
Exit For
End If
Next dimensionloop
' add the filter to our global FiltersArray
FiltersArray(UBound(FiltersArray)) = techname
Next arrayloop
End If
' Lets do some cleanup on the temporary arrays
On Error Resume Next
Erase tempVariablesArray
Erase tempFiltersArray
Erase DimensionArray
End SubThat gives us VariablesArray and FiltersArray now populated with things we can clear.
Those two arrays only hold the technical name of the Variables and Filters. We haven't tried to keep track of what they have in them currently because we are planning on clearing them out anyway.
We will clear it with the following subroutine, which is basically the same as setting the fields, but this time with "" instead of a value.
Private Sub Cleanup_the_Datasource(cleanuptype As String, thedatasource As String)
If cleanuptype = "CLEARALL" Then
' Lets loop through our filters array and clear them out
For i = LBound(FiltersArray) To UBound(FiltersArray)
lresult = xlApp.Application.Run("SAPSetFilter", thedatasource, FiltersArray(i), "", "INPUT_STRING")
Next i
' Pause any refreshing while we work on clearing our variables
Call xlApp.Application.Run("SAPSetRefreshBehaviour", "Off")
Call xlApp.Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")
' Loop through the Variables array and clear them out
For i = LBound(VariablesArray) To UBound(VariablesArray)
lresult = xlApp.Application.Run("SAPSetVariable", VariablesArray(i), "", "INPUT_STRING", thedatasource)
Next i
' Temporarily turn back on refreshing to update things
Call xlApp.Application.Run("SAPSetRefreshBehaviour", "On")
Call xlApp.Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off")
' And then turn it off again
Call xlApp.Application.Run("SAPSetRefreshBehaviour", "Off")
Call xlApp.Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")
ElseIf cleanuptype = "CLEARVARIABLES" Then
' Pause any refreshing while we work on clearing our variables
Call xlApp.Application.Run("SAPSetRefreshBehaviour", "Off")
Call xlApp.Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")
' Loop through the Variables array and clear them out
For i = LBound(VariablesArray) To UBound(VariablesArray)
lresult = xlApp.Application.Run("SAPSetVariable", VariablesArray(i), "", "INPUT_STRING", thedatasource)
Next i
' Temporarily turn back on refreshing to update things
Call xlApp.Application.Run("SAPSetRefreshBehaviour", "On")
Call xlApp.Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off")
' And then turn it off again
Call xlApp.Application.Run("SAPSetRefreshBehaviour", "Off")
Call xlApp.Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")
ElseIf cleanuptype = "CLEARFILTERS" Then
' Lets loop through our filters array and clear them out
For i = LBound(FiltersArray) To UBound(FiltersArray)
lresult = xlApp.Application.Run("SAPSetFilter", thedatasource, FiltersArray(i), "", "INPUT_STRING")
Next i
End If
End SubFifth Step – Saving/Emailing
In the previous blog I covered a way to save/change format/email the refreshed BW workbooks automatically.
That was controlled via a central Master Table, and only did one thing to one file at a time.
If we are now also using a parameters table that can change the one file multiple times, we need to move our saving and email options to the parameter file.
I’m adding the entries in green below to the parameters file.
- Loop Number
- Data Source
- Type
- Field/Selection Variable
- Value
- Action
- New Filename
- Add Current Date to Filename
- File Type
- Email Type
- Email - Display/Send
- Email Addresses
- Email Subject
- Email Message Body
In our main loop, we would then detect the ACTION type and do things accordingly.
' ***************************************************************************
' At this point we now have a refreshed file with all the new variables and filters.
' Now you can save/saveas/email here before you move on to the next loop.
' ***************************************************************************
' Now lets see if there is an action we need to perform on this loop.
' Similar to what we did for the filters, lets do the same for the Action.
For i = LBound(paramatersarray) To UBound(paramatersarray)
Action_Loopnum = paramatersarray(i, 1)
Action_DataSource = paramatersarray(mainloop, 2)
Action_Type = paramatersarray(mainloop, 3)
Action_Field = paramatersarray(mainloop, 4)
Action_Value = paramatersarray(mainloop, 5)
If Action_Loopnum = Field_Loopnum And Filter_Type = "ACTION" Then
' lets read all the action stuff and do something with it.
Action_Action = paramatersarray(i, 6)
Action_NewFilename = paramatersarray(i, 7)
Action_AddDate = paramatersarray(i, 😎
Action_FileType = paramatersarray(i, 9)
Action_EmailType = paramatersarray(i, 10)
Action_EmailDisplay = paramatersarray(i, 11)
Action_EmailAddress = paramatersarray(i, 12)
Action_EmailSubject = paramatersarray(i, 13)
Action_EmailMessage = paramatersarray(i, 14)
' now this is where you make use of the fields and call the relevant saving and emailing subroutines
End If
Next iSixth Step – Putting it all together
Below is a template that makes use of all of the Steps mentioned above, along with an example Parameters file.
It builds on what was done in the previous two blogs and adds in the new functionality from this blog.
- Is it bug free? No probably not.
- Is it elegantly coded? Maybe, but as long as it works the elegance of the code is of secondary importance.
- Can it be more efficient? Yes most likely.
Our now modified Distribution Master File has a new column called ‘AO Parameter File’.
If that column is populated with a parameter filename (and full path to it), then it will use that Parameter file instead of the rest of the info on that line.
The screenshot below shows just 1 entry with the parameters being used, though you can do it for any of the AO files in the list.
Click the link below for the main template, and then the one below that for an example parameters file.
They should be enough to get you started.
As always any questions, fixes and improvements are welcomed via the comments section below.
Previous Blogs :
Part 1 : Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript
Part 2 : Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript – Part 2
The next part of this topic is available here : Analysis for Office and Excel VBA – Lessons learnt
46 Comments
