К сожалению, это тоже не решение проблемы.
Данный код просто вызывает рабочие книги, а необходимо еще передавать в них параметры: значения переменных, фильтров, текущее значение всех признаков.

Functions to Automate and Enhance Your BW Reports in Excel

The following examples illustrate some of the functionality supported by the BEx API. See March 2004 BW Expert (page 10) for the complete list of functions and their corresponding numbers. Note that this is a partial list of the functions and some of the code offered below is used to combine features and build new functionality.

Functions 1 and 2 can be combined along with the following code to establish a connection with the server:

Public Function LogonToYourBW()
' create Logon & RFC-Handle
logonToBW = False ‘ clear connect Flag the flag can be used to find out if connected or not
'load SAP functionality by opening BEX file ( use your installation path)
Workbooks.Open (“c:\sappc\bw\sapbex.xla”)
With Run("sapbex.xla!sapbexGetConnection") ‘ call the connection
' Set the params for Auto logon
.client = “YOUR CLIENT NO”
.user = “YOUR BW USER”
.Password = “YOUR BW PWD” ‘ I recommend to create a dummy reporting user for this task
.Language = “YOUR LANGUAGE YOU LIKE TO USE FOR QUERY DISPLAY ( i.E. “EN”)”
.SystemNumber = “YOUR SYSTEM NO” ‘
.ApplicationServer = “YOUR SERVER Name OR IP-ADDRESS”
.UseSAPLOgonIni = FALSE 'important for automatic connection
.logon 0, True ‘ This will provide a dialog to appear
If .IsConnected <> 1 Then
.logon 0, False
If .IsConnected <> 1 Then Exit Function
Else
Set g_oFunction = CreateObject("SAP.Functions")
Set g_oFunction.Connection = g_oConnection
End If
End With
Run "sapbex.xla!sapbexinitConnection" ‘ this will enable the connection you just created
logonToBW = True
End Function

Function 7 can be used to refresh queries in an active workbook. To refresh all queries, set the first parameter to True and use the following code:

If Run("sapbex.xla!SAPBEXrefresh", True) = 0 Then
Else
MsgBox " Error in Refresh"

To refresh a single query, reference a valid cell in the query (navigation or filter area or result area)

‘Set the values for Variables
Dim rngVar as Range
Set rngVar = YOUR_VARIABLE_SHEET.Range(“A2:H3”)
Run "SAPBEXsetVariables", rngVar
If Run("SAPBEX.xla!SAPBEXrefresh", False, ActiveSheet.Range("C7")) = 0 Then
Else
MsgBox "Error in Refresh”
End If

Function 8 and 9 allow you to change the drill down status in a report, either across or down, to hide certain areas while providing some basic functionality to users. The code below is to create a button for users to drill down or across (horizontal or vertical). Note the following values for myDrillState:
myDrillState = 0 No drilldown”
myDrillState = 1 Drilldown is vertical
myDrillState = 2 Drilldown is horizontal

Coding for user button:

Sub btn_Customer_Click()
Dim myDrillState As Integer
Dim myFilterCell As Range
‘Set your filter cell
Set myFilterCell = ActiveSheet.Range(“C13”)
‘Now get the actual status

If Run( "SAPBEX.XLA!SAPBEXgetDrillState", myDrillState, myFilterCell) = 0 then
myState = Run("SAPBEX.XLA!SAPBEXgetDrillState_currentState")
‘ Depending on found ststus set the opposite ( toggle) state
If myState = 0 Then
If Run( "SAPBEX.XLA!SAPBEXsetDrillState", 1, myFilterCell) = 0 Then
Else
MsgBox “Error in Drill”
End If
Else
If Run ("SAPBEX.XLA!SAPBEXsetDrillState", 0, myFilterCell) = 0 Then
Else
MsgBox “Error in Drill”
End If
End If
End If
End Sub

Function 12 allows more complex commands to be executed than those available from the context menu. The code below sorts first by text ( SOAT) , then by characteristic by key (SOAK). To set the code up, first search your trace file for the OLAP function:

XLA: 13:31:28 calling OLAP function
XLA: - function code: SOAT
XLA: - array: Sheet1!$C$30:$EI$87
XLA: - Called AllowSetForegroundWindow, Result: True(<>0 is good)
...
...
XLA: 13:32:20 calling OLAP function
XLA: - function code: SOAK

Use the OLAP references to drill by a specific hierarchy level (HX03), in the case Level 3 (HX06 would be Hierarchy eXpand Level 06):

Function SetBexCommand()
'Function to change the hierarchy Drill Level as Example
Dim strCommand As String
Dim myCell As Range

strCommand = "HX03"
‘ Can be any valid ( Navigation Area or Data Area Range)
‘ I am setting the command to my customer in cel D11
Set myCell = Range(“D11”)
If Run("sapbex.xla!SAPBEXfireCommand", strCommand, ActiveSheet.Range(strMyCell)) = 0 Then
Else
MsgBox “Error in Hierarchy Command”
End If
End Function

Function 14 validates a function in a particular cell (Note: It is recommend that you use this function before the SAPBEXfireCommand to avoid errors in your code)

Function SetBexCommand()
Dim strCommand As String
Dim myCell As Range
strCommand = "HX03"
Set myCell = Range(“D11”)
If Run("sapbex.xla!SAPBEXCheckCommand", strCommand, ActiveSheet.Range(strMyCell)) = 0 Then
If Run("sapbex.xla!SAPBEXFireCommand", strCommand, ActiveSheet.Range(strMyCell)) = 0 Then
Else
MsgBox “Error in Hierarchy Command”
End If
End If
End Function

Function 15 turns on text information not implemented in an active workbook.

