vba script to download the data from SAP

[vimalanathk]

Hi All,

can some one please help me on my below requirement.

- I recorded a script in SAP
- now i want to give that script in excel so that clicking in excel SAP script should get run in back end save the excel file in specified folder.
- *in sap coding there are two paths given which needs to be picked from excel cell from specified location.

below is my incomplete code in excel:

code<>
Dim fobj

Set fobj = CreateObject("scripting.filesystemobject")

Str = fobj.getabsolutePathname("")
'msgbox str

****need to assign the range to pick the below paths from excel cell*******

SAP Date1

Public Sub SAP(Date1)

If (above mentioned cells are not blank) <> "" Then

If Not IsObject(Application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set Application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = Application.Children(0)
End If
If Not IsObject(session) Then
Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject Application, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "fb03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[20]").press
session.findById("wnd[0]/usr/txtBR_XBLNR-LOW").Text = "1"
session.findById("wnd[0]/usr/txtBR_XBLNR-HIGH").SetFocus
session.findById("wnd[0]/usr/txtBR_XBLNR-HIGH").caretPosition = 0
session.findById("wnd[0]/usr/btn%_BR_BUKRS_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[23]").press
session.findById("wnd[2]/usr/ctxtRLGRAP-FILENAME").Text = "C:\Users\vp8221\Desktop\IC Macro\LE Code.txt"
session.findById("wnd[2]/usr/ctxtRLGRAP-FILENAME").caretPosition = 44
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/btn%_BR_XBLNR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL-SLOW_I[1,0]").Text = ""
session.findById("wnd[1]/tbar[0]/btn[23]").press
session.findById("wnd[2]/usr/ctxtRLGRAP-FILENAME").Text = "C:\Users\vp8221\Desktop\IC Macro\Assignment.txt"
session.findById("wnd[2]/usr/ctxtRLGRAP-FILENAME").caretPosition = 47
session.findById("wnd[2]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/btn%_BR_XBLNR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/txtRSCSEL-SLOW_I[1,0]").Text = ""
session.findById("wnd[1]").sendVKey 8
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[2]").Select
session.findById("wnd[1]/usr/sub:SAPLSPO5:0101/radSPOPLI-SELFLAG[1,0]").Select
session.findById("wnd[1]/usr/sub:SAPLSPO5:0101/radSPOPLI-SELFLAG[1,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtRLGRAP-FILENAME").Text = "C:\Users\vp8221\Desktop\IC Macro\Excel Dumps\GP1dump.xls"
session.findById("wnd[1]/usr/ctxtRLGRAP-FILENAME").caretPosition = 44
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]").sendVKey 3
session.findById("wnd[0]").sendVKey 3
session.findById("wnd[0]").sendVKey 3
Else
MsgBox "Date Range not found.", vbCritical, "File doesnt exist"
Exit Sub
End If


Dim objExcel
Dim objFile

Set objExcel = GetObject(, "Excel.Application")
objExcel.Visible = True
objExcel.Workbooks("Worksheet in Basis (1)").Activate
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objExcel.ActiveWorkbook.savecopyas str& "C:OpenItemsGP1.xls"
objExcel.ActiveWorkbook.Close

session.findById("wnd[0]").Close
session.findById("wnd[1]/usr/btnSPOP-OPTION1").press
code<>