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
1 Answer
-
Former MemberPosted on Jan 14, 2005 at 09:17 PMHi , 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
Add a comment