通过VBA将SAP提取到EXCEL [英] SAP Extract into EXCEL via VBA

问题描述

我试图通过我记录的SAP脚本将SAP事务提取到.txt文档中，最后将其提取到Excel工作表中.

I am trying to extract a SAP transaction into a .txt document through a SAP script that I recorded which finally then gets extracted into my excel sheet.

当我在SAP中运行脚本时，它可以通过将值复制到我的.txt文档中来工作.所以看来我的问题是我的Excel宏无法激活我的SAP脚本.

When I run the script in SAP then it works by copying the values into my .txt document. So it seems like my problem is my macro in excel not activating my SAP script.

当我运行VBA宏执行相同操作时，它会显示

When I run my VBA macro to do the same, it says

运行时错误'7':内存不足

"Run-time error '7': out of memory

，并且不会将任何内容复制到我的.txt文档中.

and does not copy anything into my .txt document.

我希望有人可以告诉我为什么?

I hope someone can tell me why?

Public SapGuiAuto, WScript, msgcol
Public objGui  As GuiApplication
Public objConn As GuiConnection
Public objSess As GuiSession
Public objSBar As GuiStatusbar
Public objSheet As Worksheet
Dim W_System
Const fpath = "S:\Supply\WChristian\SAP GUI"
Const ffilename = "script2.txt"


Sub OpenCSVFile()
'
' Load the CSV extract
'

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;S:\Supply\WChristian\SAP GUI\script2.txt", Destination:=Range( _
        "$A$4:$I$24"))
        .Name = "mb52"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    End With
End Sub

Sub DeleteAll()
'
' DeleteAll Macro

    On Error Resume Next
    Cells.Select
    Selection.QueryTable.Delete
    Selection.ClearContents
    Range("A1").Select
End Sub

Function Attach_Session() As Boolean
Dim il, it
Dim W_conn, W_Sess

If W_System = "" Then
   Attach_Session = False
   Exit Function
End If

If Not objSess Is Nothing Then
    If objSess.Info.SystemName & objSess.Info.Client = W_System Then
        Attach_Session = True
        Exit Function
    End If
End If

If objGui Is Nothing Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set objGui = SapGuiAuto.GetScriptingEngine
End If

For il = 0 To objGui.Children.Count - 1
    Set W_conn = objGui.Children(il + 0)
    For it = 0 To W_conn.Children.Count - 1
        Set W_Sess = W_conn.Children(it + 0)
        If W_Sess.Info.SystemName & W_Sess.Info.Client = W_System Then
            Set objConn = objGui.Children(il + 0)
            Set objSess = objConn.Children(it + 0)
            Exit For
        End If
    Next
Next

If objSess Is Nothing Then
   MsgBox "No active session to system " + W_System + ", or scripting is not enabled.", vbCritical + vbOKOnly
   Attach_Session = False
   Exit Function
End If

If IsObject(WScript) Then
   WScript.ConnectObject objSess, "on"
   WScript.ConnectObject objGui, "on"
End If

Set objSBar = objSess.findById("wnd[0]/sbar")
objSess.findById("wnd[0]").maximize
Attach_Session = True

End Function

Public Sub RunGUIScript()

Dim W_Ret As Boolean

' Connect to SAP
W_Ret = Attach_Session
If Not W_Ret Then
    Exit Sub
End If

On Error GoTo myerr

ojbSess.findById("wnd[0]").ResizeWorkingPane 174, 29, False
ojbSess.findById("wnd[0]/tbar[0]/okcd").Text = "mb52"
ojbSess.findById("wnd[0]").sendVKey 0
ojbSess.findById("wnd[0]/usr/ctxtWERKS-LOW").Text = "DO"
ojbSess.findById("wnd[0]/usr/ctxtLGORT-LOW").Text = "01"
ojbSess.findById("wnd[0]/usr/ctxtMATKLA-LOW").Text = "2"
ojbSess.findById("wnd[0]/usr/ctxtMATKLA-LOW").SetFocus
ojbSess.findById("wnd[0]/usr/ctxtMATKLA-LOW").caretPosition = 3
ojbSess.findById("wnd[0]").sendVKey 8
ojbSess.findById("wnd[0]/tbar[1]/btn[45]").press
ojbSess.findById("wnd[1]/tbar[0]/btn[0]").press
ojbSess.findById("wnd[1]/usr/ctxtDY_PATH").Text = "S:\Supply\WChristian\SAP GUI\"
ojbSess.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "script2.txt"
ojbSess.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 11
ojbSess.findById("wnd[1]/tbar[0]/btn[11]").press

Exit Sub

myerr:
   MsgBox "Error occured while retrieving data", vbCritical + vbOKOnly

End Sub

Sub StartExtract()
    ' Set the sid and client to connect to
    W_System = "DCG210"
    ' Run the GUI script
    RunGUIScript
    ' End the GUI session
    ' Switch to the worksheet where the data is loaded to
    Sheets("Extract").Select
    ' Delete the entire sheet before loading the file
    DeleteAll
    ' Load the CSV file
    OpenCSVFile

    ' Update the time and date on the control worksheet
    Sheets("Control").Select
    Cells(2, 2).Value = Now()

End Sub

推荐答案

正如我向@Christian承诺的那样，我将在此处粘贴与SAP连接的方法.它一直在工作.我基于所有来自SAP的大量报告的宏:)

As I promised @Christian I'm pasting here my approach to connect with SAP. It was always working. I based on that all of my macros for tons of reports from SAP :)

Sub SapConn()

Dim Appl As Object
Dim Connection As Object
Dim session As Object
Dim WshShell As Object
Dim SapGui As Object

'Of course change for your file directory
Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", 4
Set WshShell = CreateObject("WScript.Shell")

Do Until WshShell.AppActivate("SAP Logon ")
    Application.Wait Now + TimeValue("0:00:01")
Loop

Set WshShell = Nothing

Set SapGui = GetObject("SAPGUI")
Set Appl = SapGui.GetScriptingEngine
Set Connection = Appl.Openconnection("01. PRD - ERP Production", True) 'here paste your name of module you are trying to connect/enter
Set session = Connection.Children(0)

'if You need to pass username and password - not connect automatically
'session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "900"
'session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "user"
'session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "password"
'session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"

If session.Children.Count > 1 Then

    answer = MsgBox("You've got opened SAP already, please leave and try again", vbOKOnly, "Opened SAP")

    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT3").Select
    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT3").SetFocus
    session.findById("wnd[1]/tbar[0]/btn[0]").press

    Exit Sub

End If

session.findById("wnd[0]").maximize
session.findById("wnd[0]").sendVKey 0 'ENTER
'and there goes your code in SAP

希望它会对您有所帮助:)

Hope it will help You :)

这篇关于通过VBA将SAP提取到EXCEL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！