Need VBA code help in For Loop and Sap Scripting Please check and help me

A

ahmed18

Board Regular
Joined
Jul 13, 2016
Messages
72
Hello All

I want to make automation of importing files from sap to excel with Different company code and Account # so as i have make a macro but it not working properly so please check the Codes and help me and also i have enclosed the screen shot of excel sheets and my codes as well

Please help me in error and guide me

Screen shot of My excel sheet

Excel 2012
ABCDEFG
1Company CodeAccountGCLCYearName to be Saved GCName to be Saved GC
2FR401201100030102016"12011000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls""12011000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
3FR401201100010102016"12011000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls""12011000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
4FR401201600030102016"12016000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls""12016000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
5FR401201600010102016"12016000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls""12016000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​



Vba codes are below

Code: 
Sub SAPLoginMacro() 'DESIGNED TO LOG IN & EXTRACT DATA FROM SAP USING USER LOGIN AND PASSWORD

Dim stSapUName As String, stSapPW As String
Dim SapguiApp As Object, connection As Object, session As Object


Dim lastcouumn As Integer
Dim lastrow As Long


     
     
    
stSapUName = InputBox("Please enter your SAP User name", "SAP User Name")
stSapPW = InputBox("Please enter your SAP Password", "SAP Password")






On Error GoTo errFailed
    '****************************************************************************************
    'ESTABLISH CONNECTION TO SAP                                                            *
    '****************************************************************************************
    Set SapguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
    Set connection = SapguiApp.OpenConnection("LH1", True)
    Set session = connection.Children(0)
    
On Error GoTo 0




    With session
        '************************************************************************************
        'LOGON TO SAP                                                                       *
        '************************************************************************************
        .findById("wnd[0]/usr/txtRSYST-BNAME").Text = stSapUName
        .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = stSapPW
        .findById("wnd[0]").sendVKey 0
    
        '************************************************************************************
        'PROCESSES THE POPUP IF MULTIPLE LOGINS DETECTED                                    *
        '************************************************************************************
        If .Children.Count > 1 Then
            .findById("wnd[1]/usr/radMULTI_LOGON_OPT1").Select
            .findById("wnd[1]/tbar[0]/btn[0]").press
        End If
    
        '************************************************************************************
        'NAVIGATES TO THE TCODE                                                    *
        '************************************************************************************
            .findById("wnd[0]").maximize
            .findById("wnd[0]/tbar[0]/okcd").Text = "fs10n"
            .findById("wnd[0]").sendVKey 0
            
        
        On Error Resume Next
        '************************************************************************************
        'SELECTS THE FILE TYPE SPREADSHEET AND SAVES FILE TO HARD DRIVE                     *
        '************************************************************************************
        .findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell").pressToolbarContextButton _
        "&MB_EXPORT"
            
            
            For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
            lastrow = ActiveSheet.Range(Rows.Count).End(xlUp).Row
            
            
            
            
            
            On Error Resume Next
            
            
     
