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

Run a VB Subroutine BEFORE results are refreshed

Hello experts,

We have a workbook that requires additional results beneath the Bex results. We have accomplished this using VB script to compute additional figures and place them beneath the Bex results.

However, to avoid the error "SAPBex: Not enough cells to diplay result", we need to find a way to clear these extra values BEFORE the query refresh takes place. The SAPBEXonRefresh Sub is only executed AFTER the query refresh.

Using the SAPBEXpauseOn/Off we can find and remove our extra values before refresh and avoid the "Not Enough Cells" error. This will require a new Sub. But since I am not too familiar with VB, where should I add the code for the Sub and the "Run" statement to execute the Sub so that it will run BEFORE the query refresh takes place? SAPBEX module? Module2? worksheet? other?

Here is a sample of the code I want to execute before the results are refreshed:

****************************

Sub ClearExtraVals()

Run "SAPBEX.XLA!SAPBEXPauseOn" 'stop BW from refreshing

'Check to see if the extra results already exist and remove them if found

Set valsPresent = Rows.Find(What:="Statistical", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False)

If Not valsPresent Is Nothing Then

valsPresent.Select

ValRow1 = Selection.Row

Rows(ValRow1).Select

Selection.Clear

Rows(ValRow1 - 1).Select

Selection.Clear

End If

Run "SAPBEX.XLA!SAPBEXPauseOff"

End Sub

*****************************

Thanks for your help,

Adam

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jan 14, 2005 at 09:17 PM

    Hi , Adam

    There is no "before Refresh" event in Excel. Or in BEx, for that matter.

    What I have done is:

    1. provide a user interface, like a CommandButton, that they should click when they want to refresh the query

    2. the code behind the user interface would first call the routine you show; then,

    3. you would use Run "SAPBEX.XLA!SAPBEXrefresh", True

    (if you want to refresh every query in that workbook")

    I have a further suggestion. Use the SAPBEX module to create a named range for the query results table. It could be as simple as this:

    Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

    resultArea.Name = "QueryResults"

    End Sub

    If you do this, then your Sub ClearExtraVals()would be more robust. I would suggest the following:

    Sub ClearExtraVals()

    'go to worksheet containing query results

    Application.Goto reference:=Range("QueryResults")

    'find the last row in query results

    Set resultArea = Range("QueryResults")

    numCells = resultArea.Cells.Count

    lastQrow = resultArea.Cells(numCells).Row

    'find last row used on this Worksheet

    veryLastRow = ActiveSheet.Cells.SpecialCells(xlLastRow).Row

    'clear rows below query results

    Rows(lastQrow + 1 & ":" & veryLastRow).Clear

    End Sub

    - 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.