Jun 26 2019 07:21 AM
Hello Team,
I need to create a macro to download attachment from SAP for each document provided by user.
I was able to build the below code but i unable to send Enter key to save the attachment. Need help
My Code gets stuck at below line
.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").pressToolbarButton "%ATTA_EXPORT"
Dim session As Variant
Public Sub StartExtract()
StartSAPSession
ExtractDocuments
End Sub
Public Function StartSAPSession() As Variant
Set WshShell = CreateObject("WScript.Shell")
Set proc = WshShell.Exec("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe")
Dim temp
temp = Timer
Do While Timer - temp < 5
Loop
Set SapGui = GetObject("SAPGUI")
Set Appl = SapGui.GetScriptingEngine
Set Connection = Appl.Openconnection("EGP - ERP Americas Electricity Production", True)
Set session = Connection.Children(0)
session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "XXX"
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "XXX"
session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
session.findById("wnd[0]").sendVKey 0
If Not IsObject(Application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set Appl = 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
Set StartSAPSession = Connection.Children(0)
End Function
Public Function ExtractDocuments()
Dim Arr() As Variant
Dim DocNum As String
Dim Company As String
Dim FY As String
Dim AttCnt As Integer
Dim i As Long
Dim j As Long
Dim HWnd2 As Long
Dim lCurHwnd As Long
Dim sText As String * 255
' When session is Nothing then we need to create a new session
' else assume we can re-use the session
If session Is Nothing Then
Set session = StartSAPSession
End If
' Load the table as an Array, this will be faster
Arr = Range("Table1").ListObject.DataBodyRange
' Cycle through each row of the table (Arr)
For i = 1 To UBound(Arr, 1)
' Start by loading the row you will enter
DocNum = Arr(i, 1)
Company = Arr(i, 2)
FY = Arr(i, 3)
With session
.findById("wnd[0]").maximize
.StartTransaction "FB03" ' Load the transaction you are after
.findById("wnd[0]/usr/txtRF05L-BELNR").Text = DocNum
.findById("wnd[0]/usr/ctxtRF05L-BUKRS").Text = Company
.findById("wnd[0]/usr/txtRF05L-GJAHR").Text = FY
.findById("wnd[0]").sendVKey 0 ' Execute transaction
' The query runs and you select context menu and attachments
.findById("wnd[0]/titl/shellcont/shell").pressContextButton "%GOS_TOOLBOX"
.findById("wnd[0]/titl/shellcont/shell").selectContextMenuItem "%GOS_VIEW_ATTA"
' How many attachments are there? If 1 or more then save each.
AttCnt = .findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").RowCount
If AttCnt > 0 Then
For j = 0 To AttCnt - 1
.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").selectedRows = j
.findById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell").pressToolbarButton "%ATTA_EXPORT"
.send ("{ENTER}")
Next j
.findById("wnd[1]/tbar[0]/btn[0]").press ' Exit the Attachments window
End If
End With
Next i
End Function
Jun 26 2019 08:28 AM