Skip to Content
author's profile photo Former Member
Former Member

Restrict the sub routine SAPBEXonRefresh

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.



Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 25, 2005 at 11:57 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.