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
<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>
Vba codes are below
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
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 | |
---|---|---|---|---|---|---|---|
Company Code | Account | GC | LC | Year | Name to be Saved GC | Name 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: