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
Company CodeAccountGCLCYearName to be Saved GCName to be Saved GC
FR40"12011000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls""12011000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
FR40"12011000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls""12011000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
FR40"12016000 30_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls""12016000 10_" & WorksheetFunction.Substitute(WorksheetFunction.Text(Now(), "mm_dd_yyyy"), " ", "_") & ".xls"
FR40"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>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12011000[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2016[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]12011000[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2016[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12016000[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2016[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]12016000[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2016[/TD]

</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:
Sort by date Sort by votes
An action triggered by the server was denied based on rule
Click to expand...
the next line will be
Do you want to see a list of the actions triggered in the last communication step..?
Click to expand...

If i click on yes it show the Deny..

So need to add trust location in excel or what to do sir,,?
 
Upvote 0
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.
 
Upvote 0
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..
 
Upvote 0
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???????????
 
Upvote 0
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
----------------------------------------------------------------------------
Click to expand...
 
Last edited:
Upvote 0
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
 
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA - how to add a copy and paste values on current module
Replies
2
Views
233
Minx_Minxy
M
M
  • Solved
Macro stops after message box appear
Replies
3
Views
749
bobsan42
bobsan42
J
  • Question Question
SAP coding
Replies
2
Views
441
Jordan2
J
T
  • Question Question
Workbook not visible by VBA
Replies
5
Views
794
Artik
Artik
T
  • Question Question
Macro to download SAP report to Excel
Replies
0
Views
3K
texasguy66
T

Forum statistics

Threads
1,226,892
Messages
6,193,538
Members
453,806
Latest member
Gene Michaels

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