cancel
Showing results for 
Search instead for 
Did you mean: 

VBA Code to Expand a Hierarchy

Former Member
0 Kudos

have a workbook in BW (3.0), and am trying to do some formatting to the results area. I want to create a macro to expand the hierarchy after getting the results of the query. I found the following code in BW Expert Online, but I an getting an error on the underlined part of the code. I am a novice at BW, and my only exposure to VBA has been in the past month trying to format the report. Any help would be greatly appreciated.

Scott

*This was inadvertently posted on the SDN Content Suggestion board. Sorry for the confusion.*

Sub test()

'

' test Macro

' Macro recorded 4/18/2005 by Scott

'

'

Range("C19").Select

Selection.Copy

Range("E14").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("A1:L1").Select

SetBexCommand

End Sub

Function SetBexCommand()

Dim strCommand As String

Dim myCell As Range

strCommand = "HX03"

Set myCell = Range("D18")

<u>If Run("sapbex.xla!SAPBEXCheckCommand", strCommand, ActiveSheet.Range(strMyCell)) = 0 Then</u>If Run("sapbex.xla!SAPBEXFireCommand", strCommand, ActiveSheet.Range(strMyCell)) = 0 Then

Else

MsgBox "Error in Hierarchy Command"

End If

End If

End Function

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Scott,

That was a great article that in BW Expert, wasn't it?

Unfortunately, the VBA in this particular example doesn't make a lot of sense. I've streamlined it a bit. See if this works better for you.

Function SetBExCommand() as Integer

Dim myCell As Range, retVal As Integer

Set myCell = ActiveSheet.Range("D18")

retVal = Run("sapbex.xla!SAPBEXcheckCommand", "HX03", myCell)

If retVal = 0 Then

'OLAP context checked OK

retVal = Run("sapbex.xla!SAPBEXfireCommand", "HX03", myCell)

Else

MsgBox "Error in Hierarchy Command", vbCritical, "OLAP Context Error"

End If

SetBExCommand = retVal

End Function

- Pete

Former Member
0 Kudos

Hi Pete,

I added the code, but I get an error message that says "The macro 'SAPBEXcheckCommand' cannot be found. Being a novice with VBA, I cannot confirm that this code is on the right sheet. I have this running on the tab that has the results area (queries are on another tab). Should it be on another sheet(SAPBEXqueries, SAPBEX filters)?

I appreciate your help on this.

Scott

Private Sub CommandButton1_Click()

Range("C19").Select

Selection.Copy

Range("E14").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("A1:L1").Select

SetBExCommand

End Sub

Function SetBExCommand() As Integer

Dim myCell As Range, retVal As Integer

Set myCell = ActiveSheet.Range("D18")

<b>retVal = Run("sapbex.xla!SAPBEXcheckCommand", "HX03", myCell)</b>

If retVal = 0 Then

'OLAP context checked OK

retVal = Run("sapbex.xla!SAPBEXfireCommand", "HX03", myCell)

Else

MsgBox "Error in Hierarchy Command", vbCritical, "OLAP Context Error"

End If

SetBExCommand = retVal

End Function

Former Member
0 Kudos

Hi Scott,

Sorry ... I overlooked your response 10 days ago.

My error. The correct name of the function is:

"SAPBEXcheckContext"

I would put the code in a general Module. It you are call it from a CommandButton on the sheet that contains the query results table, you can use the code behind the button to call a subroutine written in a general code Module. But, it SHOULD work if on the worksheet that has the query results table (once you use the correct function name ... my apologies!)

- Pete

Former Member
0 Kudos

Thanks Pete. I had given up on a solution and gone in a different direction, <u>however I have tried the code and it works</u>.

I will use this information going forward for any other reports that utilize a hierarchy. Points to you!!!!

Former Member
0 Kudos

Hi Peter,

This code allows to expand whole hierachy to level HX03.

Is it possible to expand only one branch of hierachy under the cell 'myCell' using VB code?

Sorry, problem is solved :).

(using fucntion code 'HDEX' and 'HDCO' instead of 'HX..')

Message was edited by: Andrei Lyubimov

Answers (0)