cancel
Showing results for 
Search instead for 
Did you mean: 

VBA and Bex 7- Event to use in VB code BEFORE Query Refresh ?

former_member211907
Contributor
0 Kudos

Gurus.. is there a Bex Event that I can use to 'intercept' a Refresh Query command to Bex and run some VBA first ? I'm trying to find a way to solve the analysis grid overlap problem when two queries are provided on one Excel worksheet, and navigation takes place.

I've been able to figure out how to bring the results areas together (by deleting blank rows between them), but I'd like to be able to add a chunk of blank rows between analysis grids before the queries actually refresh.

View Entire Topic
christopherdcosta
Participant
0 Kudos

You can't do this before the refresh, but you can do it in the Callback macro - that you can find in the developer section of Excel.

What you will need to do is create three worksheets in your workbook. In the first two worksheets you will have your query results for navigation on the third worksheet you will put Excel references to your cells that are in the results. After any of the two queries are refreshed the callback executes allowing you to reformat the third results page any way you like.

Edited by: Christopher D'Costa on Jul 6, 2011 11:10 AM

former_member211907
Contributor
0 Kudos

Chris-

Agreed, that was our backup plan. In any case, I figured out a different approach. I've written a VB that essentially 'hides' unused rows, and I put the analysis grids very, very far apart in the workbook so they cannot overwrite. It's ugly, but it works.

I miss the native excel handling in Bex 3.x

marco_simon2
Participant
0 Kudos

Hi Charlie,

if you're interested:

I've found a way to move the areas after the refresh - using the callback event.

The logic is generally:

- Check where analysis-grid for DP1 ends

- Leave some empty lines

- Move the offset of DP2 here.

- Repeat the same for further DP's.

There's only one hatch: I've got some trouble to reactivate the BEx-context-menu after this momvement.

So far I found only the workaround "do another refresh" to solve this issue; that's ok but not 'beautiful'.

Let me know if you need further input.

Maybe you've got an idea for my issue:

I'm looking for a way to execute the a routine only once after refresh - even if there are multiple queries in a workbook.

The problem is that I do not know the order of the query-refresh or any flag that I could use to identifiy the "last query to be processed in this refresh-run".

Kind regards,

Marco

former_member211907
Contributor
0 Kudos

Marco-

I actually figured that one out... create a global counter variable in VBA and a global 'limit' variable. Set the global limit variable with the number of bex objects in the workbook, then track when each Bex object is touched in VBA. Then execute the final setep when the global counter and the global limit are equal. Then reset the global counter.