Startseite Beiträge getaggt mit "ActiveWorkbook"

Schlagwort: ActiveWorkbook

[SAP] SAP GUI Scripting per VBA

Voraussetzungen

  • System ab SAP NW 6.2
    • Präsentationsserver: SAP Menu (SAP Easy Access) -> Button „Lokales Layout anpassen“ -> Optionen… -> Barrierefreiheit und Scripting -> Skiptunterstützung aktivieren, die anderen Haken deaktivieren
    • Applikationsserver: Profilparameter: RZ11 -> Parameter sapgui/user_scripting = TRUE (in Großbuchstaben!) -> gilt nur ab dem nächsten Login und bis Serverneustart
      • weitere Profilparameter:
        • sapgui/user_scripting must be TRUE to use scripting functions
        • sapgui/user_scripting_disable_recording if it is TRUE then it is only possible to run scripts
        • sapgui/user_scripting_force_notification if it is TRUE a notification is always displayed at the frontend
        • sapgui/user_scripting_per_user if it is TRUE you need S_SCR authority to use scripting
        • sapgui/user_scripting_set_readonly if it is TRUE it is not allowed to change the state of SAP GUI

Skriptaufzeichnung und Hilfe

  • SAP Menu (SAP Easy Access) -> Button „Lokales Layout anpassen“ -> Skript-Aufzeichnung und -Playback
  • SAP Menu (SAP Easy Access) -> Button „Lokales Layout anpassen“ -> Hilfe zu SAP GUI Scripting

OCX-Bibliothek für VBA

  • VBA-Import über: Extras -> Verweise … -> Durchsuchen …
  • C:\Program Files\SAP\FrontEnd\SAPgui\sapfewse.ocx
    oder
    C:\Apps32\SAP\FrontEnd\SAPgui\sapfewse.ocx
  • Aktivieren: SAP GUI Scripting API

Windows Scripting Host (x64)

  • C:\Windows\SysWOW64\wscript.exe
  • Ausführen von vbs-Scripten: C:\Windows\SysWOW64\wscript.exe c:\test.vbs

Beispiel 1 (Alle Connections, Sessions, GUI-Elemente auflisten)

Option Explicit

Sub SAP()
    Dim oSapGui As Object
    ' Extras -> Verweise ... -> Durchsuchen ...
    ' C:\Program Files\SAP\FrontEnd\SAPgui\sapfewse.ocx
    ' Aktivieren: SAP GUI Scripting API
    Dim oApp As SAPFEWSELib.GuiApplication
    Dim oConn As SAPFEWSELib.GuiConnection
    Dim oSession As SAPFEWSELib.GuiSession
    Dim oSessionInfo As SAPFEWSELib.GuiSessionInfo
    Dim oWindow As SAPFEWSELib.GuiMainWindow
    Dim oComponent As SAPFEWSELib.GuiComponent
    Dim oSubComponent As SAPFEWSELib.GuiComponent
    
    Debug.Print "----------"
    
    ' Ref auf SAPGUI
    Set oSapGui = GetObject("SAPGUI")
    If IsObject(oSapGui) Then
        ' Ref auf ScriptingEngine
        Set oApp = oSapGui.GetScriptingEngine
        If IsObject(oApp) Then
            
            Debug.Print oApp.ID
            Debug.Print oApp.Name
            
            ' Liste Connections
            For Each oConn In oApp.Children
                Debug.Print "|--" & oConn.ConnectionString
                Debug.Print "|  " & oConn.Description
                Debug.Print "|  " & oConn.ID
                Debug.Print "|  " & oConn.Name
                
                ' Sessions sind nur verfügbar, wenn RZ11 Parameter sapgui/user_scripting = TRUE
                ' Liste Sessions
                For Each oSession In oConn.Children
                    Debug.Print "  |--" & oSession.ID
                    Debug.Print "  |  " & oSession.Busy
                    
                    ' Ref auf Session Info
                    Set oSessionInfo = oSession.Info
                    
                    If IsObject(oSessionInfo) Then
                        Debug.Print "    |--" & oSessionInfo.SystemSessionId
                        Debug.Print "    |  " & oSessionInfo.ApplicationServer
                        Debug.Print "    |  " & oSessionInfo.SystemName
                        Debug.Print "    |  " & oSessionInfo.Client
                        Debug.Print "    |  " & oSessionInfo.Transaction
                        Debug.Print "    |  " & oSessionInfo.User
                    End If
                    
                    ' Ref auf Fenster
                    For Each oWindow In oSession.Children
                        Debug.Print "      |--" & oWindow.ID
                        Debug.Print "      |  " & oWindow.Text
                        Debug.Print "      |  " & oWindow.Left
                        Debug.Print "      |  " & oWindow.Top
                        Debug.Print "      |  " & oWindow.Width
                        Debug.Print "      |  " & oWindow.Height
                        
                        ' Liste mit Komponenten im Fenster
                        For Each oComponent In oWindow.Children
                            Debug.Print "        |--" & oComponent.ID
                            Debug.Print "        |  " & oComponent.Name
                            Debug.Print "        |  " & oComponent.Type
                            Debug.Print "        |  " & oComponent.ContainerType
                            
                            ' Liste mit Unterkomponenten (können wiederum auch Unterkomponenten haben -> besser rekursiv aufrufen)
                            For Each oSubComponent In oComponent.Children
                                Debug.Print "          |--" & oSubComponent.ID
                                Debug.Print "          |  " & oSubComponent.Name
                                Debug.Print "          |  " & oSubComponent.Type
                                Debug.Print "          |  " & oSubComponent.ContainerType
                            Next
                        Next
                    Next
                Next
            Next
        End If
    End If
    
    Set oSubComponent = Nothing
    Set oComponent = Nothing
    Set oWindow = Nothing
    Set oSessionInfo = Nothing
    Set oSession = Nothing
    Set oConn = Nothing
    Set oApp = Nothing
    Set oSapGui = Nothing
End Sub

Beispiel 2 (Transaktion SE16 starten und Inhalt des ALV-Grids in Excel kopieren)

Option Explicit

Sub SAP()
    Dim oSapGui As Object
    ' Extras -> Verweise ... -> Durchsuchen ...
    ' C:\Program Files\SAP\FrontEnd\SAPgui\sapfewse.ocx
    ' Aktivieren: SAP GUI Scripting API
    Dim oApp As SAPFEWSELib.GuiApplication
    Dim oConn As SAPFEWSELib.GuiConnection
    Dim oSession As SAPFEWSELib.GuiSession
      
    ' Ref auf SAPGUI
    Set oSapGui = GetObject("SAPGUI")
    If IsObject(oSapGui) Then
        ' Ref auf ScriptingEngine
        Set oApp = oSapGui.GetScriptingEngine
        If IsObject(oApp) Then
            ' Sind Connections vorhanden?
            If oApp.Children.Count > 0 Then
                ' 1. Connection der App
                Set oConn = oApp.Children(0)
                ' 1. Session der Connection
                Set oSession = oConn.Children(0)
                
                ' Fenster minimieren
                oSession.FindById("wnd[0]").Iconify
                
                ' SE16 starten
                oSession.StartTransaction ("SE16")
                               
                ' Selektionsbild
                ' Tabelle MARA
                oSession.FindById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").Text = "MARA"
                ' Button "Tabelleninhalt (F7)"
                oSession.FindById("wnd[0]/tbar[1]/btn[7]").Press
                ' max. 10 Einträge
                oSession.FindById("wnd[0]/usr/txtMAX_SEL").Text = "10"
                ' Button "Ausführen (F8)"
                oSession.FindById("wnd[0]/tbar[1]/btn[8]").Press
                
                ' ALV-Ansicht aktivieren
                ' Menü suchen
                Dim oMenu As GuiComponent
                Set oMenu = oSession.FindById("wnd[0]/mbar")
                
                ' Menüpunkt "Einstellungen"
                Dim oOptions As GuiComponent
                Set oOptions = oMenu.FindByName("Einstellungen", "GuiMenu")
                
                ' Menüpunkt "Benutzerparameter ..."
                Dim oUserPar As GuiComponent
                Set oUserPar = oOptions.FindByName("Benutzerparameter...", "GuiMenu")
                oUserPar.Select
                
                ' Databrowser -> Ausgabeliste -> ALV-Grid-Darstellung
                Dim oALVParam As GuiComponent
                Set oALVParam = oSession.FindById("wnd[1]/usr/tabsG_TABSTRIP/tabp0400/ssubTOOLAREA:SAPLWB_CUSTOMIZING:0400/radRSEUMOD-TBALV_GRID")
                
                ' wenn ALV-Ansicht noch nicht ausgewählt, dann anhaken
                If oALVParam.Selected = vbFalse Then
                    oALVParam.Select
                End If
                
                ' Einstellungen übernehmen
                oSession.FindById("wnd[1]/tbar[0]/btn[0]").Press
                
                Set oUserPar = Nothing
                Set oOptions = Nothing
                Set oMenu = Nothing
                
                ' ALV-Grid holen
                Dim oALV As GuiComponent
                Set oALV = oSession.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell")
                
                ' Zeilen
                Dim iRows As Integer
                iRows = oALV.RowCount() - 1
                ' Spalten
                Dim iCols As Integer
                iCols = oALV.ColumnCount() - 1
                
                ' Spaltentitel lesen
                Dim oColumns
                Set oColumns = oALV.ColumnOrder
                
                Dim c As Integer
                Dim r As Integer
                
                ' Überschriften einfügen
                For c = 0 To iCols
                    ActiveWorkbook.ActiveSheet.Cells(1, c + 1) = oColumns(c)
                Next
                
                ' Daten einfügen
                For r = 0 To iRows
                
                    ' ALV-Grid weiterscrollen, damit ein Update des Inhalts erfolgt
                    oALV.FirstVisibleRow = r
                    
                    For c = 0 To iCols
                        ActiveWorkbook.ActiveSheet.Cells(r + 2, c + 1) = oALV.GetCellValue(r, CStr(oColumns(c)))
                    Next
                Next
                
                ' Fenster maximieren
                oSession.FindById("wnd[0]").Maximize
            Else
                MsgBox "Bitte an einem SAP-System anmelden."
            End If
        End If
    End If
    
    Set oSession = Nothing
    Set oConn = Nothing
    Set oApp = Nothing
    Set oSapGui = Nothing
