Skip to Content
Introduction

As part of our migration strategy, we need to decide what to do with our workbooks. As BEx Analyzer has been around the longest of most of our BI Tools, this is generally the most widely deployed in our current customer base. Therefore, over the years, users may have become very savvy with these workbooks and may have ventured into using Visual Basic within these applications. This blog shows what BW 3.x functions in VBA are part of the public API that was published. It also discusses what the corresponding function is in SAP NetWeaver 2004s BI.

How do we compare?

Within SAP NetWeaver 2004s BEx Analyzer, a design toolbar has been added that allows us to use many elements and features we did not have previously. Therefore, in general, the use of Visual Basic is generally going to less in the new environment. However, we are talking about what migrates, so let us take a look. Figure 1 shows what functions existed in BW 3.x and their corresponding functions in SAP NetWeaver 2004s BI.

NOTE: Not all functions that existed in BW 3.x exist in SAP NetWeaver 2004s BI. In general, this is because these functions generally would not be needed due to being replaced by new objects available in the new design toolbar. However, this may cause some problems for migration.

Figure 1 – Comparison of Functions

BW 3.x
SAP NetWeaver 2004s BI
Comments
SAPBEXgetWorkbookID
SAPBEXgetWorkbookID
 
SAPBEXreadWorkbook
SAPBEXreadWorkbook
 
SAPBEXsaveWorkbook
SAPBEXsaveWorkbook
 
SAPBEXgetErrorText
SAPBEXgetErrorText
 
SAPBEXsetFilterValue
SAPBEXsetFilterValue
 
SAPBEXgetFilterValue
SAPBEXgetFilterValue
 
SAPBExcopyFilterValue
   
SAPBEXsetDrillState
SAPBEXsetDrillState 
 
SAPBEXgetDrillState
SAPBEXgetDrillState
 
SAPBEXrefresh
SAPBEXrefresh
In the 2004s Analyzer, ALL the items in the workbook can be refreshed, but refreshing a query individually is not supported
SAPBEXpauseOn
BEx.Synchronize = true
 
SAPBEXpauseOff
BEx.Synchronize = false
 
SAPBEXfireCommand
   
SAPBEXcheckContext
   
SAPBEXgetFilterValue
   
SAPBEXshowTextElements
   
SAPBEXembedQuery
   
SAPBEXgetResultRangeByID
SAPBEXgetResultRangeByID
 
SAPBEXjumpInitial
   
SAPBEXjump
   
SAPBEXgetResultRange_ResultArea
   
SAPBEXgetResultRange
   
SAPBEXtoolbar
   

What to be careful of?

We see the functions that are the same between BW 3.x and SAP NetWeaver 2004s BI. In general, these things will migrate ok. The functions that have changed and that do not exist will generally require manual work for migration.

Are there other functions in SAP NetWeaver 2004s BI?

Of Course! There are lots of new VBA functions available in the public xBEXapi module. In addition, the interface to call connection handling, BEx Precalculation, and other common functions are also in a public interface. Keep in mind that you should NOT use VBA as a first step. This is only after you’ve explored the new native capabilities in the BEx Design Toolbar.

Where do I find details and examples?

Easy. The best place to go for documentation is the online help. For a list of the functions for the BW 3.x runtime, see the online help here: Integration with Visual Basic for Applications. For SAP NetWeaver 2004s BI, you don’t even need to go to the online help. The functions are documented directly within the modules. Open the Visual Basic Editor with the SAP NetWeaver 2004s BEx Analyzer and go to the xBExAPI module to see all the functions you can run! They are commented right there…

Summary

This is just one part of your migration picture. For a full view see: Rolling out the New SAP NetWeaver 2004s BI Frontend Tools

To report this post you need to login first.

