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.
amazing.. excellent information.
Regards,
Vivek
Hi,
it looks nice. Similar to Distribution tools like BION Report Distributor or APOS.
regards,
Tobias
Hi Tobias & Vivek,
Thanks for the feedback. Its certainly not as elegant as the professional solutions you've mention, but its free and relatively easy to start off with for anyone wanting to try it out.
I've only just touched on whats possible in this specific blog. Expanding to auto-emailing, format changing before distribution (PDF etc) is something I'll likely play with in the coming weeks.
With VBA in Excel and the available Functions built in to BEX and AO a lot can be achieved and the only investment required is time.
Cheers,
Regan
Hi Regan,
I don't want to reduce your work. It is really impressive. Hope to read more about this.
Best regards,
Tobias
Very Cool Regan - both the solution and the way you have described it in this detailed blog .
We are using BION currently which is working out well but this opens up avenues for some of the power users to schedule their workbook refreshes using Macros.
It could possibly be taken to the next level too by setting query variables as well before the refresh by reading from an external source that is maintained independently of the workbook being refreshed.
Thanks again for sharing
Shailendra
Thanks Shailendra,
Thats definitely something I’m going to experiment with.
Excel can have its stability issues, particularly if someone has other macro-enabled workbooks open at the time, but overall its a pretty solid tool and most users have a certain level of comfort when using it compared with other tools.
Likely things I’ll try to get working and then post a blog on (in no specific order) :
1) Automated Email distribution after refreshing, complete with varying formats
2) Single large workbook with multiple data sources/queries and distributing the contents to different destinations (i.e. Sheet1 goes to group X, Sheet2 to Group Y etc). Allows for centralised refreshing and controlled distribution.
3) Dashboarding and distribution (refresh data sources, update the dashboard, but distribute just the dashboard results afterwards)
4) Set Query Variables before refreshing.
Ultimately I think I’ll aim for a bunch of seperate blogs which can be used on their own, but which can also be layered together if someone wants to make use of an overall ‘refresh and distribute’ platform build on Excel and Task Scheduler.
i.e. Single Excel Master File that contains a master list of files to refresh, where and how to distribute them, and what variables should be adjusted. That file gets triggered by a single scheduled task.
Basically a cheap reporting/data distribution method. I’ll be able to use it in my role with my company, so it may be useful for others too.
This is a learn as I go thing, so if there are ideas or suggestions to improve things or do them better, I’m happy to take them
I've added a bunch of those improvements now.
Part 2 is here : Automated updating of BW data in Excel files (BEX & AO) via VBA/VBscript – Part 2
Part 3 is here : Analysis for Office Variables and Filters via VBA
Nice overview Regan!!!!
Do you / anybody have experience logging on with SSO using the "SAPLogon" API function?
I read the documentation and searched the web but I could not find a clear answer.
I would have a lot of automated use cases with AO if this would work..
Regards
Sebastian
Hi Sebastian,
Unfortunately I don't have any SSO experience with AO.
My company is looking at implementing SSO in the coming year, so no doubt I will be encountering it at some point.
In your searching, did this link help?
https://wiki.scn.sap.com/wiki/display/BI/BI-RA-AO-CON+Connection+and+Authentication
Regards,
Regan
Regan,
Very helpful article! I’ve been trying to automate BExAnalyzer refreshes for several weeks now.
I am a long time VBA and Microsoft guy but new to SAP. So I'm sketchy on SAP technology and terminology. My client provides BExAnalyzer but doesn't have any technical details regarding automation. I've search all over the web and have been unable to find even the skimpiest documentation for BExAnalyzer Addin Methods and Properties. The only things I can find are small snippets of code that don't show the complete picture. Your article changed that with a beginning to end example of how to automate the loading, login, refresh and saving of a BExAnalyzer workbook.
That said I cannot make the code work without a login dialog popping up. I presume that means the script login failed. Once I complete the login the script continues performing the refresh and save. My questions are:
Can you point me to some documentation on the login and connection properties and methods?
After the script login the myConnection.IsConnected property contains a value of 8. What does that indicate?
Your example did not setup a login parameter for System or SystemID. One or both of those values are present in most other examples. I tried adding the System parameter without success. Might there be some parameters that are site specific?
Are there any logs or other debugging tools I can use to determine why I am not getting logged in via the script?
I’ve also tried running logic inside of MSAccess and MSExcel. In theory doing it that way allows me to reference type libraries. In Excel type library choices include BEx Analyzer and BEx Analyzer API. Do you know which I should be using?
Do you have any other tips for debugging this process?
Thanks so much for your help.
Andy
Hi Andy,
This might read a bit messily as the reply formatting here seems to just jam everything together.
I didn’t need to use System or SystemID as the combination of ApplicationServer, systemnumber and client gets to the right location for me.
My BExAnalyzer login details are as follows (only username/password/appserver name obscured this time) :
My BW system has nothing else on that server, so there isn’t any contention with other instances. Are you possibly trying to hook into a server with other instances on it (.systemnumber should still be handling it correctly though).
I also found SAProuter and Logon 0 was required for mine to be a bit more stable.
I reused the code from Example 1 on this page (expand the example to see the code) : https://wiki.scn.sap.com/wiki/display/BI/Using+VBA+to+connect+to+BW+server+with+SAPBEXgetConnection
The code on that page shows that anything that isn’t a 1 means it hasn’t connected correctly.
I should have that in the code for completeness purposes but found the connectivity reliable at my end, so kept it very simple.
One thing to try first up is check what your entry is for SAPLogon on your machine. BEX is installed as part of SAPGUI, and so it can also make use of the entries in your SAPLogon area.
Open SAPLogon, and make sure you have an entry in there that replicates most of the lines you have put in your code (System Number, Application Server Name/IP, Client etc).
May sound out of left field, but I remember having issues initially when I had left SAPLogon empty (as I assumed the script would handle all of the logon side of things, so why should the SAPLogon settings matter). I was getting the logon prompt at that point also. Putting in an entry for BW seemed to solve it, though potentially thats just pure coincidence.
Give it a blast and let me know how you go.
Cheers,
Regan
Forgot to mention, I have no specific debugging methods to recommend, as mine was trial and error based on various code snippets from various websites.
Analysis for Office is nicely documented, but I never really found a good single source for BExAnalyzer as yet.
Happy to work through it with you though.
Do you know the method to login to BIP (BO platform) using the available VBA macro?
I don't have any direct experience with logging in to BO with VBA sorry.
The following link may help though as the code shows a login method for BO.
http://www.mrexcel.com/forum/excel-questions/396937-launching-business-objects-excel-visual-basic-applications.html
Hi Andrew,
Did you find a solution to your problem ? How ?
I am facing exactly the same one !
Thanks
Rgds/Vincent
Another question I have is, have you had success in trying to save the xlsm as say a xlsx or xls w/o getting the prompt to overwrite the current file or get the compatibility checker to not appear if saving as a xls?
No problem. I have done this a fair bit previously, and will cover it in detail in a future blog.
Quick answer is as follows :
To save an existing file under a new name (no format change), you can use .SaveCopyAs
If you want to change the format of the file without prompting. You can use SaveAs with a specific fileformat. Unfortunately SaveCopyAs doesn’t allow you to change the format.
The key point to suppress any Excel prompting or compatibilty checks is to use DisplayAlerts = False beforehand.
With the fileformat numbers, you can use their names as well (51 = xlOpenXMLWorkbook = .xlsx for example), but using the number allows for better backwards compatibility with older versions of excel.
All of the FileFormat names and numbers are detailed on this page : https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
The key ones you are probably interested in are :
56 = xlExcel8 (.xls file)
51 = xlOpenXMLWorkbook (.xlsx file)
52 = xlOpenXMLWorkbookMacroEnabled (.xlsm file)
I’ve tried the code above on a .xlsm file, saving via vba to .xlsx and .xls and it works ok for me with no errors and no prompting and the files loaded cleanly again afterwards.
This link is really good for Excel fileformat changes via VBA : http://www.rondebruin.nl/win/s5/win001.htm
Thanks. I was thinking it was a specific sequencing issue. I'll give your sample a shot and see if it still gives me prompts.
Regan, thanks. Just tested it out and it works perfectly. I guess you do need to place the displayalert code right before you do the save as. Didn't think something as simple as that would solve the problem.
Hi Regan,
Cheers for the blog! I find it very cool digging into those things.
Would be looking forward to hints on possibility to connect to HANA DB (Not BW). Either using local connection or BIP. Also, submitting variable(s) would be nice.
Dmitry
I've got submitting variables (and filters) pretty much sorted now when it comes to AO. Planning to post a blog on it later this week.
Submitting Variables and filters (for AO) is now available here : Analysis for Office Variables and Filters via VBA
Regan I'm in the process of piloting this solution. What drawbacks have you found? Since you have to open a instance of excel do you ever notice a script hanging due to too many sessions of excel being opened?
Also are you running these scripts via task manager on a server? If yes, is the SAP GUI also installed on the server? I was told by our basis team SAP does not recommend installing the sap GUI on servers?
Lastly what generic user did you setup in BW to have it refresh the data? I assuming your not using your own personal account.
Hi Vi,
In the next few days I'll have another blog which expands on this method with more capabilities (easier control via an Excel table), but in the meantime answers on your questions are below :
I'm running it on a virtual server dedicated to task scheduling.
Its a simple Windows 2012 R2 server with 8GB ram (probably overkill for what I need). Miminal storage and CPU as well (as all of the BW files are elsewhere on the network).
I've installed SAPGUI 7.40 Patch Level 10, and Analysis for Office 2.3 (with MS Office 2010). There is pretty much nothing else on it.
You could use any windows machine/server that supports running Office, GUI, BEX and AO. THe only thing to be careful of is RAM if you are refreshing large workbooks.
With the automation I'm just opening a single instance of Excel at a time, and afterwards its a complete close of Excel. This keeps things very clean and avoids eventual memory issues and add-in clashes. I've found keeping excel open while automatically jumping between BEX and AO files can cause stability problems.
I don't have any conflicting scheduled jobs at this stage, so there is minimal chance of excel tripping over other instances of itself (though it should work fine with 2 or 3 instances). If I had to run multiple BW refreshes around the same time, I would put those files within the same scheduled task and run them sequentially.
For my initial testing however, I ran it on my local Windows 7 laptop. When running the scripts I wasn't trying to do any other tasks on my machine in terms of excel (and that is what I would recommend for anyone else too). I was easily able to work on other things in the meantime though (email, web browser etc), I just stayed away from excel for that short period of time.
As far as SAPGUI on a server goes, yes that works no problems at all. We also have it on a terminal server for some of our remote users and it isn't a problem at all. That applies to 7.3 and 7.4 gui versions.
The only setup quirk on newer servers is you need to put .Net Framework 3.5 on there for BEXAnalyzer to run, you can do this via the Add Roles and Features option in the server manager. Its in the Features section, not the roles section. People install .Net 4.5 assuming that would be compatible, but that doesn't contain the stuff that BEX actually needs. The install says it wants 2.0, and thats included as part of 3.5.
Yes I created a more generic account for the BW logon. Its still subject to the standard licensing agreements with SAP, but wasn't my direct account. This also allows me to tailor the access permissions on that account to be just the minimum required for the queries it is making use of.
There is a chance of course that things can still crash/fall over (loading a corrupt file before refreshing etc), and I haven't built anywhere near enough checking and error handling into the examples. Ideally, the scripts would look for file load and save errors ahead of time and handle them appropriately (like emailing an alert to someone). I'll probably put that into it as time permits.
Cheers,
Regan
Got my server specs wrong. Server ram is 4GB not 8GB. Virtual 2.6GHz CPU with two processors.
Regan, thanks for taking the time to answer my questions. If SAP GUI is required to be on the server then I might have a fight on my hand with our basis to see if that can be approved since they won't install a permanent copy on a server.
Thank you Regan for sharing this. We use broadcasting a lot and your solution could be an alternative for some of our use cases. Great work and good explanation.
Hi Regan,
thanks for the great manual. Somehow I receive the message: "The Macro "Refresh_the_Data" can not be exceuted. The Maco is not in this worksheet or all macros have been deactivated. Error Code: 800A03EC / Row: 16"
Do you have any idea what the problem might be?
Thanks
Thomas
Hi Tomas,
Three options on this one. Some may or may not work depending on your setup.
1) Take ‘Private’ out of the definition of Refresh_the_Data. That should make it visible to the external instance making the call. Private hides it, and is mainly useful when running things locally within the same workbook (which isn’t the case for this).
2) Check your macro settings in the Trust Centre area and have it set to ‘Enable all macros’ as your excel may be blocking the auto-enabling of macros in the test_update workbook.
3) Check out Part 2 of this series as it improves the method so you don’t need to put any macro in your AO file at all. https://blogs.sap.com/2017/01/15/automated-updating-of-bw-data-in-excel-files-bex-ao-via-vbavbscript-part-2/
I’m thinking option 3 above is probably the best choice for you, as it doesn’t need the destination AO file to be macro enabled at all.
Let me know if it works for you.
Cheers,
Regan
Hi Regan,
I have implemented your approach and it is working almost perfectly (thanks btw), I use the BEX approach and the automated process works very well, I scheduled a task to run the vbscript and I set the security option to run only when I'm logged on into the computer, then I run the task and the process runs perfectly.
But my intention is this task to run in the background whether I'm logged on or not so when I set the option in the task scheduler "Run whether user is logged on or not" and I execute the task the script is not able to make the BEX connection to the system, it gets stucked in that section I added a validation to verify if the connection was done and I got the error but with no messages or any clue about why the connection is not being done.
I guess it can be related to SAP Logon, because this program is always launched when first scenario is executed.
Have you faced something similar?
Hi Israel,
I have avoided that issue so far by having my scheduling running on a virtual server that I always leave an account logged in to.
I'll have a play around and see if there is a solution to it.
What is the operating system you have it all running on?
Cheers,
Regan
The following link may also shed some light on it, with lots of tips on getting the logged-out scheduling working.
It may still fall over for you, but is worth a look.
http://serverfault.com/questions/734035/running-a-batch-file-from-task-scheduler-without-user-being-logged-in
Thanks for your quick reply, definitly I’ll take a look at those options. I also have a virtual server, it is Windows Server 2008 R2.
Hi Former,
I had a problem trying to connecting by Bex Analyzer. Object does not support this property or method: Logon
Could you share your script please, I want to compare what is my error.
thx
Hi Regan,
Really interesting post, thanks for sharing.
My questions is the following, instead of manually inputting all files you want to refresh, can you also open all files from a folder?
Thanks
Hi Lucas,
Yes that would be possible, though it would only be relevant if you are wanting to just refresh everything in that folder without any changes to variables or filters, or have the same new variables/filters applied to everything in that folder.
Opening all files in a folder is fairly straight forward exercise in Excel using a loop to go through an array of all files in the folder of your choice (this page has a few examples : https://stackoverflow.com/questions/11152870/macro-open-all-files-in-a-folder)
Cheers,
Regan
Thanks Regan for your response. I just used a loop like you mentioned. I indeed have a bunch of similar files in which I put the same macro names, so I can use the loop in the scrip. The piece of script I used I paste below if anyone would want to use it: (No rocket science indeed 🙂 )
--------------------------------------------
For Each FIL in FLD.Files
Set xlBook = xl.workbooks.open(strFolder & "\" & FIL.Name, 0, False)
xl.Application.run "Refresh_the_Data"
xl.Application.run
xlBook.save
xl.ActiveWindow.close True
Set xlBook = Nothing
Next
Another question (And I went already through part 2 and 3 of your blogpost) I am struggling with:
I would like to change some of the variables before refreshing it. However, I want to include this in the script and not the macro. My situation is that I have +75 files that need to be refreshed every month, and I need to change one (maybe 2) variables in the 'Select Values for Variables'. I don't want to change the macro in each of the files before running it, that is why I would like to include it in the script, so I need to make one change.
Do you have any idea how to tackle it? Appreciate your feedback 🙂
Yes I think its possible though I haven’t tried it myself.
I’ll give it a try and reply back with the results.
Are you wanting it for BEX or Analysis for Office?
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
Thank you Regan for making it easier setting this up. Great blog.
Do you have anything working for passing variables to BEx Analyzer books?
Update:
I was just able to create a button using https://archive.sap.com/discussions/thread/3347923, and refresh the Bex book with button. I think I can automate things from here. Thanks Regan!!
Hi Regan (and all other experts here),
I've been trying to use the solution for connecting with SAP Analysis and for some reason the line
lResult = Application.Run("SAPLogon", "DS_1", bwclient, bwuser, bwpassword)
Is not working.
I get runtime error 1004 about the SAPLogon macro. What is the problem? Am I missing something? I have SAP version 7.4
My macros are enabled...
Thanks,
Marina
Do you have the Analysis Plugin enabled? That message implies the plugin isn't available or valid, so I can't find that option amongst all of the macros.
Regan,
To resolve this I had to call the macro Workbook_Open first (2 lines in bold are the ones I added to mytest.vbs:
' First enable Analysis
xl.Application.run "Workbook_Open"
' Run the Refresh macro contained within the file.
xl.Application.run "Refresh_the_Data"
4 years later but who knows, may help someone...
Hi Regan,
first of all thank you so much for your blog, this is exactly what I was looking for.
Basically all of the components of your code are working for me. Unfortunatly I have a problem right in the beginning (I am using the second method without BEX):
When I open Excel via the vbScript, the program always opens without the SAP analysis add-in activated. No matter what value the registry is set to. And when the program tries to run the code to active the Add-In, I get this error and the program stops:
The weird thing is: When I try to use the vbScript only to open Excel. And then try to activate the Add in, it does also not work.
So, in short: if Excel is opened via vbScript, it is not possible to activate the SAP analysis add in.
Do you, or anyone else have a solution for that?
Thanks!
Maximilian
Sometimes, I also get this error. I could not figure out what the reason for the different error messages is.
Martin RD came across the same error in this comment he posted recently : https://blogs.sap.com/2017/01/15/automated-updating-of-bw-data-in-excel-files-bex-ao-via-vbavbscript-part-2/comment-page-1/#comment-417563
Hi Regan,
thanks for your reply. It is interesting that I get the same error message.
However, my problem has nothing to do with running multiple instances, as far as I can tell. Even if I make a fresh restart and only open one instance of excel, I get the same error.
As I stated, for me the error is only and always ocurring when I open Excel via the VBScript.
BTW, I got a new, different error message now:
If you have any other idea, why this error could occur, I would be grateful for your insights.
Otherwise, thank you for your help!
Max
Update:
I got an update to Office 365. Now it works like a charm.
Hi I am getting error 438 (Object doesn't support this property or method) on line below line of function initConnection.
Here is the full code of the function:
I am running GUI 740. file version 7400.3.14.1131 Build 1769146 Patch Level 14.
Have a look at the subsequent parts (2, 3 & 4) of this blog for potential refinements on the add-in check.
Using the exact code in my blog series it works for me every time. This is from GUI 730 to 750.
I can't really comment on your revised code as I do not do the connection check that way.
Hi Regan,
good job.
I have a problem with AFO method in vba script, called from powershell (see the img); the same macro is working fine if we call from excel directly.
The core of my application is a powersheel script that trigger a series of excel marcos within AFO functions.
if we run the macro that contains SAP methods from powersheel, the powershell fails and displays macro excel error.
if we launch the same macros directly from excel, they work correctly.
I think the problem is related to the fact that from powersheel does not see the libraries of AFO: can you kindly help me?
Thanks
Pasquale
macro vba
Hi Regan,
we have solved.
the problem was that I ran 2 differently instances of excel $xlApp and $objectExcel
There is maybe a good reason for the error
In the code, the addin is connected only for $xlApp, not for $objectExcel
Thanks anyway
Hi Pasquale,
Thanks for updating with the solution. Great that you got it working from Powershell.
Cheers,
Regan
Hi Regan,
Thanks so much for putting this Blog together - it's been extremely helpful!
I've pretty much got this working except for one small thing. I can't figure out how to make my macros/vba script select ok on the SAP/Analysis login once it has been enabled (via a macro similar to macro 2 above).
The way our system is set up is that we do not have to input a password and username - it automatically logs in.
My problem is, that once Analysis has been enabled, it get's frozen here and won't move onto activiating the second macro that refreshes BW.
I'm hoping this is something easy to fix but can't seem to figure it out.
Any assistance would be greatly appreciated.
Thanks,
Matt
Sorry Regan - I should have probably mentioned that I am using your second method for AO.
Cheers,
Matt
Hi Matt,
I haven't tried it with Single-Sign-on yet, through will be doing so in the next few weeks. I'll update here with the revised code once I have it sorted out.
Cheers,
Regan
Hi Regan,
Thanks for your post.
Did you find time to work on the script changes related to Single-Sign-on ?
As I am unable to automate due to this log-on screen. Please suggest
Regards,
Peter I
No I ended up running on a non-SSO account that was dedicated to the automated reporting (normal users are still using SSO), so never go round to fully looking for a solution.
Looking through the forums this post may help : https://answers.sap.com/questions/12396288/excel-vba-single-sign-on-script.html
Peter,
For SSO change the line of code in Refresh_the_data to:
lResult = Application.Run("SAPLogon", "DS_1", "430")
Basically don't provide the user and pw. It will then logon automagically.
Thanks Aaron for that hint. Unfortunately this is not working in my setup, on the SAP Logon Screen the "OK" Button still needs to be pressed manually.
Any idea how to solve this?
Dear Regan,
I tried your code from above. Just to Log on to Bex Analyser 7.0 (SAP Gui 7.40) in Excel 2010. But always I get an Error on the login. When the Connection is set:
xl.Run "BExAnalyzer.xla!sapBEXinitConnection"
And the issue would be here:
Call pAddin.ExcelInterface.SetConnectionFromHandle(p_connectionObj)
in Standard function:
what could the problem? Thanks for any help.
best regards,
Manuel
Hi Regan,
I have a requirement. I have a analysis for office query. There is a dimension, It can be based on Row or column. I want to write a VBA to hide the column/row via lookup the member of dimension.
Let assume there is a dimension "Posting period". Its member is "April". If the member is April that April's Row/Column should hide automatically. And the thing is to remember this action should execute on the start up of the query.
Please help !
Thank you.
Regards
Parichay Gupta
Hi Experts,
I'm having a problem with SAPSetFilter using multiple values for the filter.
Query DS_8 delivers values in a column, that I try to feed into the values for the filter in DS_5.
LastRow = Sheets("DS_8").UsedRange.SpecialCells(xlCellTypeLastCell).Row
ApprovalbleList = Join(Application.Transpose(Sheets("DS_8").Range("F2:F" & LastRow)), ";")
-> ApprovalbleList looks like "PR1; PR2; PR2-V2; PR700;..." up to several hundred values
Call Application.Run("SAPSetRefreshBehaviour", "Off")
lResult = Application.Run("SAPExecuteCommand", "RefreshData")
lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_5")
lResult = Application.Run("SAPSetFilter", "DS_5", "4GPXXP_ARI20-FIELD1_CH", ApprovalbleList, "INPUT_STRING") -> lResult = 0
If lError(1) <> 0 Then
lMsg = Application.Run("SAPAddMessage", "Error: " & lError(1) & " " & lError(2), "ERROR") -> But lError = 0 as well
Else
lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_5")
End If
Call Application.Run("SAPSetRefreshBehaviour", "On")
If I copy the result column from DS_8 and paste it to the appropriate filter in DS_5 In the UI, I get the message, that not all of the pasted values are available for selection. Can this be the cause of the error I'm getting in my macro?
In fact, before putting in the results of DS_8 into DS5, I don't know which values are "valid" in DS_5.
Honestly hoping for help in this topic, any help or hint appreciated!
Thank you,
Claus
Hi Regan,
many thanks for this tool, it save me many hours.
I have just 2 issues:
1) I´m doing only refresh from BEX, I change login, password, system, etc. in file, password is correct, but every time I run process of refresh it opens window of BEx login screen and I need to add password manually. Is this some settings I missed in that macro, or it is just settings of BEx?
2) When I try to run VBS it always end with error message on line 24 (xl.ActiveWindows.Close True). It show error 800a03ec "unable to set close property of the windows class". Can you please help me with this also?
Many thanks,
Michael
Dear Regan
Suriprise for your powerful answer!
One more detailed question. how could we add filter in VBA?
For example, we need to add time range , category in different file.
Hi Ziwen,
Yes controlling filters via VBA is covered in the subsequent posts from me.
This is just Post 1 of the 4 I did on this topic.
Links to the other 3 are at the top of this page.
Regards,
Regan
Dear Experts,
Your ideas are very useful for me.Thanks.
Let me ask one more question.Hope yours idea.
I would like to control (disable, enable) the selection field of Analytical Report from Excel via VBA.
Regards,
Mya.
Hi Mya,
Unfortunately I don't know anything about that side of it. Potentially this sort of thing may be covered in the Analysis for Office book from Tobias Meyer (though I am not sure as I don't have the latest copy).
https://www.reyemsaibot.com/store/analysis-office-book/
Cheers,
Regan
Hi Regan,
Thanks for these blogs, very useful!
I am trying to connect to AO from VBA using:
But it does not work. The log in window appears, but without my password populated:
I do have DS_1 and I am using the right user name and password.
Do you know what the issue could be?
Thanks for your great posts again, "Analysis for Office Variables and Filters via VBA" was super useful, I just wish I would not need to login every time to be able to schedule overnight refresh!
Charles
This post here may help with that : https://archive.sap.com/discussions/thread/3836840
two colors,
Assuming you don't want to logon via BOE and don't want to click the Skip button in automation:
With the later versions of Analysis for Office you can go into File/Analysis/Customize Analysis/Options, select Platform tab, set Preferred Platform to Business Warehouse. Then it will directly allow you to logon via automation.
Hi Regan,
Very thanks for your reply and reference cover link.
Regards,
Mya.
Hi Regan
for me Calling and refreshing the data in an Analysis for Office (AO) file using VBscript not working
Below are the script not working properly
Option Explicit
Dim xl
Dim xlBook
set xl = createobject("Excel.Application")
xl.Application.Visible = True
xl.DisplayAlerts = False
Set xlBook = xl.Workbooks.Open("\\nzww.nzcorp.net\NZfileshares\NZSITES\INBE\DepShares\NZIN_FS_POC\Automation Project-2017\Sample Testing\Alert Report_Gran TEDA.xlsm",0,false)
xl.Application.run "Alert_AAO"
xlBook.save
xl.ActiveWindow.close True
xl.Quit
Set xlBook = Nothing
Set xl = Nothing
Please give some additional solution
Thanks Regan,
Very useful blog and macros (specifically the DMF file).
We did run into some issues with AFO login screen to BIP as noted by many users here but were eventually successful in replacing a BIP connection with local BW connection and also suppressing the BIP logon window.
Here's how to replace DS connection from BIP to BW and run the macro with SAPlogon:
Hope this helps other is same situation.
Regards
V
Hi Regan,
I am struggling with refreshing the data via BEX Analyzer. Everything seems to be working right, I come to the point when excel file with the existing BEX query in it is loaded but refresh doesn´t work. I need to click inside the query and refresh it manually. Could you please help me where the rub is?
Thanks a lot.
Regards
Betka
Hello there,
First of all thanks for the post. I’m facing the next issue:
Acording with IT people, we are no using applicationServer, instead we are using messageserver. How could i fix this?
Regards,
Hi Regan,
This is an extremely helpful post I was searching through this solution and finally i got it. I would request you send me the worksheet if possible to my mail id- arunhn67@gmail.com
Really appreciate your contribution.
Regards
Arun
Hi Regan!
perfect solution. It helps a lot!!!
But i ran into a problem. The script works perfectly as long i run it in the task scheduler when my user is logged in. If i select "Run whether user is logged or not" it does not run.
Any ideas about this?
Hello,
Very interesting. I'm guessing that adding a loop to process a list of partners and doing a SAVE.AS instead is not difficult. I will let you know 😉
Thanks again
Guy
Hi Regan,
I have this message when I try to connect by Analyzer:
Any idea of this error?
I connect successfully by AO. but I need to connect by Analyzer.
Dear Regan,
until now this script worked fine.
now after new update office 365 this script no longer works.
do you have a good solution here?
Dear Regan and Team,
Thank you for the excellent solution. I'm glad that this script is still useful in 2023!
I'm getting the error "the current system is not a BW System"
are system number and instance number same? I'm using Instance number.
The error is on
Line : 28
Char: 2
Error: The current system is not a BW System
code: 80131501
source: BeXCommuntion
Could anyone help to get my BeXreports automated? Thanks in advance.
Regards
Jonathan Thomas