Skip to Content

Start recording directly from Excel

Hello!

I was thinking of developing a script for developing scripts.. The idea is as follows;

A button in a Excel spreadsheet that when you click it, it opens a logged in-session of SAP that already has started recording.

And to the problem... I tried finding the Alt+F12 menu via the wizard but as it turns out, that is about the only button that does not have an ID. I also tried to record while doing stuff in that menu but the script does not capture that.

Anyone here got a clue if this is possible in some other way without using some kind on third-party software?

BR and thanks, Mattias.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • May 06, 2013 at 06:45 PM

    Hello.

    I have test some existing code from me with enhancement of Record and RecordFile property:

    '------------------------------Excel VBA Function---------------------------------------------

    Public Function Session_Connect() As Object
    Dim sap_applic, Connection, session, WScript, strSession_Title, strPath$
    Dim strMSG$, iMsg%, iMSG1%, iMSG2%, iMSG3%
    Dim a%, b%, c%

    '**************************************************************************
    '* set SAP session                                                        *
    '**************************************************************************

    If Not IsObject(sap_applic) Then

       Dim SapGuiAuto

       On Error Resume Next

       Set SapGuiAuto = GetObject("SAPGUI")

       On Error GoTo 0

       If SapGuiAuto Is Nothing Then

            MsgBox "Please start SAPlogon"

            Exit Function

       End If

       On Error Resume Next

       Set sap_applic = SapGuiAuto.GetScriptingEngine
    'SAPLogonNachHinten
    'ExcelWBNachVorn
       On Error GoTo 0

       If sap_applic Is Nothing Then

            MsgBox "Scripting disabled"

            Exit Function

        End If
    'SAPLogonNachHinten
    'ExcelWBNachVorn
    End If

    'ExcelWBNachVorn
    ThisWorkbook.Sheets(1).Activate
    ThisWorkbook.Sheets(1).Cells(2, 1).Select

    If Not IsObject(Connection) Then
        For a = 0 To (sap_applic.Children.Count - 1)
            For b = 0 To 6
                On Error Resume Next
                    'SAPLogonNachHinten
                    'ExcelWBNachVorn
                    ThisWorkbook.Sheets(1).Activate
                    'ThisWorkbook.Sheets(1).Cells(2, 1).Select
                    iMSG1 = MsgBox("Window: " & sap_applic.Children(0 + a).Children(0 + b).info.sessionnumber & "| System: " & sap_applic.Children(0 + a).Children(0 + b).info.systemname & " | Transaction: " & sap_applic.Children(0 + a).Children(0 + b).info.transaction & " | User: " & sap_applic.Children(0 + a).Children(0 + b).info.user & vbCr & "Do you want to use this session for Upload?", vbQuestion + vbYesNo + vbDefaultButton2, "Select correct SAP Session")
                    If iMSG1 = "6" Then
                        Set Connection = sap_applic.Children(0 + a)
                        ThisWorkbook.Sheets(1).Range("A4").Value = a
                        If Not IsObject(session) Then
                            Set session = Connection.Children(0 + b)
                            'ThisWorkbook.Sheets(1).Range("B4").Value = b
                        End If
                        Exit For
                    End If

            Next b
                If iMSG1 = "6" Then Exit For
        Next a
    End If

    If sap_applic.Children.Count = "0" Then
        MsgBox "Please logon to SAP-System where you want perform Script Recording!", vbCritical + vbMsgBoxSetForeground, "Error"
        Exit Function
    End If

    If iMSG1 = "7" Then
            ExcelWBNachVorn
            ThisWorkbook.Sheets(1).Activate
            MsgBox "You have not selected any available SAP Session. Execution aborted!", vbInformation + vbMsgBoxSetForeground, "Exit Information"
            Exit Function
    Else
            ExcelWBNachVorn
            ThisWorkbook.Sheets(1).Activate
            iMSG2 = MsgBox("You have selected System: " & session.info.systemname & " | Open transaction: " & session.info.transaction & " | Executed by user: " & session.info.user, vbInformation + vbMsgBoxSetForeground + vbYesNo + vbDefaultButton2, "System Information")
    End If

    If iMSG2 = "7" Then
        Set session = Nothing
        Set Connection = Nothing
        Set sap_applic = Nothing
        Set SapGuiAuto = Nothing
        MsgBox "You have aborted this update. Please start again.", vbExclamation + vbMsgBoxSetForeground, "Information"
        Exit Function
    End If

    If IsObject(WScript) Then
       WScript.ConnectObject session, "on"
       WScript.ConnectObject sap_applic, "on"
    End If

    '**************************************************************************
    '* get selected session GuiTitle as handle                                *
    '**************************************************************************

    strSession_Title = session.findById("wnd[0]").Text

    '**************************************************************************
    '* /get selected session GuiTitle as handle                               *
    '**************************************************************************
    strPath = "Test_IE07"
    'SAPNachVorn (strSession_Title)
    session.TestToolMode = 1
    session.SaveAsUnicode = True
    session.Recordfile = "Test_IE07"
    session.Record = 1
    session.StartTransaction "IE07"

    MsgBox "Stop Recording?", vbYesNo, "Question"
       
    session.Record = 0

    Name "C:\Users\USERNAME\AppData\Roaming\SAP\SAP GUI\Scripts\Test_IE07" As "C:\Users\USERNAME\AppData\Roaming\SAP\SAP GUI\Scripts\Test_IE07.vbs"

    Set session = Nothing
    Set Connection = Nothing
    Set sap_applic = Nothing
    Set SapGuiAuto = Nothing

    End Function

    '------------------------------Excel VBA End Function---------------------------------------------

    You need to set USERNAME as your local username on your PC. I am using SAP GUI 730. If you use an lower version SapWorkDir might be different. Normally RecordFile-property should store as VBS-file. This is somehow not working on my laptop. Therefore I need to rename at the end to vbs. We can name it as well as textfile. Or store each recorded line as Excel-row. With change-method we can adjust recored lines before it will store as file.

    Some lines in my example are comment out as I have a little bit more code in my sheets for hide SAP Logon and ensure that Excel will be in foregorund at right time. This is not available in above posting.

    If more informations are required let me know. Br, Holger

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Holger Köhn

      Regarding your comment: "...Or store each recorded line as Excel-row. With change-method we can adjust recored lines before it will store as file....."

      Can you please give me more information on how to capture each recorded line and write it to excel?

  • Apr 22, 2013 at 04:43 PM

    Hello Mattias,

    here is a solution, but with a 3rd party product:

    Sub StartScriptingViaAutoIt()

      Dim AutoIt As AutoItX3
     
      Set AutoIt = CreateObject("AutoItX3.Control")
      If IsObject(AutoIt) Then
        AutoIt.WinActivate "SAP Easy Access"
        AutoIt.Send "{ALTDOWN}{F12}{ALTUP}n"
        AutoIt.WinActivate "Record and Playback"
        AutoIt.Send "{TAB}{SPACE}"
        AutoIt.WinActivate "SAP Frontend Server"
        AutoIt.Send "{SPACE}"
      End If

    End Sub

    You find AutoItX as part of AutoIt here: http://www.autoitscript.com/site/autoit/downloads/

    Cheers

    Stefan

    Add comment
    10|10000 characters needed characters exceeded

    • Hello Stefan,

      Alright, thanks! But would such a solution require everyone that will use my macro to have this program package installed? That could be a little troubling.

      BR Mattias

  • avatar image
    Former Member
    May 10, 2013 at 12:30 PM

    I did this time ago,

    It looked like this http://lbscript.sourceforge.net/

    The excel sheet is this one http://sourceforge.net/projects/lbscript/files/Version1_Excel_Sheet/OLE_SAP.zip/download

    This was done in and excel sheet, press ALT+F11 to see the code

    Basically is a loop which reads some "instructions" and kind of parses its arguments

    Please feel free to use it in your project if needed.

    regards

    Luis

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 04, 2014 at 07:47 AM

    Hi,

    Can anyone help me out how to below code running from excel?

    If Not IsObject(application) Then
       Set SapGuiAuto  = GetObject("SAPGUI")
       Set application = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(connection) Then
       Set connection = application.Children(0)
    End If
    If Not IsObject(session) Then
       Set session    = connection.Children(0)
    End If
    If IsObject(WScript) Then
       WScript.ConnectObject session,     "on"
       WScript.ConnectObject application, "on"
    End If
    session.findById("wnd[0]").maximize
    session.findById("wnd[0]/tbar[0]/okcd").text = "/nzk97"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/usr/btn%_DD_KUNNR_%_APP_%-VALU_PUSH").press
    session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL-SLOW_I[1,0]").text = "10004638"
    session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL-SLOW_I[1,1]").text = "10054329"
    session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL-SLOW_I[1,1]").setFocus
    session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL-SLOW_I[1,1]").caretPosition = 8
    session.findById("wnd[1]/tbar[0]/btn[8]").press
    session.findById("wnd[0]/tbar[1]/btn[8]").press
    session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").select
    session.findById("wnd[1]/usr/radRB_OTHERS").setFocus
    session.findById("wnd[1]/usr/radRB_OTHERS").select
    session.findById("wnd[1]/usr/cmbG_LISTBOX").key = "08"
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[0,0]").select
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    SAP_Workbook = "Worksheet in ALVXXL01 (1)"
    EXCEL_Path  = "C:\Users\rahul.chure\Documents\SAP\"
    myWorkbook = "zk97.xlsx"
    on error resume next
    do
    err.clear
    Set xclApp = GetObject(, "Excel.Application")
    If Err.Number = 0 Then exit do
    'msgbox "Wait for Excel session"
    wscript.sleep 2000
    loop


    do
    err.clear
    Set xclwbk = xclApp.Workbooks.Item(SAP_Workbook)
    If Err.Number = 0 Then exit do
    'msgbox "Wait for SAP workbook"
    wscript.sleep 2000
    loop


    on error goto 0


    Set xclSheet = xclwbk.Worksheets(1)

    xclApp.Visible = True
    xclapp.DisplayAlerts = false

    xclapp.ActiveWorkbook.SaveAs EXCEL_Path & myWorkbook
    xclapp.ActiveWorkbook.Close


    Set xclwbk = Nothing
    Set xclsheet = Nothing
    'xclapp.Quit
    set xclapp = Nothing

    Thanks

    Add comment
    10|10000 characters needed characters exceeded