End Sub

Beispiel 3 (System-Logon)

Option Explicit

Sub SAP()
    Dim oSapGui As Object
       
    ' Extras -> Verweise ... -> Durchsuchen ...
    ' C:\Program Files\SAP\FrontEnd\SAPgui\sapfewse.ocx
    ' Aktivieren: SAP GUI Scripting API
    Dim oApp As SAPFEWSELib.GuiApplication
    Dim oConn As SAPFEWSELib.GuiConnection
    Dim oSession As SAPFEWSELib.GuiSession
      
    ' Ref auf SAPGUI
    Set oSapGui = GetObject("SAPGUI")
    If IsObject(oSapGui) Then
        ' Ref auf ScriptingEngine
        Set oApp = oSapGui.GetScriptingEngine
        If IsObject(oApp) Then

            ' Zu System verbinden, wobei <SYSTEM> == vollst. (!) Systemname aus SAP Logon Pad -> Verbindungen
            Set oConn = oApp.OpenConnection("<SYSTEM>")

            If IsObject(oConn) Then
                ' 1. Session der Connection
                Set oSession = oConn.Children(0)
  
                ' Username und Passwort setzen
                oSession.FindById("wnd[0]/usr/txtRSYST-BNAME").Text = "<USER>"
                oSession.FindById("wnd[0]/usr/pwdRSYST-BCODE").Text = "<PASS>"
                ' Enter-Taste
                oSession.FindById("wnd[0]").SendVKey 0
  
                ' SE16 starten
                oSession.StartTransaction ("SE16")
            End If
        End If
    End If
    
    Set oSession = Nothing
    Set oConn = Nothing
    Set oApp = Nothing
    Set oSapGui = Nothing
End Sub

Links

[MS Excel] BAPI-Zugriff aus Excel (VBA) heraus 

Option Explicit

Sub GetMATNR()
    ' Verbindung zu SAP herstellen
    Dim oSAP As Object
    Set oSAP = CreateObject("SAP.Functions")
    ' Verbindungsdaten vorbelegen
    oSAP.Connection.ApplicationServer = "1.1.1.1" ' IP des Appl-Servers (SM51->Details)
    oSAP.Connection.SystemNumber = "01"           ' Systemnummer, meißt im Namen des Appl-Servers enthalten
    oSAP.Connection.System = "XD1"                ' Entwicklungs-, Test-, Produktivsystem
    oSAP.Connection.Client = "100"                ' Mandant
    oSAP.Connection.Language = "DE"               ' Sprache "EN", "DE" ...
    
    ' RFC-Login: Logon-Fenster anzeigen
    If oSAP.Connection.Logon(0, False) = True Then
        ' BAPI_MATERIAL_GET_DETAIL abfragen
        Dim oFuBa As Object
        Set oFuBa = oSAP.Add("BAPI_MATERIAL_GET_DETAIL")
        
        ' EXPORTING
        Dim e_material As Variant
        Set e_material = oFuBa.Exports("MATERIAL")
        e_material.Value = ActiveWorkbook.ActiveSheet.Cells(1, 2) ' MATNR in B1
        
        ' IMPORTING
        Dim i_material_general_data As Variant
        Set i_material_general_data = oFuBa.Imports("MATERIAL_GENERAL_DATA")
        
        Dim i_return As Variant
        Set i_return = oFuBa.Imports("RETURN")
        
        ' Wenn Fuba-Aufruf ok
        If oFuBa.Call = True Then
            ' RETURN auswerten
            If i_return.Value("TYPE") = "E" Then
                ' Bei Fehler: Fehlermessage ausgeben in B2
                ActiveWorkbook.ActiveSheet.Cells(2, 2) = i_return.Value("MESSAGE")
            Else
                ' Wenn OK: Materialkurztext in B2
                ActiveWorkbook.ActiveSheet.Cells(2, 2) = i_material_general_data.Value("MATL_DESC")
            End If
        End If
    End If
End Sub

[MS Excel] Tabellendaten per RFC-Zugriff (RFC_READ_TABLE) aus einem SAP-System importieren 

Sub RFCReadTable()
    ' https://www.linkedin.com/pulse/connect-sap-r3-call-custom-fm-from-ms-excel-erkan-kopuz
    ' https://saplsmw.com/Import_tables_directly_into_Access_from_SAP_using_RFCs
    ' http://sapass.metro.client.jp/Sap_Active_X/UseFunctionControl.htm
    Set oSAP = CreateObject("SAP.Functions")
    oSAP.Connection.ApplicationServer = "1.1.1.1" ' IP des Appl-Servers (SM51->Details)
    oSAP.Connection.SystemNumber = "01"           ' Systemnummer, meißt im Namen des Appl-Servers enthalten
    oSAP.Connection.System = "XA1"                ' Entwicklungs-, Test-, Produktivsystem
    oSAP.Connection.Client = "100"                ' Mandant
    oSAP.Connection.Language = "DE"               ' Sprache "EN", "DE" ...
    oSAP.Connection.User = "USER1"                ' SAP-User
    'oSAP.Connection.Password = "xyz"              ' SAP-Passwort
    oSAP.Connection.UseSAPLogonIni = False
    
    ' RFC-Login, wobei
    ' Logon(0, False): Logon-Fenster anzeigen
    ' Logon(0, True): Silent logon, Passwort muss gesetzt sein
    If oSAP.Connection.Logon(0, False) = True Then
      Dim oFuBa As Object
        
      ' FuBa RFC_READ_TABLE abfragen
      Set oFuBa = oSAP.Add("RFC_READ_TABLE")
      
      ' EXPORTING
      Set e_query_table = oFuBa.Exports("QUERY_TABLE")
      Set e_delimiter = oFuBa.Exports("DELIMITER")
      Set e_rowCount = oFuBa.Exports("ROWCOUNT")
      
      e_query_table.Value = "STXH" ' Tabelle STXH
      e_delimiter.Value = ";"      ' Spalten mit ";" getrennt
      e_rowCount.Value = "100"     ' max. 100 Datensätze lesen, 0 = alle
      
      ' TABLES
      Set t_options = oFuBa.Tables("OPTIONS")
      Set t_fields = oFuBa.Tables("FIELDS")
      Set t_data = oFuBa.Tables("DATA")
      
      ' WHERE-Bedingung
      t_options.AppendRow
      t_options(1, "TEXT") = "TDOBJECT EQ 'TEXT'"
      
      ' Welche Spalten sollen gelesen werden
      t_fields.AppendRow
      t_fields(1, "FIELDNAME") = "TDOBJECT"
      t_fields.AppendRow
      t_fields(2, "FIELDNAME") = "TDNAME"
      t_fields.AppendRow
      t_fields(3, "FIELDNAME") = "TDID"
      t_fields.AppendRow
      t_fields(4, "FIELDNAME") = "TDTITLE"
      t_fields.AppendRow
      t_fields(5, "FIELDNAME") = "TDLUSER"
      
      If oFuBa.Call = True Then
        ' Schnittstellenparameter "TABLES-DATA" holen
        ' Rückgabe in Excel-Sheet einfügen
        
        Dim iRow As Integer
        iRow = 1
            
        ' Rückgabemenge beinhaltet Strings, die mit dem festgelegten Separator ";" getrennt sind
        For Each oDataLine In t_data.Rows

          ' Spalten einer Datenzeile anhand des Separators ";" aufsplitten
          Dim vFields As Variant
          vFields = Split(oDataLine(1), ";")
          
          ' Inhalt der Spalten in die Zellen schreiben
          For iCol = LBound(vFields) To UBound(vFields)
            ActiveWorkbook.Sheets(1).Cells(iRow, iCol + 1) = Trim(vFields(iCol))
          Next iCol
                
         iRow = iRow + 1
        Next
      Else
        ' Exception?
        MsgBox oFuBa.Exception
      End If

      ' Logoff
      oSAP.Connection.Logoff
    
    Else
      ' Kein Login möglich
      MsgBox "Login fehlgeschlagen."
    End If

