BEx 7.x: Another way to set variable values with VBA – A way without using BExApi -.

As in the very good article posted by François Gyöngyösi, I had developed a Visual Basic code to set the values of BEx variables for queries embedded in Excel workbooks under BW 3.x, too. I also had the problem that my solution doesn’t work with BW 7.x.

I could not find an adequate documentation regarding the new BexApi. So after a lot of trial and error I took another way. Now I’m using the Send Keys Method. I know that it isn’t the best way, but it works. Maybe the following code will help someone. Here are some instructions how to use the code.

1.    1. Launch the BEx Analyzer and open a BEx query with variables.

2.    2. Start the Visual Basic editor.

3.    3. Insert a new module

4.    4. Copy the code below into the module

5.    5. Adjust the code for the function “FokusAndKey”. You will find an example with comments in the code below.

6.    6. Run the procedure “RefQueryStepA”.

Option Explicit
Private Declare Function GetDesktopWindow Lib “user32” () As Long

Private Declare Function GetWindow Lib “user32” ( _
    ByVal hwnd As Long, ByVal wCmd As Long) As Long

Private Declare Function GetWindowText Lib “user32” Alias “GetWindowTextA” ( _
    ByVal hwnd As Long, _
    ByVal lpString As String, _
    ByVal cch As Long) As Long

Private Declare Function GetWindowTextLength Lib “user32” Alias “GetWindowTextLengthA” ( _
    ByVal hwnd As Long) As Long

Private Declare Function GetWindowLong Lib “user32” Alias “GetWindowLongA” ( _
    ByVal hwnd As Long, _
    ByVal wIndx As Long) As Long

Private Declare Function GetParent Lib “user32” ( _
    ByVal hwnd As Long) As Long

Private Declare Function SetForegroundWindow Lib “user32” ( _
    ByVal hwnd As Long) As Long

Private Declare Function SetTimer Lib “user32.dll” ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long

Private Declare Function KillTimer Lib “user32.dll” ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long) As Long

Private Declare Function FindWindow Lib “user32.dll” Alias “FindWindowA” ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

Private Const GC_CLASSNAMEMSEXCEL = “XLMAIN”
     
Const GW_HWNDFIRST = 0
Const GW_HWNDLAST = 1
Const GW_HWNDNEXT = 2
Const GW_HWNDPREV = 3
Const GW_OWNER = 4
Const GW_CHILD = 5
Const GW_MAX = 5

Const GWL_STYLE = (-16)

Const WS_VISIBLE = &H10000000
Const WS_BORDER = &H800000

Private ArrHandeles() As Long

Private TimerHwnd As Long

Sub RefQueryStepA()
   
    ‘I need to start a timer for the second step, because the Bex-Refresh-Variable-Window will interrupt the VB-Code.
    Call StartTimer
   
    ‘Before starting the Bex-Refresh-Variable-Window, write all windows into an array.
    If SucheHandles = True Then
        ‘Run Bex-Refresh-Variable-Window
        If RunRefVarWin = True Then
       
        End If
    End If
   
End Sub

Sub RefQueryStepB()
   
    Dim ArrHandelesA() As Long
    Dim i, j As Integer
    Dim hwnd As Long
       
    ‘Copy array into an second array, before Bex-Refresh-Variable-Window starts.
    ArrHandelesA = ArrHandeles
   
    ‘After the Bex-Refresh-Variable-Window started, write all windows into an array.
    If SucheHandles = True Then
       
        ‘Compare both arrays. If the value is similar set 0
        For i = LBound(ArrHandeles) To UBound(ArrHandeles)
            For j = LBound(ArrHandelesA) To UBound(ArrHandelesA)
                If ArrHandeles(i) = ArrHandelesA(j) Then
                    ArrHandeles(i) = 0
                End If
            Next j
        Next i
       
        ‘Only one window should be left in the array – the Bex-Refresh-Variable-Window -.
        j = 0
        For i = LBound(ArrHandeles) To UBound(ArrHandeles)
            If ArrHandeles(i) <> 0 Then
                j = j + 1
                hwnd = ArrHandeles(i)
            End If
        Next i
       
        If j = 1 Then
            ‘If only one window is left in the array, the EnterKey-Function will starts
            If FokusAndKey(hwnd) = True Then
                ‘stop timer
                Call StopTimer
            End If
        End If
       
    End If
   
End Sub

Function SucheHandles() As Boolean

    Dim hwnd, par, sty, lentit As Long
   
    Dim i As Integer
   
    ‘get desktop handle number.
    hwnd = GetDesktopWindow()
   
    ‘get aktive child window from desktop
    hwnd = GetWindow(hwnd, GW_CHILD)
   
    i = 0
    Do While hwnd <> 0
        ‘select next window
        hwnd = GetWindow(hwnd, GW_HWNDNEXT)
       
        ‘get parent from selected window
        par = GetParent(hwnd)
       
        ‘get style from selected window
        sty = GetWindowLong(hwnd, GWL_STYLE)
       
        ‘get length of title from selected window
        lentit = GetWindowTextLength(hwnd) + 1
       
        ‘If the window has no parent, it could be the Bex-Refresh-Variable-Window
        If par = 0 Then
            ‘If the window has a title, it could be the Bex-Refresh-Variable-Window
            If lentit > 0 Then
                ‘If the window is visible and has a border, it could be the Bex-Refresh-Variable-Window
                If (CBool(sty And WS_VISIBLE) = True And CBool(sty And WS_BORDER) = True) Then
                   
                    ‘write handle number from window into an array
                    ReDim Preserve ArrHandeles(i)
                    ArrHandeles(i) = hwnd
                   
                    i = i + 1
                   
                End If
            End If
        End If
       
    Loop
   
    SucheHandles = True

End Function

Function RunRefVarWin() As Boolean
    ‘Run Bex-Refresh-Variable-Window
   
    Dim SAPpAddin As Object
    Dim lCMD As String
   
    Set SAPpAddin = CreateObject(“com.sap.bi.et.analyzer.addin.BExConnect”)
   
    lCMD = SAPpAddin.ExcelInterface.cCMDRefreshVariables
   
    Call SAPpAddin.ExcelInterface.ProcessBExMenuCommand(lCMD)
   
    RunRefVarWin = True

End Function

Public Sub StartTimer()
    ‘Run timer in an intervall of 500 milliseconds.
    ‘The timer will start the second step, because the Bex-Refresh-Variable-Window interrupts the VB-Code
       
    TimerHwnd = FindWindow(GC_CLASSNAMEMSEXCEL, Application.Caption)
    SetTimer TimerHwnd, 0&, 500&, AddressOf RefQueryStepB
End Sub

Public Sub StopTimer()
    ‘Stop timer
   
    KillTimer TimerHwnd, 0&
End Sub

Function FokusAndKey(hwnd As Long) As Boolean
   
    ‘This function will set the focus to the Bex-Refresh-Variable-Window and send keys to the window.
   
    Dim i As Integer
   
    ‘Now you have to count how much tab-key’s you have to enter to reach the variable-field.
   
    ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    ‘XXXXXXX from here you have to adjust the code to your Bex-Refresh-Variable-Window XXXXXXXXX
    ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   
    ‘In my case I need 5 tab-key’s to reach the first variable.
    For i = 1 To 5
        SetForegroundWindow hwnd
        SendKeys “{TAB}”
    Next i
       
    ‘enter the value of the first valiable
    SetForegroundWindow hwnd
    SendKeys “001.2012 – 012.2012”
   
    ‘To reach the second variable I need 2 tab-key’s
    For i = 1 To 2
        SetForegroundWindow hwnd
        SendKeys “{TAB}”
    Next i
   
    ‘enter the value of the second valiable
    SetForegroundWindow hwnd
    SendKeys “001.2013 – 008.2013”
   
   
    ‘Enter again tab-key’s to reach the OK-Button from the Bex-Refresh-Variable-Window
    For i = 1 To 2
        SetForegroundWindow hwnd
        SendKeys “{TAB}”
    Next i
   
    ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    ‘XXXXXXX till here you have to adjust the code to your Bex-Refresh-Variable-Window XXXXXXXXX
    ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
       
    ‘Send Enter-key to start the Refresh
    SetForegroundWindow hwnd
    SendKeys “{ENTER}”
   
   
    FokusAndKey =True
   
End Function

This code has been tested in Excel 2010 and BEx 7.2. Please note that I will not take the responsibility for issues which arise resulting from its use.