Usage of SAPBEX setVariables method?

[Former Member]

Dear all,

I have a question regarding the usage of the SAPBEXsetVariables method which is part of the SAPBEX VBA-API.

I want to invoke a BEx Workbook from a VBA-Macro. The query, embedded in this workbook, is

using variables. (Some of the variables are further being processed by user exit, once a value for these variables

has been set.)

When opening the Workbook from my VBA-Macro, I don't want the variable screen to pop up. The variables should be set automatically during runtime, e.g. read from a spreadsheet. No user interaction should be necessary (reason: I need to batch process some 100 variables, that means, my workbook is being invoked a 100 times).

The method SAPBEXSetFilterValue does not work in this scenario, because of the above mentioned variable processing by user exit.

I thought, the SAPBEXsetVariables(varValues As Range) method might be the solution. Unfortunately, I couldn't find out, how to handle this method.

I understand, that the method is reading the variables and their respective values from an Excel Range object.

But I have no idea how variables and values need to be arranged within the range object.

Any help on this topic? Am I on the right track at all?

Thanks in advance.

Kind Regards,

Yogen Weinreich

[Former Member]

Hi Yogen,

Sorry I did not see your posting until just today.

I have not been able to use SAPBEXsetVariables to my satisfaction. But, I think I can tell you what to do.

1. set the local query property to "Save and reuse variable values;

2. use VB to set the variable value directly in the array that starts in cell FY2 on the worksheet named "SAPBEXqueries";

3. refresh the query.

Step 2 is the issue, right?

To make this worksheet visible, the VBA command is:

Sheets("SAPBEXqueries").visible = True

to return it to its original condition, the VBA command is:

Sheets("SAPBEXqueries").visible = xlSheetVeryHidden

You do not have to make it visible to change the values on it; but you probably will need to in order to find exactly what cells you want to change and the format you will need.

If you have more than one query embedded in your workbook, it can get confusing. If you need more, write back.

Regards, Pete

[Former Member]

Dear Pete,

thanks for your reply. I haven't been able to check your suggestion yet. The topic has been assigned a lower priority but it's still open. Still I'd like to use your offer and get back to you, if I have any questions.

Thanks again,

Yogen

[Former Member]

Hi Yogen,

I don't know if you are still following this thread, but I came back to this problem a few days ago and I now have the solution to the SAPBEXsetVariables method.

I like it better than the alternative solution that I was suggesting last month.

So, when you're ready to have a go at it again, I can give you the details.

- Pete

[dieterzenger]

Hi Peter,

I'm looking for an example to pass variable values by VBA. I implementet your solution above, but it would be great if you could give me an expample how to implement the SAPBEXsetVariable method.

Thank you.

Dieter

[former_member943888]

Can you please also let me know your VBA solution. I didn't yet find a documentation of SAPBEX setVariables and how the content of the range, used in the function, has to be filled in the Excel sheet to define, e.g. a select option (I, BT, Low, High) etc.

Thanks

Armin

[Former Member]

I'm glad to see that there is still interest in this subject.

First, I must say that we are still using 3.0B; there might be some differences in the specifics for other versions. I would be interested in hearing back if you are using a different version and if you have success or problems.

Step 1: locate the image of the variable refresh screen on the query repo sheet. In the Visual Basic Editor, Project Explorer window, the name of this worksheet is SAPBEXqueries. This sheet is "Very Hidden". It can not be made visible in Excel. To make it visible, in the Visual Basic editor, you can either select it (double click), then select in the Properties window change the Visible property to Visible. Or, using VBA, use code as follows:

Sheets("SAPBEXqueries").Visible = True

In this sheet, look in columns FY:GS

If you want to set all variables, select the entire table for the query of interest. If you want to set only some variables, just copy those. NOTE, however, that you DO NOT want column FY. What you want starts in column FZ and continues to column GS.

Step 2: use whatever means (probably VBA) to set the filters you want. If you want multiple filters on a single variable, add more rows. Be sure that there are no blank cells. Even a cell that appears blank has a ' in it.

Step 3: set the range that contains the variables you want to pass to BW. In the example code below, I have copied columns FZ:GS to a sheet with CodeName Sheet1 and have pasted starting in cell B3. My query is on another worksheet, with CodeName Sheet2. Cell B2 on Sheet2 is any cell in the query.

'define the range that contains the refresh variables

Sheet1.Select

lastRow = Range("B3").End(xlDown).Row

lastCol = Range("B3").End(xlToRight).Column

Set varRng = Range(Cells(3, 2), Cells(lastRow, lastCol))

'select a cell in the query

Sheet2.Select

Range("B2").Select

Run "SAPBEX.xla!SAPBEXsetVariables", varRng

Run "SAPBEX.XLA!SAPBEXrefresh", False

I've implemented this in a dozen different very different situations over the past couple of weeks and it works like a dream. If I set some of the variables, the others still show up in the refresh screen. If I set all of the variables (even with blanks), then there is no refresh screen. But, if the user chooses to refresh directly (without using my code), they see the refresh screen that they expected.

Good luck! I hope to hear of your success soon.

- Pete

[Former Member]

Hi Pete,

> I don't know if you are still following this thread,

> but I came back to this problem a few days ago and I

> now have the solution to the SAPBEXsetVariables

> method.

Yes, I am still following this thread as well as other threads on SAPBEX VBA in this forum. That's how I came across the thread named <i>suppress variable-entry-window (SAP-Exit)</i> which is dealing with the same problem. The answer you are giving there regarding those two hidden repo sheets is rather impressing and was helpful to me in solving our problem.

I our solution, we are actually not using the setVariables() method at all. Instead, we are directly writing the

variable values in the respective cells of column GE/187 on the hidden "SABBEXqueries" sheet. This works fine for us.

I'm facing a different problem though: We have been developing and running our solution from a local PC. The

problem is, that we need to upload the BEx-Workbook containing the VBA-Code to the SAP server. But there

seems to be a naming conflict with the SAPBEX library, which needs to be referenced by the VBA modules we developed and a module also named SAPBEX, which is containing the SAPBEXonRefresh() event/procedure.

Whenever we want to insert the SAPBEX library (in the menu bar of the VBA editor select Extras - References) a message window pops up, saying that there is a naming conflict between this library and an existing project or module. If I delete the SAPBEX module (the one containing the above mentioned event) I am able to insert the SAPBEX library. But when I save this very workbook to the SAP server, close it and try to open it again, I get an error message saying something about a "Runtimerror '5':

Invalid procedure call or invalid argument". I guess, it's because of the SAPBEX module I deleted.

Any suggestions on this one?

> I like it better than the alternative solution that I

> was suggesting last month.

> So, when you're ready to have a go at it again, I can

> give you the details.

Sure, I'd definitely be interested. Is it the one you gave to Armin Weh in this thread?

BTW, since you were asking, we are using Release 3.5.

Regards,

Yogen

P.S. Pete, as you can see, there is still interest in this subject and your help is really being appreciated.

I haven't really figured out, how the reward point systems works in this forum, but you have definitely earned yours.

[dieterzenger]

Hi Pete

Thanks for providing this example. I've implemented it and it works perfect.

Dieter

[Former Member]

Hi Yogen,

An interesting problem you are having. And easily solved, I believe.

You can rename the SAPBEX module to anything you like (using the VB Editor Properties window). Or, move (cut and paste) the SAPBEXonRefresh subroutine to another module (as long as it is not specified as a Private module).

I just tested this in my system. I was able to re-create the name conflict. When I renamed the SAPBEX module to SAPBEX1, the name conflict went away.

Second question: yes, the note from me about 14 hours ago describes how I am using SAPBEXsetVariables. Let me know if you need any further details or examples.

I'll tell you why I prefer it to writing directly to the queries hidden sheet.

1. I can decide which part of the refresh screen I want the user to interface with, and hide the rest. Of course, I could do the same by creating a new query; but, if I can use just one query to fit many needs this is a better solution for the company, I think.

2. I might not know in advance how many instances I will need to specify for a given characteristic filter. Using set Variables lets me expand or contract the number of filters easily. This, too, COULD be done directly on the hidden sheet; but, it becomes more complex (and slower) if I have many queries in the same workbook. The one I'm working on today has 14 views of the same query in a single workbook and I want each of them refreshed slightly differently to create the correct response.

It is very good news for me that these same tricks will work in Release 3.5; thanks for sharing.

- Pete

[dieterzenger]

Hi Pete

I have the same problem as Yogen, when I open the Workbook from the server with activated VBA Reference to SAPBEX. --> "Invalid Procedure call or argument" and Excel needs to be closed.

Does your above answer mean, I have to rename the file "SAPBEX.XLA" to avoid this.

I haven't creadet a SUB with name SAPBEX which I could rename.

Thanks

Dieter

[Former Member]

Hi Dieter,

I am not suggesting changing the name of the file SAPBEX.xla. I do not think that would help, and it will create other problems.

The problem is that in the workbook there is a Module named SAPBEX. The name of that module conflicts with the name of the add-in (which is also named SAPBEX).

I am suggesting changing the name of the Module in the Workbook that is giving you the problem. I do not know how you can do that, however, since you are unable to open the file.

If I am reading correctly, your problem is similar to Yogen's ... but it is not identical.

Yogen was not able to create the reference because of the conflict. You were able to create the reference (somehow) and now you have a conflict. I am not sure how you were able to do that. I am not able to reproduce this situation.

If I think of anything else, I will post again.

- Pete

[Former Member]

Dieter and Yogen,

I've been asking myself for 3 days now ... why do you find it necessary to specifically set a reference to the SAPBEX add-in?

What functionality is missing if you do not set this reference?

When I set the reference, I do not see any functions available that were not already available. The coding is simpler, perhaps, but this does not compensate for the problems it is causing you.

I'm puzzled.

- Pete

[Former Member]

Hi Pete,

it's simple, if I don't set a reference to the SAPBEX add-in, I get an error message during runtime as soon as the interpreter is trying to invoke a SAPBEX method.

Here ist what I do:

I save a query to a BEx-workbook. This workbook acts as the user interface.

Next I add the module and class module (both containing SAPBEX methods) that we developed locally to the above mentioned workbook. I save it as an existing workbook to the SAP-Server. I close the workbook, open it again and try to run the VBA code. At the first occurrence of a SAPBEX method I get a compiling error message saying, that the sub or function is not defined.

The next thing I do is, I hit the stop-Button, go to the 'Extras' menu in the Visual Basic menu bar and try to add a reference to the SAPBEX API. A window pops up, saying that there is a name conflict between this reference and an existing module or project.

Alright, now I'm doing as you suggested, I simply rename the SAPBEX module (the one containing the SAPBEXonRefresh subroutine). Having done that, I can now add the reference to the SAPBEX API. And now, the invocation of SABBEX methods is working fine.

What I do next is, I once again save my workbook as an existing workbook and close it. But, when I now try to open it again, I get the error message Dieter and I have been writing about: invalid procedure call or argument. Excel is then shutting down.

Well, that's the whole story.

Maybe a solution would be to do the whole renaming and referencing procedure during runtime and do a re-renaming and de-referencing at the end of the code. I have to admit, momentarily I don't know if that's possible at all. Pete, do you happen to know?

Regards,

Yogen

[Former Member]

OK. I think I understand now. Depending on how the workbook is loaded, the BW Analyzer (SAPBEX.xla) might not be automatically loaded. If you attempt to use one of the SAPBEX.xla functions when it is not loaded you get a run-time error.

There are solutions to this without creating a reference in VBA.

1. Have users install SAPBEX as an add-in. In Excel menu, select Tools >> Add-ins. If SAP Business Explorer Analyzer is in the list, click it. If it is not in the list, then use Browse to navigate to the directory where the Analyzer file is saved.

This will cause the add-in to load every time that Excel is opened. That might not be the best solution since it takes up memory and can be a problem for older PCs with less than 256MB memory.

2. Before running the routine that uses the SAPBEX function(s), check to be sure that it is loaded. For example:

Function BEXisLoaded() As Boolean

Dim vbp As Object

BEXisLoaded = False

For Each vbp In Application.VBE.VBProjects

If vbp.Name = "SAPBEX" Then

BEXisLoaded = True

Exit Function

End If

Next vbp

If Not BEXisLoaded Then

'your path might be different!

Workbooks.Open(FileName:="C:\SapGUI32.623\Bw\sapbex.xla").RunAutoMacros _

Which:=xlAutoOpen

End If

End Function

This method will not cause the Analyzer to open every time that Excel is opened.

- Pete

[Former Member]

Hi Pete,

unfortunately, that doesn't solve the problem. I'm getting the mentioned runtime error, although SAPBEX.xla is loaded. As you mentioned, we have this add-in loaded, each time Excel is opened.

Regards,

Yogen

[Former Member]

Hi Yogen,

That's very troubling. Perhaps I am not seeing that because I am using an older Release of BW and perhaps also an older version of GUI.

Can you give me an example of exactly which line of code it is trying to run when the error occurs?

- Pete

[Former Member]

Hi Yogen,

The good news, if you can call it that, is I have successfully reproduced the problem. When I add the reference to SAPBEX add-in and save the workbook to the server, opening the workbook from the server causes Excel to die.

Opening the same workbook from a local or LAN drive does not do this. But, that is not a solution to the problem.

So, I am back to wondering why the reference is necessary. It is not a difference in Excel. I have run this now on Excel 97 and on Excel XP. I can access all of the SAPBEX.xla functions without making a reference.

The syntax is different with and without the reference. Can you post a line of code or two that does not work for you without the reference?

- Pete

[Former Member]

Hi Pete,

it's any line of code that starts with SAPBEX... that causes the program to stop.

For example:

WBID = "3WG1H0UFM8VKX47TV3OZCC31Z"

WBName = SAPBEXreadWorkbook(WBID)

Yogen--

[Former Member]

Hi Yogen,

This is a big help! I would write the code as follows:

WBID = "3WG1H0UFM8VKX47TV3OZCC31Z"

WBName = Run("SAPBEX.xla!SAPBEXreadWorkbook", WBID)

Using this format to call the function, I do not need to create a reference to the Add-in. This is exactly what I meant in my last posting when I wrote that the syntax is slightly different when you have created a reference to the Add-in.

Microsoft would tell you that it is more efficient to create the reference (similar to declaring / dimensioning variables). But, it works either way. You should be able to use all of the SAPBEX functions without creating the reference.

I hope this is helpful.

- Pete

[Former Member]

Hi Yogen,

First, see posting I made earlier today.

Here is a link to coding examples for (almost) every function in the SAPBEX API.

www.bwexpertonline.com/downloads/boeke04.doc

No reference is required if you follow these examples. Note: the example on SAPBEXsetVariables at bottom of page 1 is incorrect! It should be:

Run "SAPBEX.xla!SAPBEXsetVariables", rngVar

Also note that to use this function you must first select a cell that is in the query definition.

- Pete

[Former Member]

Hi Pete,

thanks a lot. Your hint solved the problem.

It finally works, even without seeting the reference but using the different kind of syntax instead.

You are great!

Regards,

Yogen

[Former Member]

Hi Pete,

This is really helpful information. However I wanted to pass these parameters by my sheet only.

1.) If you have any document which can help me to identity the different parameters in ("SAPBEXqueries") sheeet. So that I can fill them automatically in VBA macro and pass that range in Run "SAPBEX.xla!SAPBEXsetVariables", varRng.. function. to run query.

2.) How can we hide the query designer window while refreshing the query through VBA Macro and passing these parameters from VBA Macro only.

If any one can help me in these two issue, that will really greatful.

Thanks

Sharad Singh