Home

Download attachment from SAP Using VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-721983%22%20slang%3D%22en-US%22%3EDownload%20attachment%20from%20SAP%20Using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-721983%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20create%20a%20macro%20to%20download%20attachment%20from%20SAP%20for%20each%20document%20provided%20by%20user.%3C%2FP%3E%3CP%3EI%20was%20able%20to%20build%20the%20below%20code%20but%20i%20unable%20to%20send%20Enter%20key%20to%20save%20the%20attachment.%20Need%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20Code%20gets%20stuck%20at%20below%20line%3C%2FP%3E%3CP%3E.findById(%22wnd%5B1%5D%2Fusr%2FcntlCONTAINER_0100%2Fshellcont%2Fshell%22).pressToolbarButton%20%22%25ATTA_EXPORT%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20session%20As%20Variant%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EPublic%20Sub%20StartExtract()%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EStartSAPSession%3CBR%20%2F%3EExtractDocuments%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPublic%20Function%20StartSAPSession()%20As%20Variant%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESet%20WshShell%20%3D%20CreateObject(%22WScript.Shell%22)%3CBR%20%2F%3ESet%20proc%20%3D%20WshShell.Exec(%22C%3A%5CProgram%20Files%20(x86)%5CSAP%5CFrontEnd%5CSAPgui%5Csaplogon.exe%22)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20temp%3CBR%20%2F%3Etemp%20%3D%20Timer%3CBR%20%2F%3EDo%20While%20Timer%20-%20temp%20%26lt%3B%205%3CBR%20%2F%3ELoop%3CBR%20%2F%3ESet%20SapGui%20%3D%20GetObject(%22SAPGUI%22)%3CBR%20%2F%3ESet%20Appl%20%3D%20SapGui.GetScriptingEngine%3C%2FP%3E%3CP%3ESet%20Connection%20%3D%20Appl.Openconnection(%22EGP%20-%20ERP%20Americas%20Electricity%20Production%22%2C%20True)%3CBR%20%2F%3ESet%20session%20%3D%20Connection.Children(0)%3C%2FP%3E%3CP%3Esession.findById(%22wnd%5B0%5D%2Fusr%2FtxtRSYST-BNAME%22).Text%20%3D%20%22XXX%22%3CBR%20%2F%3Esession.findById(%22wnd%5B0%5D%2Fusr%2FpwdRSYST-BCODE%22).Text%20%3D%20%22XXX%22%3CBR%20%2F%3Esession.findById(%22wnd%5B0%5D%2Fusr%2FtxtRSYST-LANGU%22).Text%20%3D%20%22EN%22%3CBR%20%2F%3Esession.findById(%22wnd%5B0%5D%22).sendVKey%200%3C%2FP%3E%3CP%3EIf%20Not%20IsObject(Application)%20Then%3CBR%20%2F%3ESet%20SapGuiAuto%20%3D%20GetObject(%22SAPGUI%22)%3CBR%20%2F%3ESet%20Appl%20%3D%20SapGuiAuto.GetScriptingEngine%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EIf%20Not%20IsObject(Connection)%20Then%3CBR%20%2F%3ESet%20Connection%20%3D%20Application.Children(0)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EIf%20Not%20IsObject(session)%20Then%3CBR%20%2F%3ESet%20session%20%3D%20Connection.Children(0)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EIf%20IsObject(WScript)%20Then%3CBR%20%2F%3EWScript.ConnectObject%20session%2C%20%22on%22%3CBR%20%2F%3EWScript.ConnectObject%20Application%2C%20%22on%22%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3ESet%20StartSAPSession%20%3D%20Connection.Children(0)%3C%2FP%3E%3CP%3EEnd%20Function%3CBR%20%2F%3EPublic%20Function%20ExtractDocuments()%3CBR%20%2F%3EDim%20Arr()%20As%20Variant%3CBR%20%2F%3EDim%20DocNum%20As%20String%3CBR%20%2F%3EDim%20Company%20As%20String%3CBR%20%2F%3EDim%20FY%20As%20String%3CBR%20%2F%3EDim%20AttCnt%20As%20Integer%3CBR%20%2F%3EDim%20i%20As%20Long%3CBR%20%2F%3EDim%20j%20As%20Long%3CBR%20%2F%3EDim%20HWnd2%20As%20Long%3CBR%20%2F%3EDim%20lCurHwnd%20As%20Long%3CBR%20%2F%3EDim%20sText%20As%20String%20*%20255%3C%2FP%3E%3CP%3E'%20When%20session%20is%20Nothing%20then%20we%20need%20to%20create%20a%20new%20session%3CBR%20%2F%3E'%20else%20assume%20we%20can%20re-use%20the%20session%3CBR%20%2F%3EIf%20session%20Is%20Nothing%20Then%3CBR%20%2F%3ESet%20session%20%3D%20StartSAPSession%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E'%20Load%20the%20table%20as%20an%20Array%2C%20this%20will%20be%20faster%3CBR%20%2F%3EArr%20%3D%20Range(%22Table1%22).ListObject.DataBodyRange%3C%2FP%3E%3CP%3E'%20Cycle%20through%20each%20row%20of%20the%20table%20(Arr)%3CBR%20%2F%3EFor%20i%20%3D%201%20To%20UBound(Arr%2C%201)%3CBR%20%2F%3E'%20Start%20by%20loading%20the%20row%20you%20will%20enter%3CBR%20%2F%3EDocNum%20%3D%20Arr(i%2C%201)%3CBR%20%2F%3ECompany%20%3D%20Arr(i%2C%202)%3CBR%20%2F%3EFY%20%3D%20Arr(i%2C%203)%3C%2FP%3E%3CP%3EWith%20session%3CBR%20%2F%3E.findById(%22wnd%5B0%5D%22).maximize%3CBR%20%2F%3E.StartTransaction%20%22FB03%22%20'%20Load%20the%20transaction%20you%20are%20after%3CBR%20%2F%3E.findById(%22wnd%5B0%5D%2Fusr%2FtxtRF05L-BELNR%22).Text%20%3D%20DocNum%3CBR%20%2F%3E.findById(%22wnd%5B0%5D%2Fusr%2FctxtRF05L-BUKRS%22).Text%20%3D%20Company%3CBR%20%2F%3E.findById(%22wnd%5B0%5D%2Fusr%2FtxtRF05L-GJAHR%22).Text%20%3D%20FY%3CBR%20%2F%3E.findById(%22wnd%5B0%5D%22).sendVKey%200%20'%20Execute%20transaction%3C%2FP%3E%3CP%3E'%20The%20query%20runs%20and%20you%20select%20context%20menu%20and%20attachments%3CBR%20%2F%3E.findById(%22wnd%5B0%5D%2Ftitl%2Fshellcont%2Fshell%22).pressContextButton%20%22%25GOS_TOOLBOX%22%3CBR%20%2F%3E.findById(%22wnd%5B0%5D%2Ftitl%2Fshellcont%2Fshell%22).selectContextMenuItem%20%22%25GOS_VIEW_ATTA%22%3C%2FP%3E%3CP%3E'%20How%20many%20attachments%20are%20there%3F%20If%201%20or%20more%20then%20save%20each.%3CBR%20%2F%3EAttCnt%20%3D%20.findById(%22wnd%5B1%5D%2Fusr%2FcntlCONTAINER_0100%2Fshellcont%2Fshell%22).RowCount%3CBR%20%2F%3EIf%20AttCnt%20%26gt%3B%200%20Then%3CBR%20%2F%3EFor%20j%20%3D%200%20To%20AttCnt%20-%201%3CBR%20%2F%3E.findById(%22wnd%5B1%5D%2Fusr%2FcntlCONTAINER_0100%2Fshellcont%2Fshell%22).selectedRows%20%3D%20j%3CBR%20%2F%3E.findById(%22wnd%5B1%5D%2Fusr%2FcntlCONTAINER_0100%2Fshellcont%2Fshell%22).pressToolbarButton%20%22%25ATTA_EXPORT%22%3CBR%20%2F%3E.send%20(%22%7BENTER%7D%22)%3CBR%20%2F%3ENext%20j%3CBR%20%2F%3E.findById(%22wnd%5B1%5D%2Ftbar%5B0%5D%2Fbtn%5B0%5D%22).press%20'%20Exit%20the%20Attachments%20window%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ENext%20i%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-721983%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-722204%22%20slang%3D%22en-US%22%3ERe%3A%20Download%20attachment%20from%20SAP%20Using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722204%22%20slang%3D%22en-US%22%3EHave%20you%20tried%20removing%20the%20.Send(%22%7BENTER%7D%22)%20and%20placing%20this%20immediately%20before%20the%20%22ATTA_Export%22%20command%3A%3CBR%20%2F%3EApplication.Sendkeys%20%22~%22%3C%2FLINGO-BODY%3E
Highlighted
Chandrakanth K
Contributor

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

 

 

1 Reply
Highlighted
Have you tried removing the .Send("{ENTER}") and placing this immediately before the "ATTA_Export" command:
Application.Sendkeys "~"