Restrict the sub routine SAPBEXonRefresh

[Former Member]

Hi Front-End Experts,

I have attached 6 BW queries in one workbook in 6 different sheets. Now I am using sheet number 7 & 8 to get some combination of information through my VBA macro.

Meaning Q1,Q2 & Q3 data are combined as per coulum & row requirements through a macro & displayed in sheet no 7 . Same for Sheet 8(Q4,Q5 &Q6).

I want to automate my macro by saying Run ("mymacro") in the module SAPBEX 's subroutine SAPBEXonRefresh.

But I want the macro to get executed after the last query refereshed in my workbook .

But the referesh function run after each query refereshed. Which in turn runs my macro & the VBA debug error pop's up saying null or mismatch error.

Please help me out to resolve the issue.

Thanks

Mahendra

[Former Member · Accepted solution]

You can restrict the subroutine to run only after a certain query is refreshed. You know which query was just refreshed by the queryID variable that the SAPBEXonRefresh passes to you.

So, for example,

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

If queryID = "SAPBEXq0003" Then

Call myMacroForQ1Q2Q3

End If

If queryID = "SAPBEXq0006" Then

Call myMacroForQ4Q5Q6

End If

End Sub

If not sure which query is which you can do one of two things (or both).

1. for each query, right-click, choose Properties, choose the Information tab, look at the local queryID

2. enter these 2 lines of code in the SAPBEXonRefresh sub:

Application.Goto reference:=resultArea 'will select result table

Range("D2") = queryID & " refreshed " & Date & " at " & Time

Now, you need to be sure that you know in which order the queries will refresh. Generally, if you select "refresh all"

Run "SAPBEX.XLA!SAPBEXrefresh", True

Then, the queries will refresh in reverse order of how they were added to the workbook. That is, if you added Q1, then Q2, then Q3 ... and at last Q6 ... the queries will refresh as Q6, Q5, Q4 ... Q1.

Of course, you can use

Run "SAPBEX.XLA!SAPBEXrefresh", False

6 times ... then you will be sure that they refresh in the order you specify.

Hope this helps. If you need more, post again.

- Pete