'       lastColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
'       lastRow = ActiveSheet.Range( Rows.Count).End(xlUp).Row
'       For i = 0
'       A1 = range(
        session.findById("wnd[0]/usr/ctxtSO_SAKNR-LOW").Text = Cells(i, 2).Value
        session.findById("wnd[0]/usr/ctxtSO_BUKRS-LOW").Text = Cells(i, 1).Value
        session.findById("wnd[0]/usr/txtGP_GJAHR").Text = Cells(i, 5).Value
        session.findById("wnd[0]/usr/ctxtSO_GSBER-LOW").SetFocus
        session.findById("wnd[0]/usr/ctxtSO_GSBER-LOW").caretPosition = 0
        session.findById("wnd[0]").sendVKey 19
        session.findById("wnd[0]/usr/ctxtGP_CURTP").Text = Cells(i, 3).Value
        session.findById("wnd[0]/usr/ctxtGP_CURTP").SetFocus
        session.findById("wnd[0]/usr/ctxtGP_CURTP").caretPosition = 2
        session.findById("wnd[0]/tbar[1]/btn[8]").press
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").selectContextMenuItem "&PC"
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        
        .findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\YOUR USERID HERE\12011000\"
        .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Cells(i, 6).Value & ".xls"
        session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 2
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        session.findById("wnd[0]").sendVKey 3
        session.findById("wnd[0]/usr/ctxtGP_CURTP").Text = Cells(i, 4).Value
        session.findById("wnd[0]/usr/ctxtGP_CURTP").SetFocus
        session.findById("wnd[0]/usr/ctxtGP_CURTP").caretPosition = 2
        session.findById("wnd[0]/tbar[1]/btn[8]").press
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").selectContextMenuItem "&PC"
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        
        .findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\YOUR USERID HERE\12011000\"
        .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Cells(i, 7).Value & ".xls"
     '"Muzzu_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
        session.findById("wnd[0]").sendVKey 3
     
     
     'i = i + 1
     
     Next i
    
    
    


    End With


    On Error GoTo 0
    '****************************************************************************************
    'CLEAR ALL SET VARIABLES                                                                *
    '****************************************************************************************
    Set session = Nothing
    Set connection = Nothing
    Set SapguiApp = Nothing
    
    
    
           


'********************************************************************************************
'DISPLAY ERROR HANDLING MESSAGE BOX                                                        *
'********************************************************************************************
errFailed:
    MsgBox "The connection to SAP has been halted by the user."
    
    
    


End Sub

Below is the link where u can find the my excel sheet and code in it

https://app.box.com/s/eekvctxswoija2xoi5rzhy34k4obv9nh

Thanks in advance
Ahmed Muzamil:)
 
Last edited by a moderator:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Do me a favor... clean up your code... I do NOT have access to FS10N so I can not record a macro so you need to... Go into SAP and use the recorder to do what you need it to do ONE time then paste that code here. You can NOT have SAP repeat logins like you are trying to do. I can not clean up this code, because I have NO ACCESS to FS10N on my network.

Code: 
[COLOR=#333333]Sub SAPLoginMacro() 'DESIGNED TO LOG IN & EXTRACT DATA FROM SAP USING USER LOGIN AND PASSWORD
[/COLOR]
Dim lastcouumn As Long, lastrow As Long
Dim stSapUName As String, stSapPW As String
Dim SapguiApp As Object, connection As Object, session As Object
    
stSapUName = InputBox("Please enter your SAP User name", "SAP User Name")
stSapPW = InputBox("Please enter your SAP Password", "SAP Password")

On Error GoTo errFailed
    '****************************************************************************************
    'ESTABLISH CONNECTION TO SAP                                                            *
    '****************************************************************************************
    Set SapguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
    Set connection = SapguiApp.OpenConnection("LH1", True)
    Set session = connection.Children(0)
    
On Error GoTo 0

    With session
        '************************************************************************************
        'LOGON TO SAP                                                                       *
        '************************************************************************************
        .findById("wnd[0]/usr/txtRSYST-BNAME").Text = stSapUName
        .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = stSapPW
        .findById("wnd[0]").sendVKey 0
    
        '************************************************************************************
        'PROCESSES THE POPUP IF MULTIPLE LOGINS DETECTED                                    *
        '************************************************************************************
        If .Children.Count > 1 Then
            .findById("wnd[1]/usr/radMULTI_LOGON_OPT1").Select
            .findById("wnd[1]/tbar[0]/btn[0]").press
        End If
    
        '************************************************************************************
        'NAVIGATES TO THE TCODE                                                    *
        '************************************************************************************
            .findById("wnd[0]").maximize
            .findById("wnd[0]/tbar[0]/okcd").Text = "fs10n"
            .findById("wnd[0]").sendVKey 0
            
        
        On Error Resume Next
        '************************************************************************************
        'SELECTS THE FILE TYPE SPREADSHEET AND SAVES FILE TO HARD DRIVE                     *
        '************************************************************************************
[COLOR=#ff0000]        .findById("wnd[0]/usr/cntlCONTAINER/shellcont/shell").pressToolbarContextButton _
        "&MB_EXPORT[/COLOR]"
            
            
            For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
            lastrow = ActiveSheet.Range(Rows.Count).End(xlUp).Row         
            
     
'       lastColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
'       lastRow = ActiveSheet.Range( Rows.Count).End(xlUp).Row
'       For i = 0
'       A1 = range(
        session.findById("wnd[0]/usr/ctxtSO_SAKNR-LOW").Text = Cells(i, 2).Value
        session.findById("wnd[0]/usr/ctxtSO_BUKRS-LOW").Text = Cells(i, 1).Value
        session.findById("wnd[0]/usr/txtGP_GJAHR").Text = Cells(i, 5).Value
        session.findById("wnd[0]/usr/ctxtSO_GSBER-LOW").SetFocus
        session.findById("wnd[0]/usr/ctxtSO_GSBER-LOW").caretPosition = 0
        session.findById("wnd[0]").sendVKey 19
        session.findById("wnd[0]/usr/ctxtGP_CURTP").Text = Cells(i, 3).Value
        session.findById("wnd[0]/usr/ctxtGP_CURTP").SetFocus
        session.findById("wnd[0]/usr/ctxtGP_CURTP").caretPosition = 2
        session.findById("wnd[0]/tbar[1]/btn[8]").press
[COLOR=#ff0000]        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"[/COLOR]
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").selectContextMenuItem "&PC"
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        
        .findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\YOUR USERID HERE\12011000\"
        .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Cells(i, 6).Value & ".xls"
        session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 2
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        session.findById("wnd[0]").sendVKey 3
        session.findById("wnd[0]/usr/ctxtGP_CURTP").Text = Cells(i, 4).Value
        session.findById("wnd[0]/usr/ctxtGP_CURTP").SetFocus
        session.findById("wnd[0]/usr/ctxtGP_CURTP").caretPosition = 2
        session.findById("wnd[0]/tbar[1]/btn[8]").press
[COLOR=#ff0000]        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"[/COLOR]
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").selectContextMenuItem "&PC"
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        
        .findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\YOUR USERID HERE\12011000\"
        .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Cells(i, 7).Value & ".xls"
     '"Muzzu_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
        session.findById("wnd[0]").sendVKey 3
          
     'i = i + 1
     
     Next i

    End With

    On Error GoTo 0
    '****************************************************************************************
    'CLEAR ALL SET VARIABLES                                                                *
    '****************************************************************************************
    Set session = Nothing
    Set connection = Nothing
    Set SapguiApp = Nothing        


'********************************************************************************************
'DISPLAY ERROR HANDLING MESSAGE BOX                                                        *
'********************************************************************************************
errFailed:
    MsgBox "The connection to SAP has been halted by the user."

 [COLOR=#333333]End Sub[/COLOR]

You are trying to export three different times.... why?
 
Last edited:
Upvote 0
Hello Sir,,

As you see above in the screen shot i have multiple company codes with multiple accounts so instead of i can do for loop and sap codes as once we updated in excel..
Im trying to make automation for the my process as i have to import all compay code with account

Please find the Codes below
Code: 
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "fs10n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtSO_SAKNR-LOW").Text = "12011000"
        session.findById("wnd[0]/usr/ctxtSO_BUKRS-LOW").Text = "FR40"
        session.findById("wnd[0]/usr/txtGP_GJAHR").Text = "2016"
        session.findById("wnd[0]/usr/ctxtSO_GSBER-LOW").SetFocus
        session.findById("wnd[0]/usr/ctxtSO_GSBER-LOW").caretPosition = 0
        session.findById("wnd[0]").sendVKey 19
        session.findById("wnd[0]/usr/ctxtGP_CURTP").Text = "30"
        session.findById("wnd[0]/usr/ctxtGP_CURTP").SetFocus
        session.findById("wnd[0]/usr/ctxtGP_CURTP").caretPosition = 2
        session.findById("wnd[0]/tbar[1]/btn[8]").press
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").selectContextMenuItem "&PC"
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        
        .findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\YOUR USERID HERE\12011000"
        .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "12011000 30 _" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
        
        session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 2
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        session.findById("wnd[0]").sendVKey 3
        session.findById("wnd[0]/usr/ctxtGP_CURTP").Text = "10"
        session.findById("wnd[0]/usr/ctxtGP_CURTP").SetFocus
        session.findById("wnd[0]/usr/ctxtGP_CURTP").caretPosition = 2
        session.findById("wnd[0]/tbar[1]/btn[8]").press
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
        session.findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").selectContextMenuItem "&PC"
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
        session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        
        .findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Users\YOUR USERID HERE\12011000"
        .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "12011000 10 _" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"

I want to take control from excel and if you want more reference please vist this link you will get my excel sheet
https://app.box.com/s/eekvctxswoija2xoi5rzhy34k4obv9nh

Thanks
Ahmed
 
Upvote 0
Try this, let me know if it works or not... if not let me knoiw where it breaks at...

Code: 
Sub SAPLoginMacro() 'DESIGNED TO LOG IN & EXTRACT DATA FROM SAP USING USER LOGIN AND PASSWORD

Dim lastRow As Long
Dim stSapUName As String, stSapPW As String, stUserId As String
Dim SapguiApp As Object, connection As Object, session As Object
    
stSapUName = InputBox("Please enter your SAP User name", "SAP User Name")
stSapPW = InputBox("Please enter your SAP Password", "SAP Password")


lastRow = Range("A" & Rows.Count).End(xlUp).Row

stUserId = (Environ$("Username"))  'GATHER ID OF CURRENTLY LOGGED IN USER

On Error GoTo errFailed
    '****************************************************************************************
    'ESTABLISH CONNECTION TO SAP                                                            *
    '****************************************************************************************
    Set SapguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
    Set connection = SapguiApp.OpenConnection("LH1", True)
    Set session = connection.Children(0)
    
On Error GoTo 0

    With session
        '************************************************************************************
        'LOGON TO SAP                                                                       *
        '************************************************************************************
        .findById("wnd[0]/usr/txtRSYST-BNAME").Text = stSapUName
        .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = stSapPW
        .findById("wnd[0]").sendVKey 0
    
        '************************************************************************************
        'PROCESSES THE POPUP IF MULTIPLE LOGINS DETECTED                                    *
        '************************************************************************************
        If .Children.Count > 1 Then
            .findById("wnd[1]/usr/radMULTI_LOGON_OPT1").Select
            .findById("wnd[1]/tbar[0]/btn[0]").press
        End If
    End With

For j = 3 To 4
    For i = 2 To lastRow
    
        myPath = "C:\Users\" & stUserId & "\" & Cells(i, 1).Value & "\"
        myFile = Range("A" & i).Value & " " & Cells(i, j).Value & "_" & Format(Now(), "mm_dd_yyyy") & ".xlsx"
        
        If Dir(myPath) = "" Then MkDir (myPath)
    
        Wirh session
            '************************************************************************************
            'NAVIGATES TO THE TCODE                                                    *
            '************************************************************************************
            .findById("wnd[0]").maximize
            .findById("wnd[0]/tbar[0]/okcd").Text = "/NFS10N"
            .findById("wnd[0]").sendVKey 0
    
            '************************************************************************************
            'SELECTS THE FILE TYPE SPREADSHEET AND SAVES FILE TO HARD DRIVE                     *
            '************************************************************************************
            .findById("wnd[0]/usr/ctxtSO_SAKNR-LOW").Text = Range("B" & i).Value
            .findById("wnd[0]/usr/ctxtSO_BUKRS-LOW").Text = Range("A" & i).Value
            .findById("wnd[0]/usr/txtGP_GJAHR").Text = Range("E" & i).Value
            .findById("wnd[0]/usr/ctxtSO_GSBER-LOW").SetFocus
            .findById("wnd[0]/usr/ctxtSO_GSBER-LOW").caretPosition = 0
            .findById("wnd[0]").sendVKey 19
            .findById("wnd[0]/usr/ctxtGP_CURTP").Text = Cells(i, j).Value
            .findById("wnd[0]/usr/ctxtGP_CURTP").SetFocus
            .findById("wnd[0]/usr/ctxtGP_CURTP").caretPosition = 2
            .findById("wnd[0]/tbar[1]/btn[8]").press
            .findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
            .findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").selectContextMenuItem "&PC"
            .findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
            .findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
            .findById("wnd[1]/tbar[0]/btn[0]").press
            .findById("wnd[1]/usr/ctxtDY_PATH").Text = myPath
            .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = myFile
            .findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 2
            .findById("wnd[1]/tbar[0]/btn[0]").press
        End With
        
            Workbooks(myFile).Close
            
    Next i
Next j

    '****************************************************************************************
    'CLEAR ALL SET VARIABLES                                                                *
    '****************************************************************************************
    Set session = Nothing
    Set connection = Nothing
    Set SapguiApp = Nothing

'********************************************************************************************
'DISPLAY ERROR HANDLING MESSAGE BOX                                                        *
'********************************************************************************************
errFailed:
    MsgBox "The connection to SAP has been halted by the user."

End Sub
 
Upvote 0
Hello Sir

Its getting break in this line
Code: 
 Workbooks(myFile).Close

as its showing run time error 9 as the "Subscript out of range "

And after 1st line its not going to second one in excel

Please check and help sir..

Thanks
AHmed Muzamil
 
Last edited:
Upvote 0
And also sir forget to tell you that i con't open the excel file which its getting saved in pathe...?
 
Upvote 0
I am assuming you are either using an older version of Excel or you have default file saving as version 97 (.xls) instead of .xlsx.

Try this, see if it will help...

Code: 
[COLOR=#333333]Sub SAPLoginMacro() 'DESIGNED TO LOG IN & EXTRACT DATA FROM SAP USING USER LOGIN AND PASSWORD
[/COLOR]
Dim lastRow As Long
Dim stSapUName As String, stSapPW As String, stUserId As String
Dim SapguiApp As Object, connection As Object, session As Object
    
stSapUName = InputBox("Please enter your SAP User name", "SAP User Name")
stSapPW = InputBox("Please enter your SAP Password", "SAP Password")

lastRow = Range("A" & Rows.Count).End(xlUp).Row

stUserId = (Environ$("Username"))  'GATHER ID OF CURRENTLY LOGGED IN USER

On Error GoTo errFailed
    '****************************************************************************************
    'ESTABLISH CONNECTION TO SAP                                                            *
    '****************************************************************************************
    Set SapguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
    Set connection = SapguiApp.OpenConnection("LH1", True)
    Set session = connection.Children(0)
    
On Error GoTo 0

    With session
        '************************************************************************************
        'LOGON TO SAP                                                                       *
        '************************************************************************************
        .findById("wnd[0]/usr/txtRSYST-BNAME").Text = stSapUName
        .findById("wnd[0]/usr/pwdRSYST-BCODE").Text = stSapPW
        .findById("wnd[0]").sendVKey 0
    
        '************************************************************************************
        'PROCESSES THE POPUP IF MULTIPLE LOGINS DETECTED                                    *
        '************************************************************************************
        If .Children.Count > 1 Then
            .findById("wnd[1]/usr/radMULTI_LOGON_OPT1").Select
            .findById("wnd[1]/tbar[0]/btn[0]").press
        End If
    End With

For j = 3 To 4
    For i = 2 To lastRow
    
        myPath = "C:\Users\" & stUserId & "\" & Cells(i, 1).Value & "\"
        myFile = Range("A" & i).Value & " " & Cells(i, j).Value & "_" & Format(Now(), "mm_dd_yyyy") & ".xls"
        
        If Dir(myPath) = "" Then MkDir (myPath)
    
        Wirh session
            '************************************************************************************
            'NAVIGATES TO THE TCODE                                                    *
            '************************************************************************************
            .findById("wnd[0]").maximize
            .findById("wnd[0]/tbar[0]/okcd").Text = "/NFS10N"
            .findById("wnd[0]").sendVKey 0
    
            '************************************************************************************
            'SELECTS THE FILE TYPE SPREADSHEET AND SAVES FILE TO HARD DRIVE                     *
            '************************************************************************************
            .findById("wnd[0]/usr/ctxtSO_SAKNR-LOW").Text = Range("B" & i).Value
            .findById("wnd[0]/usr/ctxtSO_BUKRS-LOW").Text = Range("A" & i).Value
            .findById("wnd[0]/usr/txtGP_GJAHR").Text = Range("E" & i).Value
            .findById("wnd[0]/usr/ctxtSO_GSBER-LOW").SetFocus
            .findById("wnd[0]/usr/ctxtSO_GSBER-LOW").caretPosition = 0
            .findById("wnd[0]").sendVKey 19
            .findById("wnd[0]/usr/ctxtGP_CURTP").Text = Cells(i, j).Value
            .findById("wnd[0]/usr/ctxtGP_CURTP").SetFocus
            .findById("wnd[0]/usr/ctxtGP_CURTP").caretPosition = 2
            .findById("wnd[0]/tbar[1]/btn[8]").press
            .findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
            .findById("wnd[0]/usr/cntlFDBL_BALANCE_CONTAINER/shellcont/shell").selectContextMenuItem "&PC"
            .findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
            .findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
            .findById("wnd[1]/tbar[0]/btn[0]").press
            .findById("wnd[1]/usr/ctxtDY_PATH").Text = myPath
            .findById("wnd[1]/usr/ctxtDY_FILENAME").Text = myFile
            .findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 2
            .findById("wnd[1]/tbar[0]/btn[0]").press
        End With
        
            Workbooks(myFile).Close
            
    Next i
Next j

    '****************************************************************************************
    'CLEAR ALL SET VARIABLES                                                                *
    '****************************************************************************************
    Set session = Nothing
    Set connection = Nothing
    Set SapguiApp = Nothing

'********************************************************************************************
'DISPLAY ERROR HANDLING MESSAGE BOX                                                        *
'********************************************************************************************
errFailed:
    MsgBox "The connection to SAP has been halted by the user."
 [COLOR=#333333]End Sub[/COLOR]
 
Upvote 0
Hello Sir,,

Yes it has default saving as version 97 (.xls) its working but the im getting Break
Code: 
[COLOR=#333333]Workbooks(myFile).Close[/COLOR]
as as its showing run time error 9 as the "Subscript out of range "

Can you please check this sir,,?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
SAP coding
Replies
2
Views
96
Jordan2
J
T
  • Question
Workbook not visible by VBA
Replies
5
Views
181
Artik
Artik
T
  • Question
Macro to download SAP report to Excel
Replies
0
Views
2K
texasguy66
T
S
  • Question
SAP GUI DOES NOT SAVE in MACRO VBA
Replies
1
Views
1K
Zaifee
Z
S
  • Question
SAP GUI Scripting does not save the report
Replies
2
Views
1K
szymon94
S

Forum statistics

Threads
1,215,061
Messages
6,122,921
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top