Skip to Content

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.

To report this post you need to login first.

37 Comments

You must be Logged on to comment or reply to a post.

    1. Regan MacDonald 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

      (0) 
  1. Shailendra Tandon

    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

    (0) 
    1. Regan MacDonald 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 🙂

      (0) 
  2. 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

    (0) 
  3. Andrew Schmidt

    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

     

    (0) 
    1. Regan MacDonald 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

      (1) 
      1. Regan MacDonald 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.

        (1) 
  4. 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?

    (0) 
    1. Regan MacDonald 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

      (0) 
      1. 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.

        (0) 
        1. 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.

          (0) 
  5. 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

    (0) 
  6. 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.

    (0) 
    1. Regan MacDonald 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

      (0) 
    2. 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.

      (0) 
  7. 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.

    (0) 
  8. Thomas Breuß

    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

    (0) 
    1. Regan MacDonald 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

       

      (0) 
  9. Israel V

    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?

    (0) 
    1. Regan MacDonald 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

      (0) 
      1. Israel V

        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.

         

        (0) 
  10. Lucas Carette

    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

    (0) 
    1. Regan MacDonald 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

       

       

      (1) 
      1. Lucas Carette

        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 🙂

         

         

         

        (0) 
        1. Regan MacDonald 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?

          (0) 

Leave a Reply