End Sub

[MS Excel] Nutzerliste per RFC-Zugriff (TH_USER_LIST) aus einem SAP-System importieren 

Sub GetUserList()
  ' https://www.linkedin.com/pulse/connect-sap-r3-call-custom-fm-from-ms-excel-erkan-kopuz
  ' https://saplsmw.com/Import_tables_directly_into_Access_from_SAP_using_RFCs
  ' http://sapass.metro.client.jp/Sap_Active_X/UseFunctionControl.htm

  ' SAP-Objekt erzeugen
  Set oSAP = CreateObject("SAP.Functions")
  oSAP.Connection.ApplicationServer = "1.1.1.1"     ' IP des Appl-Servers (SM51->Details)
  oSAP.Connection.SystemNumber = "01"               ' Systemnummer, meißt im Namen des Appl-Servers enthalten
  oSAP.Connection.System = "DV1"                    ' Entwicklungs-, Test-, Produktivsystem
  oSAP.Connection.Client = "100"                    ' Mandant
  oSAP.Connection.Language = "DE"                   ' Sprache "EN", "DE" ...
  oSAP.Connection.User = "USER1"                    ' SAP-User
  'oSAP.Connection.Password = "xyz"                  ' SAP-Passwort
  oSAP.Connection.UseSAPLogonIni = False
    
  ' RFC-Login, wobei
  ' Logon(0, False): Logon-Fenster anzeigen
  ' Logon(0, True): Silent logon, Passwort muss gesetzt sein
  If oSAP.Connection.Logon(0, False) = True Then
    Dim oFuBa As Object
        
    ' FuBa TH_USER_LIST abfragen
    Set oFuBa = oSAP.Add("TH_USER_LIST")

    If oFuBa.Call = True Then
        
      ' Schnittstellenparameter "TABLES-USRLIST"
      Dim oUsrList As Object
      Set oUsrList = oFuBa.Tables("USRLIST")
             
      ' Userliste in Excel-Sheet einfügen         
      Dim i As Integer
      i = 1
            
      For Each User In oUsrList.Rows
               
        ActiveWorkbook.Sheets(1).Cells(i, 1) = User(2)  ' Client
        ActiveWorkbook.Sheets(1).Cells(i, 2) = User(3)  ' UserName
        ActiveWorkbook.Sheets(1).Cells(i, 3) = User(5)  ' Terminal
        ActiveWorkbook.Sheets(1).Cells(i, 4) = User(16) ' IP
               
        i = i + 1
      Next
    Else
      ' Exception?
      MsgBox oFuBa.Exception
    End If

    ' Logoff
    oSAP.Connection.Logoff
    
  Else
    ' Kein Login möglich
    MsgBox "Login fehlgeschlagen."
  End If

End Sub