Skip to Content
Author's profile photo Regan Macdonald

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.

Assigned Tags

      84 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Vivek Singh
      Vivek Singh

      amazing.. excellent information.
      Regards,
      Vivek

      Author's profile photo Tobias Meyer
      Tobias Meyer

      Hi,

      it looks nice. Similar to Distribution tools like BION Report Distributor or APOS.

      regards,

      Tobias

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Tobias Meyer
      Tobias Meyer

      Hi Regan,

      I don't want to reduce your work. It is really impressive. Hope to read more about this.

      Best regards,

      Tobias

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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 ?

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Sebastian Keim
      Sebastian Keim

       

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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) :

      With myConnection
      .client = "100"
      .User = "USERNAME"
      .Password = "PASSWORD"
      .Language = "EN"
      .systemnumber = "21"
      .ApplicationServer = "hanabiprd01.xxx.com"
      .SAProuter = ""
      .Logon 0, True
      end with

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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.

      Author's profile photo Vi Tran
      Vi Tran

      Do you know the method to login to BIP (BO platform) using the available VBA macro?

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      Hi Andrew,

      Did you find a solution to your problem ? How ?

      I am facing exactly the same one !

      Thanks

      Rgds/Vincent

      Author's profile photo Vi Tran
      Vi Tran

      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?

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      excelfilename = "<The full path and name of the new file you want to save>"
      
      ActiveWorkbook.SaveCopyAs excelfilename

      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.

      Application.DisplayAlerts = False
      
      excelfilename = "<The full path and name of the new file you want to save>"
      
      ActiveWorkbook.SaveAs excelfilename, FileFormat:=51
      
      Application.DisplayAlerts = True
      

      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

      Author's profile photo Vi Tran
      Vi Tran

      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.

      Author's profile photo Vi Tran
      Vi Tran

       

      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.

      Author's profile photo Dmitry Kuznetsov
      Dmitry Kuznetsov

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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.

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

       

      Submitting Variables and filters (for AO) is now available here : Analysis for Office Variables and Filters via VBA

      Author's profile photo Vi Tran
      Vi Tran

      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.

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      Got my server specs wrong. Server ram is 4GB not 8GB. Virtual 2.6GHz CPU with two processors.

      Author's profile photo Vi Tran
      Vi Tran

      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.

      Author's profile photo Lawrence Chan
      Lawrence Chan

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

       

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

       

      Author's profile photo Former Member
      Former Member

      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.

       

      Author's profile photo Bibiana Maria Palacio Jaramillo
      Bibiana Maria Palacio Jaramillo

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

       

       

      Author's profile photo Former Member
      Former Member

      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 🙂

       

       

       

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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?

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

       

      Author's profile photo Sangharsh Aggarwal
      Sangharsh Aggarwal

      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!!

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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.

      Author's profile photo Aaron Benner
      Aaron Benner

      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...

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Former Member
      Former Member

      Sometimes, I also get this error. I could not figure out what the reason for the different error messages is.

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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 

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Update:

       

      I got an update to Office 365. Now it works like a charm.

      Author's profile photo Sangharsh Aggarwal
      Sangharsh Aggarwal

      Hi I am getting error 438 (Object doesn't support this property or method) on line below line of function initConnection.

      Call pAddin.ExcelInterface.SetConnectionFromHandle(p_connectionObj)

      Here is the full code of the function:

      Public Function initConnection() As Boolean
      initConnection = False
      If isConnected Then
      With p_connectionObj
      End With
      If pAddin Is Nothing Then Call SetStart
      If pAddin Is Nothing Then
      initConnection = False
      Exit Function
      End If
      Call pAddin.ExcelInterface.SetConnectionFromHandle(p_connectionObj)<--Erroring here.
      If Len(pWbid) > 1 Then
      Call pAddin.ExcelInterface.runWorkbook(pWbid)
      Else
      If Len(pQueryid) > 1 Then
      Call pAddin.ExcelInterface.runQuery(pQueryid)
      End If
      End If
      End If
      initConnection = isConnected
      End Function

       

      Author's profile photo Sangharsh Aggarwal
      Sangharsh Aggarwal

      I am running GUI 740. file version 7400.3.14.1131 Build 1769146 Patch Level 14.

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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.

      Author's profile photo Pasquale Chimento
      Pasquale Chimento

      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

      #ps1
      $objectExcel = new-object -c excel.application
      $objectExcel.displayAlerts = $false # don't prompt the user
      #$objectExcel.visible = $True;
      $Classeur = $objectExcel.workbooks.open("C:\DATI\reportLux\test_lancio.xlsm", $null, $true)
      $objectExcel.run("Refresh")
      $Classeur.close($false)
      #$objectExcel.visible = $False;
      #$objectExcel.quit()
      spps -n excel

      macro vba

      Sub Refresh()
      
      Dim lResult As Long
      Dim addin As COMAddIn
      
       For Each addin In ActiveWorkbook.Application.COMAddIns
          If addin.progID = "SapExcelAddIn" Then
              If addin.Connect Then
                  addin.Connect = False
                  addin.Connect = True
              Else
                  addin.Connect = True
              End If        
          End If     
      Next
      
      lResult = Application.Run("SAPLogon", "xxx", "xxx", "xxx", "xxx")
      lResult = Application.Run("SAPExecuteCommand", "xxx", "xxx")
      lResult = Application.Run("SAPSetFilter", "xxx", "xxx", "<value>")
      'Call Crea
      End Sub

       

      Author's profile photo Pasquale Chimento
      Pasquale Chimento

      Author's profile photo Pasquale Chimento
      Pasquale Chimento

       

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      Hi Pasquale,

      Thanks for updating with the solution. Great that you got it working from Powershell.

      Cheers,

      Regan

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Former Member
      Former Member

      Sorry Regan - I should have probably mentioned that I am using your second method for AO.

      Cheers,

      Matt

       

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Peter Jeyaraj
      Peter Jeyaraj

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Aaron Benner
      Aaron Benner

      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.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Manuel Grieshaber
      Manuel Grieshaber

      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:

      Public Function initConnection() As Boolean
        initConnection = False
        If isConnected Then
          With p_connectionObj
          End With
          If pAddin Is Nothing Then Call SetStart
          If pAddin Is Nothing Then
            initConnection = False
            Exit Function
          End If
          Call pAddin.ExcelInterface.SetConnectionFromHandle(p_connectionObj)
          If Len(pWbid) > 1 Then
            Call pAddin.ExcelInterface.runWorkbook(pWbid)
          Else
            If Len(pQueryid) > 1 Then
              Call pAddin.ExcelInterface.runQuery(pQueryid)
            End If
          End If
        End If
        initConnection = isConnected
      End Function
      

      what could the problem? Thanks for any help.

      best regards,

      Manuel

       

      Author's profile photo Parichay Gupta
      Parichay Gupta

      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

      Author's profile photo Claus Noethen
      Claus Noethen

       

      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

       

      Author's profile photo Michael Hnilička
      Michael Hnilička

      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

      Author's profile photo Ziwen Geng
      Ziwen Geng

      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.

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Mya Thidar
      Mya Thidar

      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.

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      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

      Author's profile photo Two Colors
      Two Colors

      Hi Regan,

       

      Thanks for these blogs, very useful!

      I am trying to connect to AO from VBA using:

      Application.Run("SAPLogon", "DS_1", "000", "XXX", "XXX")

      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

      Author's profile photo Regan Macdonald
      Regan Macdonald
      Blog Post Author

      This post here may help with that : https://archive.sap.com/discussions/thread/3836840

       

      Author's profile photo Aaron Benner
      Aaron Benner

      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.

      Author's profile photo Mya Thidar
      Mya Thidar

      Hi Regan,

      Very thanks for your reply and reference cover link.

      Regards,

      Mya.

      Author's profile photo Surender Ramanakrishnan
      Surender Ramanakrishnan

      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

      Author's profile photo Vinod Tarange
      Vinod Tarange

      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:

      1. In AFO set Preferred Platform to "Business Warehouse", save & close AFO
      2. Open "Ao_user_roaming.config" file
      3. Find section: <BOESystems>xxxxxxxxxxxxxxxxx</BOESystems>
      4. Clear out the connections defined in <BOESystems>; we had five different connection details i.e. one each for our BOBJ environments. You may have more or less...
      5. Save file "Ao_user_roaming.config" and launch AFO once to check connection change
      6. Now, the macro is good to run.

      Hope this helps other is same situation.

      Regards

      V

      Author's profile photo Betka Mizerová
      Betka Mizerová

      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

      Author's profile photo Diego Vilches Loyola
      Diego Vilches Loyola

      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,

      Author's profile photo Arun kumar Mehta
      Arun kumar Mehta

      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

      Author's profile photo Frank Mueller
      Frank Mueller

      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?

      Author's profile photo Guy Lamoureux
      Guy Lamoureux

      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

      Author's profile photo Bibiana Maria Palacio Jaramillo
      Bibiana Maria Palacio Jaramillo

      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.

      Author's profile photo Halil Metin
      Halil Metin

      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?

      Author's profile photo jonathan thomas
      jonathan thomas

      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