Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript

For a little while now I’ve been looking for hands-off ways to auto-update Excel files with data from our BW system, without having to use Broadcasting or any other additional servers.

After reading through numerous posts and forums from a lot of different sources, and a bunch of trial and error I have a technique working reliably both both Analysis for Office and BEX.

I thought I’d therefore share the process I use so anyone who has the same struggles as me can get a potential solution from one location.

This works for me, but of course may not work for you depending on the setup you have, so mileage may vary.

Feedback/Fixes/Corrections are welcomed.

Part 2 of this topic is available here : Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript – Part 2

Part 3 of this topic is available here : Analysis for Office Variables and Filters via VBA

Part 4 of this topic is available here : Analysis for Office and Excel VBA – Lessons learnt

This guide covers :

• Why are we doing this?
• The Basic Setup
• Calling and refreshing the data in a BEX file using VBscript and VBA
• Calling and refreshing the data in an Analysis for Office file using VBscript and VBA
• Advanced Options
• 9th Jan 2017 : Update with improved AO refresh option

Why are we doing this?

We have a number of excel files that we wanted to have updated on a regular basis without users having to load the files, log in/connect to BW and then refresh.

Some are accessed via BEX and others via Analysis for Office 2.3.

A lot of forum posts and blogs may say use Broadcasting or Web reporting, but thats not something we are looking at investing time in at this stage.

Instead we can do it using simple, reliable and efficient scripting from within Windows (and Excel) itself. The added bonus is this method doesn’t require additional systems or servers.

The basic setup

We are running the following :

• BW 7.4 on HANA
• Excel 2010 (this should work fine on 2013 also)
• Analysis for Office 2.3
• SAP GUI 7.40 Patch level 10 incl BEX

Calling and refreshing the data in a BEX file using VBscript and VBA

While the title sounds intimidating, once you have the basics working its quite straight forward.

We will be using a VBscript to run and control Excel, and then make a call to a VBA Macro within the Excel file to refresh the data.

Set up the VBscript

For anyone not familiar with VBscript, its basically just a text file with a .vbs extension.

So, what do the contents of the BEX VBscript look like? It looks like this :

' 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 that Excel is open, open the BEX Analyzer Addin xla file
xl.Workbooks.Open("C:\Program Files (x86)\Common Files\SAP Shared\BW\BExAnalyzer.xla")

' Run the SetStart macro that comes with BEX so it pays attention to you
xl.Run ("BExAnalyzer.xla!SetStart")
 
' Logon directly to BW using the sapBEXgetConnection macro
Set myConnection = xl.Run("BExAnalyzer.xla!sapBEXgetConnection")
With myConnection
	.client = "YOUR CLIENT NUMBER"
	.User = "YOUR USERNAME"
	.Password = "YOUR PASSWORD"
	.Language = "YOUR LANGUAGE"
	.systemnumber = "YOUR SYSTEM NUMBER"
	.ApplicationServer = "YOUR BW SYSTEM"
	.SAProuter = ""
	.Logon 0, True
end with

' Now initialize the connection to make it actually usable
xl.Run("BExAnalyzer.xla!sapBEXinitConnection")

' now open the file you want to refresh
Set xlBook = xl.Workbooks.Open("PUT YOUR FULL FILEPATH & FILENAME HERE", 0, False)

' Run the custom Refresh macro contained within the file.
xl.Application.run "Refresh_the_Data"

' Save the file and close it
xlBook.save
xl.ActiveWindow.close True
  
' Close Excel  
xl.Quit
  
'Clear out the memory  
Set xlBook = Nothing
Set xl = Nothing

The comments before each section (the lines starting with an apostrophe) explains what its about to do.

Basically replace the sections in CAPITALS with your own details.

The VBscript does the following :

1. Start Excel
2. Load and initialise the BEX Analyzer addin
3. Make a connection to the BW system
4. Load the Excel file that has the existing BEX query in it
5. Run the macro ‘Refresh_the_Data‘ which actually refreshes the data
6. Save the Excel file (same name and location)
7. Close Excel and clean up after itself

