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
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
Have you tried removing the .Send("{ENTER}") and placing this immediately before the "ATTA_Export" command:
Application.Sendkeys "~"
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies