Skip to Content

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.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Kubiszyn

    Hi Dominik

    Using: BEx 7.x

    As part of my BEx / VBA automation project I been using a Filter Object linked to a Grid to update my query by overwriting the Cell Value using VBA, but this is limited to single Variable inputs ie. you cannot use a Range like 39.2015 – 40.2015 as the Filter does not process this like the Variables Screen.  So once the Report is setup I have only been able to change the Calendar Week to a single Week when automating.

    I have recently tried the Button linking and the Range with SAPBEXsetVariables with limited / no success (not sure if this is due to a recent HANNAH upgrade). 


    Anyhow, your Code worked fine once the SendKeys routine was adapted for my Variables Screen – a great little workaround.  No developer ever likes using SendKeys, but for my project where the entire process is silent, login, refresh, email, close, the example Code posted is a useful workaround.


    Thanks, Mark Kubiszyn

    (0) 
  2. Mark Kubiszyn

    By the way I found that I needed to pass semi-colon delimited lists of Materials that exceeded the maximum String Length that you can pass using SendKeys, therefore as a workaround I have added in an enhancement to Paste data directly from the Clipboard after Copying it from a Named Range (or Defined Name) in Excel using VBA

    1. Add a reference to: Microsoft Forms 2.0 Object Library (Important!)

    Add these variables to the top of your Code Module

    Private objData As DataObject

    Private strClipboard As String

    2. Add the following Code at the beginning of the FocusAndEnterVariables() Function to pick up a Named Range called Materials (which has 3,500 Material Codes formatted like this 56510;56615 etc…)

    strClipboard = Range(“Materials”).Value

    Set objData = New DataObject

    objData.SetText strClipboard

    objData.PutInClipboard

    3. Add the following Code in place of the normal SendKeys to Paste the Clipboard contents into the Variable Screen Input Box

    SetForegroundWindow hwnd

    SendKeys (“^v”)

    Now you can Paste as many characters as is possible in the Variables Screen Input Box…

    (0) 
  3. Ryan Lee

    Thanks for the helpful post.

     

    Is there any way to put delays between sendkeys?

    CPU isn’t following the speed of the command it seems..

     

    Thanks,

    (0) 

Leave a Reply