SAP Extract into EXCEL via VBA out of memory

Question

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.

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.

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

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

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

I was struggling with SAP connection some time ago, I don't have much time to dig into your code, but if nobody post answer I can post my approach mixed with your code (I'm not using SapGuiAuto.GetScriptingEngine) — Teamothy
Can you explain what means "run the script in SAP"? (the VBA script? what means then "in SAP"?) — Sandra Rossi
@SandraRossi sorry I meant that when I run my SAP Script in SAP then it works fine with extracting the data to the document. So I mean it seems that my code is doing it wrong when it needs to activate my SAP script. — Christian
I still don't understand. But let's forget about this "SAP script" which you refer to but don't provide the code (shouldn't you post it, as it's probably a conversion error?). In your VBA macro (what I referred as "VBA script"), can you tell us in which subroutine it fails, by using the debugger. NB: it's difficult to help because one can't reproduce your case. — Sandra Rossi

Teamothy Teamothy · Accepted Answer · 2019-10-11T21:37:52

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 :)

SAP Extract into EXCEL via VBA out of memory

1 Answers