34 Comments

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

  1. Sini Kumar
    Hi Prakash,

    Is refreshing a query individually going to be supported in later Support Packs?
    This becomes a problem for current workbook using multi query that needs to be refreshed individually.

    Please advise!

    Thankyou,
    -Sini

    (0) 
  2. Laura Compton
    Is refreshing a single query in a workbook planned for later service packs???  I believe that it was taken out of SPS11 so that stabilization could occur.  Do you know when it is now scheduled??

    Thanks,

    Laurie

    (0) 
  3. Fionna Sze
    Greetings!

    Great series of blogs, Prakash.

    I have two questions:

    1) When I try to migrate a workbook (it has about 7 queries spread over a few tabs and a tab-mainpage with formula taking values from the 7 queries’ cells), it errors out saying “A critical program error occurred.  The program has to close….” and then it shuts down Analyzer.  I get the same error when I play around the Analyzer long enough. Have you encountered this before?

    2) On a separate case, I was trying out the new functionality, and used the drop-down for a master data attribute.  The idea was the query will filter according to the value I select from the drop-down… however, if I pick value Name =”ABC” for instance, it will show result set of Name = “XYZ”.  Any idea why this is happening?  (Also tested the radio-button and check-boxes, they work just fine.)

    Extra info:
    SAP GUI 710 (from compilation 4 CD) no other patches applied.  Running Office 2003, made sure the Macro security is set accordingly.

    Thanks!

    Cheers,
    Fionna

    (0) 
  4. Bruno Garcia
    Hello,

    I’m currently migrating a workbook that has VBA enabled.

    One of the functions that were used previously was SAPBEXpauseOn(). Since this function no longer exists, and it should be used BEx.Syncronize = True, when I replace one for another, the systems states the error “Object Required- 404”

    Any Ideas? How can I use the older SAPBEXpauseOn or any substitute?

    Thank You,
    Diogo Ferreira

    (0) 
    1. Bruno Garcia
      Hello once more,

      Does anyone have an idea why when we create a brand new macro “Macro1” with BEx functions called, for example, setdrilldown, and we ran the macro it executes normally, but when we put this code in the macro “SAPBExOnRefresh” it executes the code normally but doesn’t release the application? Excel keeps the time icon, like it was doing something but does not free the application, we have to manually kill the excel?

      (0) 
  5. Rainer SIGG
    Hello,

    we need to invoke a macro after refreshing a workbook, which we called within the “SAPBExonrefresh” in BEx 3.5.
    We converted the queries in the workbook to formulas and wrote the macro name in the workbook settings “invoke macro on refresh”, but it is not invoked.

    Thanks in advance for your help,

    Rainer Sigg

    (0) 
      1. Phaneendra Mudunuri
        HI Hugues Lesoil,

        We have recently upgraded the workbook from 3.x to 7.0 version.
        When I try to change the sub definition from Public Sub SAPBEXonRefresh(queryID As String, resultArea As Range) to Public Sub SAPBEXonRefresh(queryID() As String, resultArea () As Range) Then the VB Code is giving me an error Out of memory error.

        Can you put forward your suggestions on this
        Thanks in Advance
        PV

        (0) 
  6. Arno Hermans
    Hi Prakash,

    At the moment I’m struggling with a problem and just can not find the solution anywhere at this moment, so I hope perhaps you have any idea how to solve this. I’m responsible for automated regression testing within a project, and I automated with (amongst others) VBA in Excel using the BEx Analyzer 3.X the complete expanding/drilling down of the rows of a query result. Within the BEx Analyzer 3.X, while looping all the data rows, on each row I executed the following VBA code, with the result that the specific row was expandend/drilled down:
    retVal = Run(“sapbex.xla!SAPBEXcheckContext”, “HDEX”, myCell)
    If retVal = 0 Then
    ‘OLAP context checked OK
    retVal = Run(“sapbex.xla!SAPBEXfireCommand”, “HDEX”, myCell)
    Else …..

    So I used the SAPBEXfire command for expanding the specific row. Now however, we want to use the new BEx Analyzer 7, so I have to migrate all my testactions. The only problem I have at the moment is that the SAPBEXfire command is not available in the new BEx Analyzer. And I just can not find or think of any workaround. Do you have perhaps any idea what command I could use for expanding/drilling down a row in Excel? Is there any alternative for the “Run(“sapbex.xla!SAPBEXfireCommand”, “HDEX”, myCell)” command. Any help much appreciated.

    (0) 
  7. Jens Consör
    Hi all,

    i am on netweaver 2004s sp14 + excel 2003

    there is definitely no comment or documentation in any of the api functions in bexanalyzer.xla!
    ..besides the hint in some functions: ‘Not supported in 7.0’..

    there is as well no documentation in the online help!

    next thing is: where can you get the local query id (which you need for some functions) from? .. it is not anymore under “properties” ..there you can only get the technical name.

    third an most annoying issue:
    the drill functions and filter functions don’t work anymore although they are not marked with ‘not suppoerted in 7.0’
    they always stop with a message of the debugger ..something like :
    ‘runtime error 1004. cannot assign the intersect property of the application object’ in the code line where the paddin method is called (e.g.   SAPBEXgetDrillState = pAddin.SAPBEXgetDrillState(ActiveWorkbook.Name, currentState, atCell)
    ).

    is there any solution?

    thanks in advance

    (0) 
    1. Andreas Franke
      We have exactly the same problem here:

      Runtime error 1004, complaining about intersection when calling SAPBEXgetFilterValue or other routines.
      Office 2003, Service Pack 3.

      Any suggestion?

      (0) 
  8. Nata Abramenko
    Dear Prakash,
    Is there a possibility to access the Report Report Interface (RRI) from VBA code in SAP NetWeaver 2004s BI? Which function should I use instead of SAPBEXjump?
    (0) 
  9. Eric GNACADJA
    Hi,

    The question:
    how can I programmaticaly setting the value of a variable. That’s without poping up the variable dialog box (with SHOW_VARIABLE_SCREEN for example) on BI 7.0 SP14 (BEx Analyser front-end).
    As I read on SDN this was possible with the API function SAPBEXsetVariables. Unfortunaly this function seems to be deprecaded.

    Now my business scenario (not really needed to answer the question but any design advice is welcomed!):
    I’m designing a IP application for our controling department. This involve 2 realtime infocube which both belong to my aggregation level.
    Some of the planing function need both infocubes but the most just need one of them (the planing infocube). The idea is (with vba code) checking according to the function called whatever the both infocube are in the filter or not. Evently setting the variable containing the InfoCube correctly.

    Thanks in advance for your answers,

    Eric

    (0) 
  10. kirian kirian
    Hello All,

    i am really irritated with the new “Advanced BEx Analyzer Workbooks”. There is no documentation for the new/changed functions, the essential questions
    concerning the problems on the forum (and on this page) get no answer from SAP.
    It seems that SAP knows, that this area in 2004s is not (yet?) properly implemented and tries to keep silent.

    >>Are there other functions in SAP NetWeaver 2004s BI?
    >>Of Course! There are lots of new VBA functions >>available in the public xBEXapi module.

    can you name this new functions, i can only see in this module that many functions that are really needed are not supported in 7.0

    >> For SAP NetWeaver 2004s BI, you don’t even need >>to go to the online help. The functions are >>documented directly within the modules.

    i have not found any online help for 2004s VBA integration. If i tell my customer, he don’t need any documentation, he can just analyse the comments in the code, i will be fired on the next day. On the other side, i have found no proper documentation in this code …

    Regards,
    Kirill

    (0) 
  11. Andreas Franke
    Dear Prakash,

    I found some sample coding to get started with the new BEx API for Excel here:
    https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/8065f00c-59bc-2a10-31ab-9c538126d775

    Some generic sample coding to access the results of a certain query through its data provider I adopted from that document:

    Dim bex1 As Object
        Dim bexitems As Object
        Dim bexitem As Object
        Set bex1 = Application.Run(“BExAnalyzer.xla!GetBEx”)
        Set bexitems = bex1.Items
       
        For Each bexitem In bexitems
            ‘ Further Coding …
            Debug.Print bexitem.Name
        Next bexitem

    If I stop with the VBA debugger, I can see in the debug window what is the real type of bexitem although it is declared as “Object”: BExItemGrid

    In debug view I cannot access any sub-objects or variables of bexitem (probably because the variable “bexitem” is declared as object instead of as “BExItemGrid”). But the coding in the document uses e.g. “bexitem.Name” or “bexitem.DataProvider”.

    So the question is: how did the author get the information what the structure of BExItemGrid-Class is? Just give me a hint on how to find that out. It will probably be enough just to add a reference to the library containing all that classes to my project, so I can declare the variable as BExItemGrid instead of generic “Object”.

    Thank you very much.
    Regards,
    Andreas

    (0) 
    1. Obaid Ullah
      I’m having same issues. Unable to find object library and get more information about the object. Have you got any further information about object library.
      (0) 
  12. Denis Titho
    Dear Prakash,

    I´m still missing any kind of documentation for the VBA possibilities! SAP Help, SDN as well as the BEx-Addin coding does not really help.

    Where can we all get the information about the object modell of the BEx? Which function accepts wihch parameters? Where are some examples?

    Obviously some people DO know things about it since some code fragments are in the air…
    I´d like to be one of it, since I really do need to program some things for our customers…

    Regards
    Denis

    (0) 
    1. Prakash Darji Post author
      Hey Denis,

      I’ve not worked on BEx topics in quite some time now. I’ll let the relevant colleagues at SAP know that they should look at adding examples. Cheers,

      Prakash

      (0) 
  13. Carlo Conti
    Dear Prakash,
    with analyzer 7.0 i can’t display variables values in workbook text item.
    I have some variables used for restricted key figure.
    With analyzer 3.x i can see variables values in the variables area of text elements.
    But with analyzer 7.0 i can’t.
    Why?

    thanks in advance
    Alberto

    (0) 
    1. Smiley Smile
      Hi Alberto,

      I also facing the same issue as you. May I know have you find out any solution to display the text elements as in Analyzer 3.x?

      Thank you and really appreciate your help.

      Best Regards,
      Smiley

      (0) 
  14. Senthilkumar Packirisamy
    The new BI7X front-end looks to be a bad development from SAP with limited functionality.Atleast expected the same functions as in 3x.

    Too many limitations which are  critical and requirement – which worked in 3X.

    (0) 
  15. Camacho Jairo
    Dear Prakash,

    When we could expect a solution for RRI between workbooks? In BI 3.x we used the function SAPBEX.XLA!SAPBEXjump (VBA) but it was not implemented in BExAnalyzer.xla in BI 7.0. You explained in your blog some functions were not created because they maybe were replaced with objects in the new design toolbar. I undertand this is not the case for function SAPBEXjump. If there is any solution right now, please let me know.

    Thanks in advance

    Jairo

    (0) 
  16. Uday Gupta
    Hello.

    We wanted to have a query inserted in a workbook by simple 1-click command as it was done in 3.X toolbar.

    We are aware that it is possible via design mode etc.  However, this is very cumbersome and more over for end users this is too many steps to insert one query in workbook.

    I attempted to automate this process by manual recording of a vba macro, however vba macro steps are not recorded for BEx 7,0 Toolbar functions or Workbook design mode functions.

    I attempted to Insert button and configure the button to do this, however this is not possible either. 

    Is that way or method to have a new query inserted in a workbook in 1 click method?

    Thanks
    Uday

    (0) 

Leave a Reply