Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript – Part 2
In my previous blog we used VBscript and VBA macros to update BW workbooks in BEX and Analysis for Office.
Its a simple and low cost way to auto-update your BW workbooks.
This blog will expand on that idea and include a more elegant way to do all that and also distribute those refreshed workbooks.
At the end of this you’ll have the knowledge on how to make an Excel template that can do the following via a simple table, some VBA and ultimately a single button click (or scheduled task) :
- Refresh BEX and AO files.
- Save them to different filenames
- Save them to different formats (xlsm, xlsx, xls, pdf)
- Email them via Outlook or Gmail
- Use combinations of the first 4 points.
The complete example template will also be supplied so you can make use of it or pull it apart to confirm how it ticks. Skip to the end if you just want to grab it and start playing around.
This is a long blog. Its not a quick read. I’ve written it so even those with a minimal understanding of VBA can make it work and more importantly understand how and why it is working.
Here is what will be covered :
- How is it going to work
- How is it built
- Distribution Master File (DMF)
- Emailing our files
- BEX Files
- AO Files (update Jan 18th 2017 – Can now leave AO files untouched)
- Triggering the DMF from a VBscript
- Triggering the VBscript from Windows Task Scheduler
- Distribution Master File (DMF)
- Securing your VBA code from prying eyes
- Advanced Scheduling
- Status Reporting
- The Example DMF File
Jan 19th 2017 update :
Originally we needed to add some macros to the AO files to ensure they function correctly. This is no longer the case.
We can now call all of the relevant macros from our main DMF file.
The original guide below has now been adjusted to take that into account.
Feb 3rd 2017 update :
Part 3 of this series is available here : Analysis for Office Variables and Filters via VBA
How is it going to work?
There will be a single table in excel that has a list of the workbooks we want to refresh, as well as what we want to do with them after the refresh is complete.
Our VBA code will work sequentially through that table and action each line as it goes.
Depending on what has been chosen, it will trigger a second instance of Excel to do all the refreshing and saving, and then trigger the relevant email program to distribute them.
How is it built?
Lets start with the Distribution Master File (DMF) from our diagram above. This is the most complex part. Everything else we need to do outside of that file we have covered in the previous blog, so those parts will be relatively easy when we come to them.
Distribution Master File
The DMF is a single sheet, macro enabled workbook. It has a single table inside it that looks like this (not all columns shown).
The columns and their purpose are as follows :
When set to YES, that line will be paid attention to.
When set to NO, it will be ignored by our VBA code. This allows you to put everything in the one file but not necessarily have it active all at once.
2. Filename to load and action
This is the filename of the BW BEX/AO workbook you are wanting to load and refresh. It should be the full path, filename and extension.
It will happily use either UNC names (\\) or drive letters.
You need to specify if the file you are loading is a BEX or AO file. This is so the VBA code knows how to handle it.
This is what you want to do with the BW file.
- Refresh : Load the file, refresh it from BW, then save it over the same filename.
- Refresh & SaveAs : Load the file, refresh it, then save it under a new filename and format.
- Refresh & Email : Load the file, refresh it, optionally save it under a new filename and format, then email it to someone using either Outlook or Gmail.
- Email Only : Don’t refresh the file, just pick it up and email it using Outlook or Gmail.
5. New Filename
This is the new filename you want to use excluding the extension. We are leaving out the extension in case you want to dynamically add todays date to the newly saved file (see the next field). The extension is handled in a different field too.
6. Add current date to Filename
If you select YES, it will add todays date to the filename in the format _YYYY_MM_DD.
i.e. If ‘New Filename’ is set to myBEXfile and todays date is Jan 12th 2017, it will make it myBEXfile_2017_01_12
7. File Type
This not only controls the extension at the end of your filename, but also the format itself. The VBA code will save as the correct format depending on what you choose.
- xlsm : Excel 2007 and above macro-enabled file.
- xlsx : Excel 2007 and above with no macros.
- xls : Excel 97-2003 file with no macros.
- pdf : PDF document. Excel basically ‘prints’ to the PDF format, so ideally you have the print layout of your file set to your liking first. This option will make use of that print layout behind the scenes.
8. Email Type
There are two options in this initial version. Outlook and Gmail. More can easily be added.
- Outlook : Makes use of the Outlook software already installed on the machine running the VBA code
- Gmail : Makes use of Gmail via the free SwithMail command line mailer. SwithMail handles Yahoo and other providers too. We will cover SwithMail in more detail later on.
9. Email Display/Send
This is an option for Outlook only. Gmail will ignore this and send directly.
- Display : Your email will be generated and then displayed in screen in front of you. At this stage you can edit the message like a normal email or just click on the normal send button. A good option for previewing what you are about to send, particularly during testing.
- Send : This will send the email through outlook without displaying it to you first.
10. Email Address
The email address of who you want to send the file to.
11. Email Subject
What you want in the subject line of the email.
12. Email Message Body
This is text you can include in the body of your email message. Its just plain text and won’t do any fancy formatting.
The table itself is specifically formatted as a table, using the ‘Format as Table’ option on the Home tab.
The table is given a name via the option in the top left of the ‘Table Tools Design’ tab to name it. It is called MASTERDATA.
We give the table a name as its cleaner to refer to it that way in the VBA code, and it also means we don’t have to care about what sheet it’s on (if you have multiple sheets). Excel happily goes to the right sheet automatically if you refer to the table by name.
We also use a table instead of a plain old list, as when we add rows to it, it cleanly inherits the data validations for formats from the previous rows.
At this stage we can now move on to the VBA code.
Distribution Master File VBA Code.
In your DMF file go to the VBA section (Developer > Visual Basic).
If you don’t have the Developer tab available, enable it through the File > Options > Customize Ribbon section. Tick the normally unticked ‘Developer’ section on the right hand side.
Once in the VBA window, expand out your file in the left hand window and add the following section to one of the modules.
Ideally add your own module so you can export and reuse the code in other files.
We are calling the module BWRefresh, though you don’t really need to name it anything. Its more for your own reference.
The template at the end of this blog will have the full code, but the basics of it will also be explained below.
At the top of our module we define our global constants. These are useful at the top as there is one place to change things if your setup changes in the future. It also helps with the readability of your code.
There are no hard and fast formatting rules for your code, use whatever suits you.
'********************************************************* ' Lets define some constants that we can refer to later on '********************************************************* ' What table do we want to run through when processing the files Public Const mastertablename = "MASTERDATA" ' Set up our BW server details so we can use it with BEX and AO later on Public Const bw_client = "your bw client number" Public Const bw_user = "your bw username" Public Const bw_password = "your bw password" Public Const bw_language = "EN" Public Const bw_system = "your bw system number" Public Const bw_server = "your bw server address" ' Define the path to the BEXAnalyzer Addin for Excel Public Const BEXAnalyzer_Path = "C:\Program Files (x86)\Common Files\SAP Shared\BW\BExAnalyzer.xla" ' If you are wanting to email the files via Gmail (or Yahoo or pretty much any server), ' we make use of the free piece of software SwithMail. It is a command line mail sender. ' https://www.tbare.com/software/swithmail/ ' For outlook we assume the user already has Outlook installed on their machine Public Const swithmail_path = "C:\temp\SwithMail.exe" ' To use Gmail (with SwithMail) we need to supply the relevant credentials Public Const gmail_user = "firstname.lastname@example.org" Public Const gmail_pass = "yourgmailpassword"
So that’s the global settings sorted out. Next is the main routine that loops through our MASTERTABLE.
The Subroutine is called Process_the_Mastertable. This is what we are trying to achieve with the code :
It may look slightly complex but it’s reasonably straight forward.
Process_the_Mastertable makes calls to other subroutines, with the primary one being Refresh_Workbook. Refresh workbook does a few things, and its logic flow we will cover a bit later.
Lets take a look at the Process_the_Mastertable code to see how its doing it. We will do this section by section.
The comments in the code are self-explanatory in most areas, so we will highlight just some of the sections.
Sub Process_the_Mastertable() ' This will loop through the mastertable and perform the relevant actions on each line. ' If the line says YES in the first column it will action it, otherwise it will ignore it. ' Lets set up a bunch of variables ahead of time so Excel knows how to best handle them Dim mastertable As Range, rng As Range Dim currentWorkbook As String, wb1 As Workbook, wb2 As Workbook Dim tActive As String, tFilename As String, tBexAo As String Dim tAction As String, tNewFilename As String, tAddDate As String Dim tExtension As String, tEmailType As String, tEmailDisplaySend As String Dim tEmailAddress As String, tEmailSubject As String, tEmailBody As String Dim finalFilename As String ' Lets speed things up and make the screen not flicker turn_off_screen_updating ' Loop through the first column of our named table and check ' if its marked with a YES saying it is an active row Set mastertable = Range(mastertablename) For Each rng In mastertable.Columns(1).Cells tActive = rng.Value If tActive = "YES" Then ' We have a line marked as active, so make a note of everything else ' on the line using simple offsets from the first column. ' Then assign it to a more recognisable variable name to make things ' easier to understand later on in our code tFilename = rng.Offset(0, 1).Value tBexAo = rng.Offset(0, 2).Value tAction = rng.Offset(0, 3).Value tNewFilename = rng.Offset(0, 4).Value tAddDate = rng.Offset(0, 5).Value tExtension = rng.Offset(0, 6).Value tEmailType = rng.Offset(0, 7).Value tEmailDisplaySend = rng.Offset(0, 8).Value tEmailAddress = rng.Offset(0, 9).Value tEmailSubject = rng.Offset(0, 10).Value tEmailBody = rng.Offset(0, 11).Value
You can see in the code above we are referring to our named table as a range. We had previously defined mastertablename in our global constants at the top of our module.
Set mastertable = Range(mastertablename)
We then loop through it by referring to the first column in that range, the columns(1) in the code below.
For Each rng In mastertable.Columns(1).Cells
The For loop finds the cells in that first column (the ‘Active’ column) and puts it in the rng variable. rng now holds all of the details of that cell, including its value, its location, its formatting etc.
We make use of that, and assign the value from that cell to tActive with this command.
tActive = rng.Value
Why assign it to the tActive variable instead of just using it directly? Just for readability. Instead of having to remember what rng is pointing to, we can look at the variable name and know at a glance.
After checking if tActive = YES, we make further use of the rng data to get all the rest of our fields from that same row of the Mastertable, this time by using .offset
The filename is the next column over from the Active one, so we get that using :
tFilename = rng.Offset(0, 1).Value
This means we are looking 0 rows and 1 cell to the right, and grabbing the value in that cell.
Increasing the offset means we can look at any cell we want in reference to the rng cell found at the start of our loop. We do that right through to offset 11, which holds the text for the email message body.
At this point we have all the details we want from the table, we can now action them.
Lets look at the next lot of code in the Process_the_Mastertable routine.
' Before we do anything else make sure they have given a filename for us to load If tFilename <> "" Then ' Now see if that file actually exists. If it doesn't, there isn't anything further we can do ' The next bit looks for the filename using the standard Dir command, and then checks the length of it. ' If the length is longer than 0, then it found the file. If Len(Dir(tFilename)) <> 0 Then ' So we have a file to use that actually exists. Lets work out what they want to do with it based on the tAction field Select Case tAction ' ************ ' REFRESH ONLY ' ************ Case "Refresh Only" ' They want to just refresh the file in-place, without changing its filename. ' First we need to make sure we can write to it when saving after the update, so check if its already opened by someone else. ' The IsFileOpen function later on in this module does exactly that. If IsFileOpen(tFilename) = True Then MsgBox "The file : " & tFilename & " is already in use by someone else, so we can't update and save it." Else ' Make a note of the workbook we are currently in so we can come back to it later currentWorkbook = ActiveWorkbook.Name ' Go ahead and refresh the file via the Refresh_Workbook subroutine Refresh_Workbook tFilename, currentWorkbook, tBexAo End If
We do a quick check to see if a filename was supplied at all (tFilename <> “”), and if so, then check that it exists with the Dir command. No point doing anything if a valid file wasn’t supplied.
If that’s all good, we pay attention to what they want to do with the file in the tAction field via the case statement.
For the first section, if they have chosen ‘Refresh Only’, we check the file to see if someone else is already using it. The IsFileOpen function at the end of the module takes care of that. If someone is in the file, we won’t be able to save it after refreshing, so we shouldn’t waste time refreshing it in the first place.
Assuming all of those checks are passed, we call the Refresh_Workbook routine and give it the filename to refresh along with the name of our current workbook (so we can come back to it later if things go wrong) and whether the file is BEX or AO.
The next section is the ‘Refresh & SaveAs’ option. As you will see below it is very similar to the ‘Refresh’ option, but this time we are seeing if they want a new filename and if they want to add todays date to it. If they do, we prepare the new filename in advance, as we need to also check whether we can write to that file.
When we then call the Refresh_Workbook routine and supply extra fields containing the new filename and the new filetype.
' ***************** ' REFRESH & SAVE AS ' ***************** Case "Refresh & SaveAs" ' They want to refresh the file but save it as a new name/format ' Build our new filename first so we can check to see if it can be saved to ' If they want to add the current date to the new filename we handle that here too If tAddDate = "YES" Then finalFilename = tNewFilename & Format(Date, "_yyyy_mm_dd") & "." & tExtension Else finalFilename = tNewFilename & "." & tExtension End If ' First we need to make sure we can write to the NEW location, so check if its already opened by someone else. ' The IsFileOpen function later on in this module does exactly that. If IsFileOpen(finalFilename) = True Then MsgBox "The file : " & finalFilename & " is already in use by someone else, so we can't update and save it." Else ' Make a note of the workbook we are currently in so we can come back to it later currentWorkbook = ActiveWorkbook.Name ' Go ahead and refresh it via the Refresh_Workbook subroutine Refresh_Workbook tFilename, currentWorkbook, tBexAo, finalFilename, tExtension End If
As you can see we are trying to keep the code simple and pass off any more detailed work to the Refresh_Workbook subroutine. This allows you to see the logic flow a bit easier compared with having everything lumped into one big blob of code.
Next action option is ‘Refresh & Email’. We won’t show the full code below as its almost identical to the ‘Refresh & SaveAs’ option, aside from the email section at the end of it.
' The updating is now done, lets see if we can email it. If tEmailType = "Outlook" Then ' send it via Outlook with the Send_Email_Outlook subroutine Send_Email_Outlook tEmailAddress, tEmailSubject, tEmailBody, finalFilename, tEmailDisplaySend ElseIf tEmailType = "Gmail" Then ' send it via Gmail with the Send_Email_Gmail subroutine Send_Email_Gmail tEmailAddress, tEmailSubject, tEmailBody, finalFilename, tEmailDisplaySend End If
This calls the relevant emailing subroutine passing all of the details they need.
The final action was ‘Email Only’, which is pretty much identical to the email part we just showed, so we won’t repeat it here.
That’s the main routine covered, we will now take a look at the routine that does the BW grunt work.
The Refresh_Workbook routine does the following :
Lets look at the first lot of code from the Refresh_Workbok routine.
Private Sub Refresh_Workbook(theFilename As String, currentWorkbook As String, bex_or_ao As String, _ Optional newFilename As String, Optional newFiletype As String) ' Open a workbook, refresh it, then save it as either the same name or another name ' We will be making use of another instance of Excel rather than the current instance. ' This is to help us get around excel issues when switching between AO and BEX connection methods. ' In particular the way Excel handles the respective plugins when it comes to disconnecting and reconnecting. Dim wb2 As Workbook, wbExternal As Workbook Dim xlApp As Application Dim finalFilename As String ' ******************* ' REFRESHING THE FILE ' ******************* If bex_or_ao = "BEX" Then ' Update the Statusbar to let the user know whats happening Application.StatusBar = "Refreshing the BEX workbook " & theFilename & " in a new instance of Excel" ' Start up a whole new instance of Excel Set xlApp = CreateObject("Excel.Application") ' From this point on when we want to refer to the new instance of Excel we prefix it with xlApp. ' Lets make sure BEX is up and running and connected in the new Excel instance xlApp.Application.StatusBar = "Loading the BEXAnalzyer Addin" xlApp.Workbooks.Open (BEXAnalyzer_Path) ' We need the new instance of Excel to be visible for things to work properly xlApp.Visible = True ' Run the SetStart macro that comes with BEX so it pays attention to you xlApp.Application.StatusBar = "Giving BEXAnalzyer a wakeup call" xlApp.Application.Run "BExAnalyzer.xla!SetStart" ' Logon directly to BW using the sapBEXgetConnection macro. This is still all in the other instance of Excel xlApp.Application.StatusBar = "Logging into the BW system" Set myConnection = xlApp.Application.Run("BExAnalyzer.xla!sapBEXgetConnection") With myConnection .client = bw_client .User = bw_user .Password = bw_password .Language = bw_language .systemnumber = bw_system .ApplicationServer = bw_server .SAProuter = "" .Logon 0, True End With ' Now initialize the connection to make it actually usable xlApp.Application.StatusBar = "Connecting BEXAnalzyer to BW" xlApp.Application.Run "BExAnalyzer.xla!sapBEXinitConnection" ' Lets then open our BEX file and refresh it via the Refresh_the_data macro ' Open the file in the new instance of Excel, don't update the links (the 1st false) and don't open it read-only (the 2nd false) xlApp.Application.StatusBar = "Opening " & theFilename Set wb2 = xlApp.Workbooks.Open(theFilename, False, False) xlApp.Application.Run "'" & wb2.Name & "'!Refresh_the_data"
The comments in the code explain whats going on and its very close to what we did in the previous blog, where the VBscript would be performing those steps.
The main difference now is us making a new instance of Excel. Basically the VBA code running in Excel is creating another instance of Excel to handle the BEX file.
Its assigning that new instance of Excel to the xlApp variable. If we then prefix the rest of our existing commands with xlApp, it will perform those commands in that new instance of Excel.
You can use this same method to create even more instances of Excel if you wish (xlApp2, xlApp3 etc).
Why are we using a second instance of Excel?
Since we are already in excel processing the Master table, why are we launching another instance to do the BEX/AO refreshing?
When it comes to switching between BEX and AO Add-ins and their respective connections to BW, chopping and changing between the two while in the same instance introduced instabilities when I tested it.
To solve the instability issue and eliminate the excel crashes, triggering everything in a second excel instance and then closing that instance in between each file has worked really well. There is minimal additional overhead in terms of time, only a few extra seconds.
Coming back to our Refresh_Workbook code, the AO section is again similar to what we did in the previous blog, but against the second instance of Excel.
Code below update Jan 19th 2017 to no longer need a macro within the AO file.
ElseIf bex_or_ao = "AO" Then ' We are updating an Analyis for Office file, which is much simpler than BEX ' Lets then open our AO file and refresh it ' Update the Statusbar to let the user know whats happening Application.StatusBar = "Refreshing the AO workbook " & theFilename & " in a new instance of Excel" ' Start up a whole new instance of Excel Set xlApp = CreateObject("Excel.Application") ' From this point on when we want to refer to the new instance of Excel we prefix it with xlApp. ' We need the new instance of Excel to be visible for things to work properly xlApp.Visible = True ' Open the file in the new Excel instance, don't update the links (the 1st false) and don't open it read-only (the 2nd false) xlApp.Application.StatusBar = "Opening " & theFilename Set wb2 = xlApp.Workbooks.Open(theFilename, False, False) ' Instead of relying on macros in the destination file, lets run them from here ' Force the plugin 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 lResult = xlApp.Application.Run("SAPExecuteCommand", "RefreshData") End If
At this stage we have refreshed our file, next we need to work out how it should be saved.
' *************** ' SAVING THE FILE ' *************** ' Now see how we should be handling the saving of the file. ' If newFilename is blank, we just save it as-is If newFilename = "" Then ' Update the Statusbar to let the user know whats happening Application.StatusBar = "Saving and closing the workbook " & theFilename ' Close the workbook we refreshed and save any changes without prompting ' To skip the prompts we turn off the DisplayAlerts option by setting it to false ' After that, close the second instance of Excel xlApp.Application.DisplayAlerts = False ' Save the workbook wb2.Save ' Close the workbook wb2.Close xlApp.Application.DisplayAlerts = True ' Now close the second instance of excel xlApp.Quit Else ' Save the workbook as a new name/location then close it. ' Make sure we aren't trying to save to a file that is already open If IsFileOpen(newFilename) = True Then MsgBox "The file : " & newFilename & " is already in use by someone else, so we can't save over top of it." wb2.Close ' close the second instance of excel xlApp.Quit Else ' Update the Statusbar to let the user know whats happening xlApp.Application.StatusBar = "Saving the workbook " & theFilename & " under its new name " & newFilename ' Depending on the extension the user has supplied in the table, save as a different filetype using FileFormat xlApp.Application.DisplayAlerts = False Select Case newFiletype Case "xls" wb2.SaveAs newFilename, FileFormat:=56 Case "xlsx" wb2.SaveAs newFilename, FileFormat:=51 Case "xlsm" wb2.SaveAs newFilename, FileFormat:=52 Case "pdf" wb2.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=newFilename, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False Case others MsgBox "No filetype was supplied for : " & newFilename & " so it will be skipped." End Select wb2.Close xlApp.Application.DisplayAlerts = True ' close the second instance of excel xlApp.Quit End If End If
The embedded comments cover most of whats going on in the code above.
Note that we are still referring to our second instance of Excel via the xlApp variable. We do the saving in that instance and then close that instance when we are done.
In that second section of the routine you can see where we are saving under the different formats. This is achieved through the FileFormat:= parameter. There are lots of different ones you can make use of, and they are all listed at this official Microsoft URL : https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
The PDF format isn’t straight forward. It makes use of the predefined print area in the document. Basically ensure you are happy with how it will look if printed (use the print-preview option), and the PDF will format the same way.
Emailing our files
We have two routines to send our files via email.
The first sends via the Outlook. It relies on outlook being on the machine doing the refreshing, and also being open at the time. That routine is Send_Email_Outlook, and the comments in that subroutine explain everything its trying to do.
The second uses Gmail. Given any business with SAP will likely have a corporate email system instead of Gmail, why have I included Gmail? Just to see if its possible. This is the option I’ll go in to more detail on.
The Gmail option makes use of a free command line mail sending program called SwithMail (http://www.tbare.com/software/swithmail/). It is free use for commercial and non-commercial purposes.
It’s a single, standalone windows executable file. It doesn’t need to be installed anywhere, and can sit in the same location you are running your scripts or the DMF file from. It can send to Gmail, Yahoo, Hotmail, Exchange and others, and importantly it doesn’t need you to have any email client already running on your machine.
It also has a nice GUI option to help you craft the syntax of the parameters you will eventually use on the commandline.
Our Send_Email_Gmail codes looks like this :
Private Sub Send_Email_Gmail(therecipient As String, thesubject As String, thebody As String, _ theattachment As String, senddisplay As String) ' This will send an email through Gmail with the Swithmail command line tool to "therecipient" with "theattachment" ' It relies on SwithMail being somewhere accessible to the machine running this macro Dim wsh As Object Dim waitOnReturn As Boolean: waitOnReturn = True Dim windowStyle As Integer: windowStyle = 1 Dim swithparameters As String ' Update the status bar to let the user know whats going on Application.StatusBar = "Sending a Gmail email to " & therecipient & " via SwithMail" ' Lets set up the commandline parameters for Swithmail and gmail ' All of the extra quotes look hideous, but ultimately create the correct string that Swithmail needs swithparameters = " /s /from """ & gmail_user & """ /server ""smtp.gmail.com"" /p 587 /SSL /u """ & gmail_user & """ /pass """ & gmail_pass & """ /to """ & therecipient & """ /sub """ & thesubject & """ /body """ & thebody & """ /a """ & theattachment & """" ' Now use the parameters along with the swithmail global path. ' Create a shell (command line window essentially) Set wsh = VBA.CreateObject("WScript.Shell") ' Now within the shell, run Swithmail and give it the relevant parameters wsh.Run swithmail_path & swithparameters, windowStyle, waitOnReturn ' Free up memory now that we are done Set wsh = Nothing End Sub
The routine sets up the parameters we want to give to SwithMail, then creates a shell window (a command line window) and calls SwithMail with the parameters.
It then waits until SwithMail is done with its send through Gmail.
That’s the key parts of DMF file covered.
There are three other subroutines/functions in the code and they are as follows. We won’t go into them in this blog as they are quite simple and have comments within the code :
- IsFileOpen : Checks to see if the filename you have given is already open by someone else. It tries to open a write lock on it, and if its not successful, it assumes the file is already locked.
- Turn_off_screen_updating : Turns off the Excel screen updating. This speeds things up a lot.
- Turn_on_screen_updating : Turns back on the screen updating to ensure everything is updated on-screen.
As mentioned in the previous blog, we need a macro in there that will force the refresh.
Put this VBA code in each of the BEX files that you are wanting to auto-refresh.
Sub Refresh_the_Data() Run "BExAnalyzer.xla!SAPBEXrefresh", True End Sub
Jan 19th 2017 update : The instructions below are no longer required as we do not need the AO file to have any VBA code in it. Leaving them in here in case you want to use the old method.
Again, following the instructions from the previous blog, put the following in each of the AO files you are wanting to auto-refresh, and ensure the AO Workbook is saved as a .xlsm
In the main module :
Sub Refresh_the_Data(bwclient As String, bwuser As String, bwpassword As String) Dim lResult As Long lResult = Application.Run("SAPLogon", "DS_1", bwclient, bwuser, bwpassword) lResult = Application.Run("SAPExecuteCommand", "RefreshData", "DS_1") End Sub
In the ThisWorkbook sheet in the VBA area put this bunch of code to ensure the AO addin is working correctly.
Private Sub Workbook_Open() Dim lResult As Long Dim addin As COMAddIn For Each addin In 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 End Sub
Triggering the DMF from a VBscript
We can run Process_the_Mastertable from a VBscript, which is a key part in enabling full hands-off scheduled automation.
Once the VBscript can trigger the VBA code in the DMF file, we can in turn trigger the VBscript via Windows Task Scheduler (shown in the next section).
Create a text file with a .vbs extension and put the following into it.
' Setup the global variables Dim xl Dim myConnection Dim xlBook ' Launch Excel set xl = createobject("Excel.Application") ' Make it visible otherwise things just don't work well xl.Visible = true ' now open the DMF file Set xlBook = xl.Workbooks.Open("PUT YOUR FULL DMF FILEPATH & DMF FILENAME HERE", 0, False) ' Run the custom Process_the_Mastertable macro contained within the file. xl.Application.run "Process_the_Mastertable" ' We don’t need to save the DMF file, so just close it without any prompts to save xl.DisplayAlerts = False xlBook.close False xl.ActiveWindow.close True ' Close Excel xl.Quit 'Clear out the memory Set xlBook = Nothing Set xl = Nothing
Triggering the VBscript from Windows Task Scheduler
With Windows Task scheduler, you can set up a task that then runs your new VBscript file at a time and frequency of your choosing.
Search Google for plenty of examples on scheduling a VBscript from Task Scheduler.
Securing your VBA code from prying eyes
As you saw at the top of the module, we have some sensitive information (usernames, passwords etc).
If there is a chance this template will be run by someone other than yourself, you may want to do some basic security and hide your sensitive information from prying eyes.
Go to Tools > VBAProject Properties > Protection and put a password in the ‘Password to view project properties’ area and tick the ‘Lock project for viewing’ option.
When someone opens your DMF file, goes to the Developer > Visual Basic area all they will see is the following :
If they try to expand on your VBAProject section they will be prompted for the password, so those sensitive items in your code will be reasonably safe (though never completely safe if someone is determined enough).
There are a few options to expand on the scheduling, particularly if you are wanting to refresh and distribute different files at different times throughout the day, or on different days of the week, or once a month etc.
1. Multiple DMF files each being triggered at a different time by different scheduled tasks.
You could have a DMF file for 5am, a different DMF file for 7am etc and have a separate scheduled task triggering each one at the right time.
2. Single DMF file with multiple ‘Master’ tables, each called from a different scheduled task.
Your DMF file would have multiple sheets, each with a different Mastertable (one for Finance, one for Logistics etc). Each of those tables would be given a different name.
You could then modify your Process_the_Mastertable routine to accept the table name as a parameter, and that parameter would be the name of the Mastertable you want to process.
Your scheduled VBscripts would then simply refer to the same file and macro, but a different Mastertable each time via the parameter option.
DMF File :
Sub Process_the_Mastertable(yourtable as string)
And later on in the code you would then refer to yourtable instead of mastertable.
xl.Application.run "Process_the_Mastertable", “YOURPARAMETER”
3. Add a time column into the one DMF file, and schedule the same file to be run often (say hourly).
You can have a single large master DMF file with everything you want in it. One of the columns may have a rough time to action that particular row (say an hour of the day).
The Process_the_Mastertable loop can check that column and if the hour in the column matches the current hour in which the VBA code is running, it knows to action it.
If tScheduledHour = Hour(Now) then Action the row Endif
Using similar code to the emailing section, you can send yourself emailed alerts after each run of the DMF file.
You could accumulate any errors that have come up during the processing of the file, and then email that error log to yourself so you are only having to deal with exceptions.
The Example DMF File
Here is the DMF file we have been talking about in the blog. It is free to use/change/modify/break as much as you want.
Please note the following :
- All filenames are dummy/example filenames
- All BW server info and login credentials will need to be replaced with your own.
- If you are using Gmail you will need to have the free utility SwithMail available also. Get SwithMail from here : http://www.tbare.com/software/swithmail/
- There is no VBAProject password set.
- Created in Excel 2010, but should work fine in higher versions (though untested).
Jan 19th 2017 update : The link above has the latest version of the file with the different AO handling routine. Its version 0.5.
Hopefully you have found this guide useful.
If you have ways to improve on it please let everyone know in the comments section below.
Part 3 of this topic is available here : Analysis for Office Variables and Filters via VBA
very nice post. I have to check your file later, because I can't open Dropbox here. Hope to hear more about this topic. You can only use local Excel workbooks and not some which are stored in a BIP or BW?
No I haven't tried it on workbooks stored on the server yet. I'll give that a go after I get dynamic variables working reliably. If I'm successful I'll blog on that next.
I'm happy to email you the template if you like (its only 47KB). Drop me a line at email@example.com or let me know your email address and I'll send it through.
Regan, great write up again on how to make managing these updates easy.
Can you please forward workbooks to the email id firstname.lastname@example.org
Really awesome article
I looking for some help on Bex Analyzer VBA . We know that we can insert a query in a excel work book, however , i have different need. My requirement is to copy the query from existing excel workbook to a new excel work book and able to refresh from the new book.
Currently i am doing this by manually accessing through bex tool bar and under that by selected copy query button.
Any hep with regard to same is highly appreciated.
I am trying to automate AO. The code you provided is not working for me.
I am trying open the "Select Data Source" from Analysis menu, open the data source by passing the credentials, and then I need to fetch a report by passing the parameters (see below). Could you let me know what is it I am not doing right?
Please let me know the syntax for running a report by passing the parameters.
iAppCall = Application.Run("SAPLogon", "BRP", "300",<Username>, <Password>, "EN")
iAppCall = Application.Run("SAPExecuteCommand", "RefreshData", "BRP")
iAppCall = Application.Run("SAPOpenWorkbook", "ZQC_FI1124_SALES_TAX_RECON", "BRP", "ZVMGEOHY", "NA")
Thanks in advance.
Tank's for your post, Can you please forward workbooks to the email id email@example.com
Have you tried the download from Part 4? Its available here : https://blogs.sap.com/2017/02/20/analysis-for-office-and-excel-vba-lessons-learnt/
Thanks for the post. I tried downloading the file but got a message that it wasn't there. Could you please forward the template to firstname.lastname@example.org
Sending now. New link is : https://www.dropbox.com/s/fxtkdgv2qfhk081/BW-Report-Refresh-and-Distribute-0.7.zip?dl=0
One question: whether we plan a schedule with these settings (see picture), the VB-Skript runs without starting the DMF. By starting the skript with the settings "Run only when user is logged on" it runs...
Do you have any idea?
Many thanks and best regards,
I couldn't get that setting to work reliably for me. I think it had something to do with the AO addon needing Excel to be visible for it to function fully, and running without a user being logged in just didn't seem to do the right thing.
If it works for you please let me know.
I am unable to connect Analysis with the 7.0 process_the_mastertable macro.
When I click on the button, it correctly opens the file I linked to, but then it hangs for a few seconds before I get the error message "the add-in could not be installed". I have different files, but it's the same problem. I have insert the VBA code into the file I am trying to update, and it's then able to successfully connect Analysis and update the file. So I suspect it's related to opening a seperate instance of excel. When I click on debug, the line with "AddIn.Connect = True" is highlighted.
I am on Office 2013, SAP 740, and using the 7.0 Refresh and Distribute report. In the trust center I have enabled all macros.
Do you have any idea what the problem could be? Perhaps some setting that needs to be enabled in order for the files to work correctly?
Whats its supposed to be doing is opening the new instance of Excel and initiating a fresh connection to your BW server.
What happens if you use that code directly within the Excel window the file is already in (instead of the 0.7 template loading it). Does it still fail?
I was able to copy the part of the code that connects Analysis and updates the query into the other file, and it ran without any problems. Could the issue I am having be related to me using single sign-on to connect to SAP?
Yes possibly. I haven't tried any of the code with single sign-on.
After some more testing, I have come to the conclusion that it's not related to single sign-on, but instead running multiple Excel instances.
The macro fails already when trying to enable Analysis in the target file. I am getting the following error message:
"Run-time error '-2146233088 (80131500)':
The add-in could not be installed."
As mentioned previously, if I copy the part of the macro that enables Analysis into an Excel instance that has been opened normally, it works fine. I suspected the error was related to somehow not being able to handle a new instance. I tested this by using a macro that only opens a new excel instance, and in that document I tried to use the basic macro that only enables Analysis - same error as above.
Do you know what could cause my Excel to have problems with multiple instances? I have looked through your guide several times to see if there were mentions of anything, but haven't found anything.
In the Trust Center I have tried to allow for anything and remove all restrictions, i.e. all macros are enabled and has access to the VBA project object model, ActiveX has no restrictions, etc.
You could try not enabling Analysis in any subsequent instance of Excel and see if that helps.
Alternatively, only have one additional instance of excel running at a time. i.e. Open new instance of Excel, Open up the file, activate the plugin, update/adjust the data, close file, close instance of excel, then repeat cleanly for the next file.
March 2018 : Note any download link in the blog post points to Dropbox which unfortunately keeps changing the link URL.
The final version of this blog series is 0.7 and is also available at the more permanent link on gt.tt : http://ge.tt/92Kodvo2
Could you help me how to control this filter? Thanks for your help!
Yes this is covered in Parts 3 & 4 of these posts.
The link to part 3 it is at the end of this original post.
Hello Regan, this macro helps me a great deal. Thank you!
However, as of late the macro doesn't cycle through the Excel files in the rows automatically anymore.
When refreshing a file the popup message 'Microsoft Excel is waiting for another application to complete an OLE action' will appear. I have to click OK (a few times) before it will cycle to the next file.
Enabling 'Ignore other application that use DDE' in the Excel configuration didn't help.
First, thanks for your great sharing !!!
Within the post subroutine “Send_Email_Outlook” seem missed. Could share again in the post?
Its all in the example file at this location : http://ge.tt/92Kodvo2
Thanks again and great help from your post~~~
Thank you for your sharing!
While trying out this solution, I've successfully opened the file, but I'm not able to log in thus not able to refresh, I'm wondering if it's because my SAP is ver. 7.5?
Do you have any idea what the problem could be? I could provide more information if it's needed, thank you!
7.5 works ok for me with the existing logon code in the examples above.
What error are you getting?
This is great but I have a issue:
When refreshing a file the popup message ‘Microsoft Excel is waiting for another application to complete an OLE action’ will appear. I have to click OK before it will cycle to the next file. This happens if a file takes a bit longer to update.
Enabling ‘Ignore other application that use DDE’ in the Excel configuration didn’t help.
Thanks a lot,
This is a great information and was looking out for exact solutions.
Can you share the DMF file to refresh Bex and send the copy via outlook to my email id email@example.com
I am unable to download from given link
Hi Regan ,
I was going thru the blog it was very clear and appreciate the way you put together to work as a process. I am not able to access the file at location http://ge.tt/92Kodvo2 . Is there a way to get access?
Hello Regan, Thank You so much for all the efforts you had put to share your knowledge with us. The code worked like a charm for me.
Quick Question: I have 3 loops of a data source and currently saving them into a PDF with ACTION syntax after every loop. The requirement is to save all the results of all the 3 loops into One PDF file. How do I achieve this?