The following values can be used:
selectGroup = blank or "*": Show all text elements
selectGroup = "C": Show only general text elements (created by, changed on,...)
selectGroup = "F": Show filter only
selectGroup = "V": Show variables only

This example uses selectGroup = "C" to insert data into a footer in Excel

Function Get_text_Elem()
Dim selectGroup As String
Dim myRange As Range
selectGroup = "C"
Set myRange = ActiveSheet.Range("C23")
If Run("SAPBEXshowTextElements", selectGroup) = 0 Then
Else
MsgBox "Error in Textelements"
End If
End Function

Function 16 provides access to the Report Report Interface (RRI) to jump directly to another query.

View as Jump target

Private Sub CommandButton1_Click()
If Run("SAPBEX.XLA!SAPBEXjump", "v", "My jump view", ActiveSheet.Range("D19")) = 0 Then
Else
MsgBox "ERROR in Jump Target"
End If
End Sub

Query Jump target via RSBBS

Private Sub CommandButton1_Click()
If Run("SAPBEX.XLA!SAPBEXjump", "r", "QURY0001", ActiveSheet.Range("D19")) = 0 Then
Else
MsgBox "ERROR in Jump Target"
End If
End Sub

Function 18 automatically saves your workbooks.

Private Sub CommandButton1_Click()
If Run("SAPBEX.XLA!SAPBEXsaveWorkbook") = 0 Then
Else
MsgBox "ERROR WB save"
End If
End Sub

Function 19 opens workbooks without the BEx toolbar.

Private Sub CommandButton1_Click()
Dim strWBID As String
Dim strTmp As String

strWBID = "2A024RULBDK33U5BNZH60433U"
strTmp = Run("SAPBEX.XLA!SAPBEXreadWorkbook", strWBID)
If strTmp = "" Then
MsgBox "ERROR WB Read"
End If
End Sub

Function 20 translates the human friendly name to the GUID being stored on the BW server.

Private Sub CommandButton1_Click()
Dim strWBName As String
Dim strWBID As String
strWBName = "Testquery Jump"

strWBID = "2A024RULBDK33U5BNZH60433U"
strWBID = Run("SAPBEX.XLA!SAPBEXreadWorkbook", strWBName)
If strWBID = "" Then
MsgBox "ERROR get WBID"
End If
End Sub

Function 21 can be used to dynamically insert queries into an Excel workbook. There are two ways of using the function:
1. Boolean Value TRUE for the second parameter of that function, which will insert the query where you defined the active cell.
2. Leave the second function value blank, which open a new Workbook for a particular query.

Private Sub CommandButton1_Click()
Dim strQueryID As String
Dim strTmp As String

strQueryID = "85RFOO9I0FXTZOK5GP6X2UVNA"
ActiveWorkbook.Sheets("Sheet1").Range("A10").Select

strTmp = Run("SAPBEX.XLA!SAPBEXEmbedQuery", strQueryID)
If strTmp = "" Then
MsgBox "ERROR Insert Query"
End If
End Sub

Combine Functions 4, 5, and 10 to create multiple function calls. Function 3, 4, and 10 can be combined to first stop the automatism in BEX, then collect all function calls, and execute the functions in batch:

Run "sapbex.xla!SAPBEXpauseOn()" ‘This will stop all automatic refreshes by SAP
‘Set the values for Variable 1
If Run("SAPBEX.xla! SAPBEXSetFilterValue",YOUR_1_VARIABLE_VALUE,””, Sheets("Query_A").Range("B7")) = 0 Then
Else
MsgBox "Function failed"
End If
‘Set the values for Variable 2
If Run("SAPBEX.xla! SAPBEXSetFilterValue",YOUR_2_VARIABLE_VALUE,””, Sheets("Query_A").Range("B5")) = 0 Then
Else
MsgBox "Function failed"
End If
Run "sapbex.xla!SAPBEXpauseOn()"‘This will turn on all automatic refreshes by SAP and execute all functions since PauseOn

Попробуйте так:
This VBA-macro connects to BW in silent mode and executes two workbooks sequentally

Public Sub LogonAndOpenWB()
Dim b As Boolean
Dim g_oConnection As Object

Dim SAPLogonControl As Object
Set SAPLogonControl = CreateObject("SAP.Logoncontrol.1")
Set g_oConnection = SAPLogonControl.NewConnection
g_oConnection.System = "BWD"
g_oConnection.ApplicationServer = "appserver"
g_oConnection.SystemNumber = 10
g_oConnection.User = "i028159"
g_oConnection.Password = "secret"
g_oConnection.Client = "100"
g_oConnection.Language = "R"
If g_oConnection.LogOn(0, True) = False Then
MsgBox "BW connection failed"
Else
MsgBox "BW connection OK!"
Set p_connectionObj = g_oConnection
b = Run("BExAnalyzer.xla!initConnection")
b = Run("BExAnalyzer.xla!runWorkbook", "93S77X1V97S07QO1YBX5US9FX")
b = Run("BExAnalyzer.xla!runWorkbook", "DAE8KPSJSD4MTJ4VF9Y9SY2EW")
End If
End Sub

Взято отсюда.

Не совсем понятна сама идея.
Можно по подробнее? Или пример кода для реализации?

Да, у меня немного другая задача. С копированием переменных у меня проблем нет, необходимо скопировать значения признаков для выделенной ячейки. По строкам у меня динамическая иерархия следующего вида:

Если текущая ячейка находится на уровне "Месяц", то необходимо определить кроме месяца значения для Признака 1 и Признака 2.
Если на уровне Признак 2, то значение для Признака 1 и Признака 2.
Если на уровне Признак 1, то только значение для Признака 1.