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) :
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 :
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
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.
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.
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 :
1. Active
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.
3. BEX/AO
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.
4. Action
This is what you want to do with the BW file.
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.
8. Email Type
There are two options in this initial version. Outlook and Gmail. More can easily be added.
9. Email Display/Send
This is an option for Outlook only. Gmail will ignore this and send directly.
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.
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.
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.
We then loop through it by referring to the first column in that range, the columns(1) in the code below.
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.
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 :
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.
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.
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.
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.
Refresh_Workbook
The Refresh_Workbook routine does the following :
Lets look at the first lot of code from the Refresh_Workbok routine.
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.
At this stage we have refreshed our file, next we need to work out how it should be saved.
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 :
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 :
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.
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 :
In the ThisWorkbook sheet in the VBA area put this bunch of code to ensure the AO addin is working correctly.
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.
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.
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 :
becomes
And later on in the code you would then refer to yourtable instead of mastertable.
VBscript :
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.
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.
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 :
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
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)
- Process_the_Mastertable
- Refresh_Workook
- 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)
- Extras
- 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 :
1. Active
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.
3. BEX/AO
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.
4. Action
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 = "yourgmailaddress@gmail.com"
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).CellsThe 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.ValueWhy 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).ValueThis 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 IfAs 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 IfThis 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.
Refresh_Workbook
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 IfAt 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 SubThe 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.
BEX Files
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 SubAO Files
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 SubIn 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 SubTriggering 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 = NothingTriggering 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.
Extras
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).
Advanced Scheduling
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()becomes
Sub Process_the_Mastertable(yourtable as string)And later on in the code you would then refer to yourtable instead of mastertable.
VBscript :
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
EndifStatus Reporting
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
- SAP Managed Tags:
32 Comments
