Skip to Content

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.

  • 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 mainloop

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.

 

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 If

In 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 If

Same 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 If

And 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 Function

 

Fourth 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 If

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.

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 Sub

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.

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 Sub

 

Fifth 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, 8)
                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 i

 

Sixth 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

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply