Refreshing queries from VBA that expect prompted values

[Former Member]

Please consider:

Run "SAPBEX.XLA!SAPBEXrefresh", False, SAP01.Names("SAPBEXq0001").RefersToRange

(SAP01 is my code name for SAPBEXqueries.)

Assuming the query requires a date for refresh, I would expect the following to work:

Run "SAPBEX.XLA!SAPBEXrefresh", False, SAP01.Names("SAPBEXq0001").RefersToRange, format(Date,"mm/dd/yy")

but it does not... replying, 'invalid # of arguments'.

So I tried:

Run "SAPBEX.xla! SAPBEXSetFilterValue", Format(Date, "mm/dd/yy"), "", BEXD02.Range("ab6")

(BEXD02 being the codename of the query embedded sheet.)

but no dice.

[Former Member · Accepted solution]

Please bear in mind that I so not wish to usurp the capabilities of the SAPBEX toolbar and its 'one-stop & shop' button to refresh all queries in the workbook.

All of the workbooks to date I've created are refreshed through the SAPBEX toolbar. Most queries are refreshed by the same OCALMONTH prompt and after one query refreshes and the next uses the date to refresh WITHOUT encumbering the user to enter he date again. Depending on how well the queries are synced and/or written, once can refresh a host of queries with just one prompt.

However in this situation, one of my two queries will accept OCALDAY (or thereabouts) while the 2nd query takes prompts for something different, yet the same. Behind the scenes, the technical names of each prompt are unfortunately different.

_____________________________________

After the 1st query prompts for 'mm/dd/yy' and refreshes itself, a 2nd prompt (for 2nd query) again prompts for 'mm/dd/yy'.

Since both queries/prompts will ask the user the same question, it's silly to have 2 prompts. I want to prevent the 2nd prompt from being displayed and instead want to carry out the refresh of the 2nd query in code.

As you are probably aware, intercepting the values used to refresh a query are a piece of cake. If after the 1st query refreshes I intercept the refresh date, how can I pass that to the 2nd query so that the user is not needlessly prompt again for the same information?

[Former Member · Accepted solution]

Hi Bill,

Clear.

Given your preferences, I think I would NOT use API functions for this.

So, the first question is: will the users always want to refresh all queries or sometimes want to refresh only one?

Second question ... will the user always refresh the same query first? Or will they sometimes change which query they refresh first?

As you say, when one query is refreshed in a workbook, it is not that difficult to know which one it was. You can:

1. use SAPBEXonRefresh routine; or,

2. look at numbers in column B of SAPBEXqueries sheet; or,

3. just see which query's information is at the bottom of the stack in any of the tables on the repository sheets.

Then, you can read the date (or date range) used to refresh that query by reading from columns GD, GE, and GG; and, use this information to populate other date variables (changing format as required to satisfy needs of different date variables).

Change "FALSE" to "TRUE" in column P to force the other query (or queries) to use saved variable values. After refresh of other queries, change all entries in column P back to "FALSE".

Let me know if you would like to see sample code for any of this.

The downside / risk of this approach, as you probably know already, is that in some future release of BW, the locations of some or all of these entries could change and your code would then produce unexpected results.

Using SAPBEXsetVariables API function has a similar risk.

Good luck!

- Pete

[Former Member · Accepted solution]

Pete,

To your 1st & 2nd question, I provide my users workbooks to with embedded queries that generally are NOT exposed. Therefore, they'll refresh all queries every time and one in order of last in 1st refreshed, as per design of REFRESH ALL QUERIES.

Your solution worked quite well and frankly I'm impressed by your ability. I've never really considered anyone having a better understanding of how it 'worx' and you've proven me wrong.

I came upon the project I now support (ug) as an Excel developer told about this new bex tool that integrates with xls. A Bw consultant showed me how to embed a query and from that point foward, I've honed my skill with it developing and then implementing a global system designed to consume bex queries for my client. Of course one of the first things I did was to jump into the VBE to get a feel for what was actually going on and there I found our two VERY HIDDEN pals along with the SAPBEX module. I learned early on NOT to tamper with SAPBEXqueries tab as one slip up wipes out all of your embedded queries hence, I left it alone.

However I did take from it the named ranges produced on the tab and liberally use them when needed especially regards the variables. So, rather than using GD, GE, and GG, I pulled values from the named ranges created by BEX.

Where did you get your get your 'comfort' with the SAPBEXqueries tab? And your 'leisure' with the exposed functions offered by SAPBEX.XLA?

Regardless, appreciate your help.

[Former Member · Accepted solution]

Bill,

Thank you for the gracious words.

I came to this subject the same way that you did. I had spent time automating applications in Excel and Access before starting to use BW. So, automating BW in Excel seemed like a natural thing to do.

I learned about the SAPBEXqueries sheet, probably, the same way that you did. I noticed it while in the VB Editor one day. I immediately recognized its utility because I had used hidden sheets to facilitate my own Excel tools.

At first, to understand the several tables on the hidden worksheets, I developed a little tool to track changes when I executed OLAP and Change query functions. Then I found the sapbexDebugPrint function. That function creates a complete mapping of the tables (for one query at a time). I tend to run this function manually from the VB Editor (select the SAPBEX add-in; then use Tools >> Macros; or use Run >> Run Macro).

As for the API functions available in the SAPBEX add-in, I came to understand these more slowly. The SAP documentation told me that such functions existed. The Object Browser revealed the (almost) complete list of these functions. (Click ONCE on the SAPBEX add-in object; launch the Object Browser; change the library to SAPBEX; select the xBEXapi under Classes.)

But, like most people, I had difficulty understanding exactly how to use them.

In April 2004, BW Expert published an article by Joerg Boeke that gave sample code for many of the functions. Some of the code is wrong and some of it is for earlier versions of BW; but, nevertheless, it was a very good start for me.

From there, a lot of trial and error. Aided significantly by reading postings in this forum and OSS notes.

The named ranges ... you might know something that I don't. I am familiar with how BEx uses named ranges to locate query elements (filter cells, text cells, the result table itself). But, I do not understand how you can use named ranges instead of using GD, GE, GG for the ranges on the hidden sheets. Can you explain?

- Pete

[Former Member · Accepted solution]

Pete,

There are a few things I do when first I embed queries.

1) Change CODENAMES based on a simplistic naming convention.

codename for tab SAPBEXqueries becomes SAP01

codename for tab SAPBEXfilters becomes SAP02

codename for 1st tab of queries becomes BEXD01

codename for 2nd tab of queries becomes BEXD02 (Bex data, etc.)

Not to bore you, I bring that up to lead into the following code references. Perhaps like you, I use a SELECT CASE once a query has refreshed and I'm sure to last implant a query for which workbook dates can be derived. Once that last query has refreshed and control is returned to my project, I use the following to grab the date supplied by the user (RefreshWBDates is a sub that derives a host of dates based on the value passed):

RefreshWBDates SAP01.Names(queryID & "tVARIABLE_ZGVCPPD2").RefersToRange.Cells(1, 2)

(Variables of course are dependent on the query used and I change them accordingly.)

Or for example I want to create a named range based on the headings of a query I might do the following:

[BEXD01].Names.Add "Indices", query.Offset(0, 2).Resize(1, query.Columns.Count - 2)

OK, the last example isn't quite inline with your question but until very recently, 'query' was set according to the following:

Set query = SAP01.Names(queryID).RefersToRange

In this I was again utilizing the named ranges on the SAPBEXqueries page. Little did I realize I could have just used RESULTAREA instead:) But actually I never thought to use RESULTAREA because for debugging purposes I wanted to test my code WITHOUT bex so I'd use the reference above like so:

Private Sub TrixBex()

'No purpose for the range but to satisfy the parameters

SAPBEXonRefresh "SAPBEXq0017", Range("A1")

SAPBEXonRefresh "SAPBEXq0016", Range("A1")

SAPBEXonRefresh "SAPBEXq0015", Range("A1")

end sub

'----

---

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

ProcessQuery queryID, resultArea

End Sub

'----

---

Private Sub ProcessQuery(queryID As String, resultArea As Range)

dim query as range

Set query = SAP01.Names(queryID).RefersToRange

'processing...

end sub

But now that I've 'grown', I use the following for my debugging:

Private Sub SeedBex()

CallBex "SAPBEXq0002"

CallBex "SAPBEXq0001"

End Sub

'----

---

Private Sub CallBex(queryID As String)

SAPBEXonRefresh queryID, SAP01.Names(queryID).RefersToRange

End Sub

'----

---

Sub SAPBEXonRefresh(queryID As String, query As Range)

RefreshBexQuery queryID, query

End Sub

'----

---

Sub RefreshBexQuery(queryID As String, query As Range)

'processing...

end sub

So in essence, I've totally avoided reading from the SAPBEXqueries page completely and instead pull from BEX's named ranges. Of course, I do that because I've seen a number of SAPBEXqueries pages pooched that totally destroy the linked queries. But I suppose in the long run doing it this way is probably more bullet proof since the blasted page is undocumented and could change upon any revision. Possible but doubtful the named ranges will ever radically change as you've probably deduced.

No reward points for this post though eh?

[Former Member · Accepted solution]

Hola,

Did I miss something?

I still don't see how one could get around manually using columns, GD - GJ when setting query parameters. As far as I can tell so far, there aren't named ranges for the cells and since the number of rows and the location of rows for a given query might change from instance to instance, I don't see how it would be workable.

As far as I can tell, to populate a range with the query parameter values, one pretty much has to loop through the cells in the required columns and enter valid values (with code).

-V-

[Former Member]

BTW, the naming convention I use also helps facilitate working with bex though a host of utilites I developed.

For example, I have a form in my personal.xls that helps me manage the bex worksheets. Because the codename for my queries always starts with BEXD, I can iterate though my worksheets and for example, hide them with a button click (very hide those with starting with SAP)...

[Former Member]

Thanks Peter..

I'm using SAP Front End 6.20 V2...

I too would think the API route the lesser of two evils regards refreshing through automation.

However, a few questions.

Assuming I have 2 queries to refresh, each having only 1 prompt to fill, the template range is FZ4:GS5.

Per your last post, I dis a bit of experimentation...

Last I refeshed the queries, I saved the book in it's 'refreshed' state.

I closed Excel then open the saved book along with the analyzer.

I copied FZ4:GS5 to a new workbook, hoping to capture any changes between my next refresh and the last one. I refresh the book and compare the old and new criteria range. They are virtually identical (I assumed I'd find a flag in the range that would be used to instruct SAPBEX that the queries were refreshed in this instance).

So what does 'SAPBEXsetVariables' actually do? Simply seed the 'sapbex.xla' with predetermined values?

________________________________

Lets assume a user opens the book (with queries) and the analyzer and clicks the REFRESH QUERY button from the SAPBEX toolbar and decided to REFRESH ALL QUERIES.

Once the 1st query has refreshed, code captures refresh information and hopes to use it to refreh the 2nd query WITHOUT prompting the user. Would using the 'SAPBEXsetVariables' API be applicabale in this situation?

[Former Member]

Hi Bill,

I am not certain that I understand the questions. But, let me try.

"What does 'SAPBEXsetVariables' actually do? Simply seed the 'sapbex.xla' with predetermined values?" Correct.

If all you want to do is pass a single filter value to a query, the easiest way to do this is using SAPBEXsetFilterValue. But, SAPBEXsetFilterValue has limitations:

+ you can pass only a single, INCLUDE filter

+ you cannot set a filter RANGE

+ you cannot set an EXCLUDE filter

+ you cannot set MULTIPLE filter values

+ using SAPBEXsetFilterValue removes that characteristic from the drill-down; so, if you want it in the results, you would need to also use SAPBEXsetDrillState (which has its own limitations).

In other words, SAPBEXsetFilterValue is best used for a FREE Characteristic, when you have only one filter value you want to set.

SAPBEXsetVariables has more flexibility. The only real drawback to SAPBEXsetVariables is that you MUST first create a refresh screen variable.

"I assumed I'd find a flag in the range that would be used to instruct SAPBEX that the queries were refreshed"

There is a flag that tells BW which query(-ies) have been refreshed in current workbook in current session. Look in column B on the SAPBEXqueries sheet.

What is confusing to me ... are you trying to pass same initial variables to both queries? Or, are you trying to pass RESULTS of Query1 to Query2?

If the first (use same initial variables), there are easier ways to do that.

+ Make refresh screens identical; then, give user a button to "refresh all" ... using "SAPBEXrefresh, True".

+ Use replacement path.

If the second, you have other options:

+ Use RRI and temporarily define a permanent template for embed of the jump target

+ use VB to locate the result(s) you want to use as filter(s); select that cell (or those cells) and use SAPBEXfireCommand, "SELM" (executes OLAP to "Keep Filter Value"); then use SAPBEXcopyFilterValue to copy filters from Query1 to Query2.

I might be giving you too many options. Could you be more specific about what you need to do and what are your constraints?

- Pete

[Former Member]

of course after i set the filter value, i refresh:

Run "SAPBEX.XLA!SAPBEXrefresh", False, SAP01.Names("SAPBEXq0001").RefersToRange

blah blah blah

[Former Member]

Hi Bill,

You have two choices:

+ use the function SAPBEXsetVariables before using SAPBEXrefresh I will try to find a link to discussion of this topic and post it.

+ set local query properties to "Save and Reuse Variable Values"; then, write the date(s) you want to use for refresh in the query repository worksheet.

- Pete

[Former Member]

Bill, here is the link I was looking for ...

- Pete

[Former Member]

Hi again Bill,

I just ran across your note again while looking for something else. And I realized that I did not read it carefully enough.

The problem with your use of SAPBEXsetFilerValue is that you had the wrong date format. Keep in mind that you must use the INTERNAL filter value. You can find that out using SAPBEXgetFilterValue.

In this case, the date format required is "yyyymmdd".

- Pete