SAP sales order attachment downloader

SAP sales order attachment downloader.vba

Sub SAP()
    ' Tools -> References ... -> Browse
    ' C:\Program Files (x86)\SAP\FrontEnd\SAPgui\sapfewse.ocx
    ' SAP GUI Settings: Accessbility & Scripting -> Scripting
    ' Activate SAP GUI Scripting
    ' Disable Show natice Microsoft Windows dialogs
    
    Dim DOWNLOAD_FOLDER As String
    Dim oSapGui As Object
    Dim oApp As SAPFEWSELib.GuiApplication
    Dim oConn As SAPFEWSELib.GuiConnection
    Dim oSession As SAPFEWSELib.GuiSession
    Dim oALV As GuiComponent
    Dim Vbeln As String
    Dim Row, Rows As Integer
    Dim oGOSMenu As GuiComponent

    DOWNLOAD_FOLDER = ActiveWorkbook.ActiveSheet.Cells(2, 3)
       
    ' Ref to SAPGUI
    Set oSapGui = GetObject("SAPGUI")
    If IsObject(oSapGui) Then
        ' Ref to ScriptingEngine
        Set oApp = oSapGui.GetScriptingEngine
        If IsObject(oApp) Then
            ' Check connections
            If oApp.Children.Count > 0 Then
                ' Connect first connection on SAP GUI
                Set oConn = oApp.Children(0)
                ' Connect first session
                Set oSession = oConn.Children(0)
                 
                ' Minimize for performance
                oSession.FindById("wnd[0]").Iconify
                
                ' Call VA03
                oSession.StartTransaction ("VA03")
                
                Row = 1
                Vbeln = ActiveWorkbook.ActiveSheet.Cells(Row, 1)
                Do While Vbeln <> ""
                    oSession.FindById("wnd[0]/usr/ctxtVBAK-VBELN").Text = Vbeln
                    ' Button Run
                    oSession.FindById("wnd[0]").SendVKey F8
                    
                    If oSession.FindById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021", False) Is Nothing Then
                        ActiveWorkbook.ActiveSheet.Cells(Row, 2) = "Order not exit"
                        GoTo ContinueDo
                    End If
                    
                    ' GOS Menu
                    Set oGOSMenu = oSession.FindById("wnd[0]/titl/shellcont/shell", False)
                    If oGOSMenu Is Nothing Then
                        ActiveWorkbook.ActiveSheet.Cells(Row, 2) = "No GOS menu"
                        ' return document select
                        oSession.FindById("wnd[0]/tbar[0]/btn[3]").Press
                        GoTo ContinueDo
                    End If
                    oSession.FindById("wnd[0]/titl/shellcont/shell").PressContextButton "%GOS_TOOLBOX"
                    oSession.FindById("wnd[0]/titl/shellcont/shell").SelectContextMenuItem "%GOS_VIEW_ATTA"
                    
                    Set oALV = oSession.FindById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell", False)
                    If oALV Is Nothing Then
                        ActiveWorkbook.ActiveSheet.Cells(Row, 2) = "No attachment"
                        ' return document select
                        oSession.FindById("wnd[0]/tbar[0]/btn[3]").Press
                        GoTo ContinueDo
                    End If
                    Rows = oALV.RowCount()
                    If Rows > 0 Then
                         ' prepare folder
                        If Dir(DOWNLOAD_FOLDER & Vbeln, vbDirectory) <> "" Then
                            Set fso = CreateObject("Scripting.FileSystemObject")
                            fso.DeleteFolder DOWNLOAD_FOLDER & Vbeln
                        End If
                        MkDir DOWNLOAD_FOLDER & Vbeln
                        For i = 0 To Rows - 1
                            oALV.SelectedRows = i
                            oALV.PressToolbarButton "%ATTA_EXPORT"
                            oSession.FindById("wnd[1]/usr/ctxtDY_PATH").Text = DOWNLOAD_FOLDER & Vbeln
                            Set Filename = oSession.FindById("wnd[1]/usr/ctxtDY_FILENAME")
                            Filename.Text = (i + 1) & "_" & Filename.Text
                            ' generate local file (download)
                            oSession.FindById("wnd[1]/tbar[0]/btn[0]").Press
                            Set oALV = oSession.FindById("wnd[1]/usr/cntlCONTAINER_0100/shellcont/shell")
                        Next i
                    End If
                    ' close attachment window
                    oSession.FindById("wnd[1]/tbar[0]/btn[12]").Press
                    
                    ActiveWorkbook.ActiveSheet.Cells(Row, 2) = "Done"
ContinueDo:
                    Row = Row + 1
                    Vbeln = ActiveWorkbook.ActiveSheet.Cells(Row, 1)
                Loop
            Else
                MsgBox "Please login SAP system."
            End If
        End If
    End If
     
    Set oSession = Nothing
    Set oConn = Nothing
    Set oApp = Nothing
    Set oSapGui = Nothing
End Sub