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

[ahmed18]

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

	A	B	C	D	E	F	G
1	Company Code	Account	GC	LC	Year	Name to be Saved GC	Name to be Saved GC
2	FR40	12011000	30	10	2016	"12011000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"	"12011000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
3	FR40	12011000	10	10	2016	"12011000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"	"12011000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
4	FR40	12016000	30	10	2016	"12016000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"	"12016000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
5	FR40	12016000	10	10	2016	"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

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

 

[dchaney]

What line?

 

[ahmed18]

An action triggered by the server was denied based on rule

the next line will be

Do you want to see a list of the actions triggered in the last communication step..?

If i click on yes it show the Deny..

So need to add trust location in excel or what to do sir,,?

 

[dchaney]

I meant is there a line of the VBA code that causes the error? If this is an error with SAP then I can not help you, if there is a line of VBA code in your excel file that is causing the issue I might be able to provide assistance.

 

[ahmed18]

No its not problem in SAp its from VBA only..sir..? as manual check i sap its working fine so its a problem in VBA only..

Please check and help sir..

 

[dchaney]

I can not help if I do not know where the code is breaking...What line of VBA code is causing the issue... does it come up with an error message to End or Debug? If so and you click Debug what line of code is highlighted yellow???????????

 

[ahmed18]

Nope that kind of error is not coming sir,,
if i do sap manually it working fine and if i run the macro then only its showing the that error..

Its showing below error

============================================================================Request Details
Version: 1.0
Encoding: UTF-8
============================================================================




----------------------------------------------------------------------------
REQUEST:


- Object:
Object name: c:\program files (x86)\SAP\FrontEnd\SAPgui\False\FR40 30_08_25_2016.xls
Object type: File


- Context:
System ID: LH1
Network:
Client: 100
Transaction: FS10N
Dynpro name: SAPLKKBL
Dynpro number: 0200


- Access:
Access type: Write




ACTION:
Action taken: Deny
Action source: SAP rule




RULE:


- Object:
Object name: [SAP:GUIInstallDir]
Object type: Directory


- Context:
System ID:
Network:
Client:
Transaction:
Dynpro name:
Dynpro number:


- Access:
Access type: Write
----------------------------------------------------------------------------

 

[dchaney]

It is something between SAP and Excel then that SAP does not like... not sure what it is but is not a VBA issue...

ACTION:
Action taken: Deny
Action source: SAP rule

 

[ahmed18]

Hello Sir,,

Good Day

Now its working fine thanks for you continue support and kindness

Will get back to you if some thing i needed..

Thank you..

 

[dchaney]

What was the issue?

What was the fix?

 

[ahmed18]

SIr need one more help sir..

In my excel sheet i have to filter each time with different company code and the account #, I have 49 Different company codes for all company codes i have to Same account # to be filtered like 13011000,13011400,13011500,13016000

Can you please help how to filter in VBA code in for loop way Please

It will be a great help Thank you

AHmed Muzamil