To complete the work, we need to add in a very small macro to the file that we are wanting to regularly refresh.

Add a Macro to your Excel file

Open your macro enabled .xlsm Excel file and 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.

Sub Refresh_the_Data()

Run "BExAnalyzer.xla!SAPBEXrefresh", True

End Sub

Its a very simple piece of code. All it does is call the BEX Analyser function SAPBEXrefresh, which will refresh any data sources within that file.

Remember you have already established the connection to the BW server within the VBscript, so all this macro needs to do it refresh the data.

Some may ask why isn’t this simple command also within the VBscript, after all isn’t it just calling another routine?

The answer is I couldn’t get it working reliably within the VBscript, and rather than burn more time on it, adding a small extra macro to an already macro-laden file made the most sense.

Test it

Now that you have the VBscript ready, and the Excel file macro set up, now all you need to do it test it.

Open a command prompt on your machine, and run the .vbs file by just typing its name.

i.e. if you have your.vbs file in c:\temp\mytest.vbs, find your way to c:\temp and type in mytest.

Windows will know what to do with it.

You should see Excel open, the Add-Ins tab appear, your file load, refresh itself and then everything will close again.

Calling and refreshing the data in an Analysis for Office (AO) file using VBscript and VBA

The basics are the same as the BEX instructions above, but with a difference when it comes to connecting to BW.

We will be using a VBscript to run and control Excel, and then make a call to a VBA Macro within the Excel file to refresh the data.

So, what do the contents of the AO VBscript look like? It looks like this :

' Setup the global variables
Dim xl
Dim xlBook
  
' Launch Excel
set xl = createobject("Excel.Application")

' Make it visible otherwise it doesn’t work
xl.Application.Visible = True
xl.DisplayAlerts = False
  
' now open the file you want to refresh
Set xlBook = xl.Workbooks.Open("PUT YOUR FULL FILEPATH & FILENAME HERE", 0, False)

' Run the Refresh macro contained within the file.
xl.Application.run "Refresh_the_Data"

' Save the file and close it
xlBook.save
xl.ActiveWindow.close True
  
' Close Excel  
xl.Quit
  
'Clear out the memory  
Set xlBook = Nothing
Set xl = Nothing

The comments before each section (the lines starting with an apostrophe) explains what its about to do.

Basically replace the section in CAPITALS with your own filename.

The VBscript does the following :

1. Start Excel
2. Load the Excel file that has the existing AO query in it
3. Run the macro ‘Refresh_the_Data‘ which actually logs in to BW and refreshes the data
4. Save the Excel file (same name and location)
5. Close Excel and cleaned up after itself

To complete the work, we need to add in two small macros to the file that we are wanting to regularly refresh.

Add a Macro to your Excel file

Open your macro enabled .xlsm Excel file and 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.

Macro 1 :

Sub Refresh_the_Data()

Dim lResult As Long

lResult = Application.Run("SAPLogon", "DS_1", "YOUR CLIENT NUMBER", "YOUR USERNAME", "YOUR PASSWORD")

lResult = Application.Run("SAPExecuteCommand", "RefreshData", "DS_1")

End Sub

Its a very simple piece of code. All it does is call the AO function SAPLogon to connect to BW, and then it calls SAPExecuteCommand with the RefreshData option to refresh DS_1.

Whats DS_1 you ask? Its the first lot of data you have in your AO file.

Each new AO query you have in your file gets the next number like DS_2, DS_3 etc.

To find out what your DS_* number is, go to the Analysis Design Panel and choose the Components tab at the bottom. You will see the DS_* number

Note the use of RefreshData instead of Refresh. RefreshData forces a fresh read from the server, whereas Refresh pays attention to any cached data. We wanted a clean load each time to ensure we had the very latest info.

Macro 2 :

The second macro brings in some reliability when it comes to ensuring the ‘Analysis’ tab is available in the Excel Ribbon. As soon as the workbook is opened, it checks for the Analysis COMAddIn.

If it finds Analysis and thinks it isn’t enabled, it enables it.

If it finds Analysis and thinks its already enabled, it disables it first then re-enables it to make doubly sure its going to function ok.

The second part is very important, and gives excel a kick in the ass to really ensure the addin is active.

Add this macro to the ‘ThisWorkbook’ section and call it Workbook_Open.

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

Thats the macros taken care of.

Test it

Now that you have the VBscript ready, and the Excel file macro set up, now all you need to do it test it.

Open a command prompt on your machine, and run the .vbs file by just typing its name.

i.e. if you have your.vbs file in c:\temp\mytest.vbs, find your way to c:\temp and type in mytest.

Windows will know what to do with it.

You should see Excel open, the Analysis tab appear, your file load, refresh itself and then everything will close again.

Advanced options

Updating/Refreshing more than one file at a time

If you want to refresh multiple files in one session, rather than the overhead of opening and closing excel each time, simply adjust your VBscript as follows.

' now open the file you want to refresh
Set xlBook = xl.Workbooks.Open("PUT YOUR FULL FILEPATH & FILENAME HERE", 0, False)

' Run the custom Refresh macro contained within the file.
xl.Application.run "Refresh_the_Data"

' Save the file and close it
xlBook.save
xl.ActiveWindow.close True

' now open the SECOND file you want to refresh
Set xlBook = xl.Workbooks.Open("SECOND FILENAME HERE", 0, False)

' Run the custom Refresh macro contained within the file.
xl.Application.run "Refresh_the_Data"

' Save the file and close it
xlBook.save
xl.ActiveWindow.close True

' now open the THIRD you want to refresh
Set xlBook = xl.Workbooks.Open("THIRD FILENAME HERE", 0, False)

' Run the custom Refresh macro contained within the file.
xl.Application.run "Refresh_the_Data"

' Save the file and close it
xlBook.save
xl.ActiveWindow.close True

Automating it via Windows Task Scheduler

Once you know your VBscript is working, its very simple to then schedule it to run using Windows Task Scheduler.

Set your date, time and frequency, and then point the action part of the step to your .vbs file. There are plenty of guides to scheduling VBscripts on the internet.

Note if you are running multiple schedules, make sure they don’t overlap. Having multiple instances running at one time isn’t something I have tested so I can’t comment on the reliability of it.

Having conflicting schedules of BEX and AO at the same time may be worse than two of the same type of file.

The basic idea of it looks like this :

Sending status emails after successfully updating, or unsuccessfully updating as the case may be

We have the basics of calling an embedded Macro from a VBscript, so enhancing it to check for errors and then generate alert emails is a relatively straight forward task.

I’ll create another blog on that should there be enough interest. In general though, trigger emails from within VBA is fairly well documented across the internet.

Hopefully this guide is useful for those wanting to automate some of the data refreshing from their systems without having to spend a lot of time or money to do so.

** Update 9th of January 2017 – Improvement the AO update method **

After a bit more messing around, the solution that allows us to not have any logon details in the AO files was embarrassingly obvious.

In the details above, we currently have our Refresh_the_Data macro in the AO file as follows :

Sub Refresh_the_Data()

Dim lResult As Long

lResult = Application.Run("SAPLogon", "DS_1", "YOUR CLIENT NUMBER", "YOUR USERNAME", "YOUR PASSWORD")

lResult = Application.Run("SAPExecuteCommand", "RefreshData", "DS_1")

End Sub

Instead change it to the following :

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

Note we are now letting the macro take parameters, and we will pass them in from the VBscript.

In our VBscript, instead of this line with no parameters :

xl.Application.run "Refresh_the_Data"

Replace it with this :

xl.Application.run "Refresh_the_Data", "YOURCLIENT", "YOURUSERNAME", "YOURPASSWORD"

So essentially you can have all of your logon credentials kept safely in the VBscript rather than embedded in the AO files.