Skip to Content
Jul 05, 2019 at 12:18 PM

Scripting to copy sap comments to excel


Hi Script man,

My finial code is shown below but problem is sap is running and saving the comments like "data saved or any error mgs will come", That remarks I need to put in excel please help on this.

Public Sub SimpleSAPExport()
Dim connection As Object
Dim Myvalue As Variant
Dim Myvalue2 As Variant
Dim objSheet1 As Worksheet

Set SapGuiAuto = GetObject("SAPGUI") 'Get the SAP GUI Scripting object
Set SAPApp = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI
Set SAPCon = SAPApp.Children(0) 'Get the first system that is currently connected
Set Session = SAPCon.Children(0) 'Get the first session (window) on that connection

If Not IsObject(SAPApp) Then

Set SapGuiAuto = GetObject("SAPGUI")

Set SAPApp = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(connection) Then

Set connection = SAPconnection.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 SAPApp, "on"

End If

Dim objExcel, objWorkbook, objSheet, i
'Dim A As Variant

'Dim B As Variant

Set objExcel = CreateObject("Excel.Application")

myfile = application.GetOpenFilename(, , "Browse for worksheets")
Workbooks.Open myfile

Set objSheet = ActiveWorkbook.Sheets("Sheet1")
'A = Input("enter starting row", "")
'B = Input("Enter Ending row", "")
Myvalue = InputBox("Give me some input")
Myvalue2 = InputBox("Give me some input")
StopNow = False

'For i = 2 To 1000
For iRow = Myvalue To Myvalue2
'Cycle through the rows with status 0 and call the ProcessRow function to process them
'For iRow = startrow To objSheet.UsedRange.Rows.Count
'If objSheet.Cells(iRow, 3) = "0" Then
'Call ProcessRow(iRow)
'itemcount = itemcount + 1
'objSheet.Cells(9, 1) = itemcount & "/" & itemmax
'End If
Range("A1").Value = i

' DoEvents
'Function ProcessRow(iRow)
'Dim W_BPNumber, W_SearchTerm
'Dim lineitems As Long
' Set the line status to "processing..."
'objSheet.Cells(iRow, 5) = 1

'BP Number
'If objSheet.Cells(iRow, 1) <> "" Then
'W_BPNumber = objSheet.Cells(iRow, 1)
'W_BPNumber = "xxxxxx"
'End If

'Seartch term
'If objSheet.Cells(iRow, 2) <> "" Then
'W_SearchTerm = objSheet.Cells(iRow, 2)
' W_SearchTerm = ""
'End If

col1 = Trim(CStr(objSheet.Cells(iRow, 1).Value)) 'Column1
COL2 = Trim(CStr(objSheet.Cells(iRow, 2).Value)) 'Column2
COL3 = Trim(CStr(objSheet.Cells(iRow, 3).Value)) 'Column3
COL4 = Trim(CStr(objSheet.Cells(iRow, 4).Value)) 'Column4

'On Error GoTo myerr

Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nzpb2"
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = col1
Session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 12
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[1]/usr/tblSAPLMGMMTC_VIEW").getAbsoluteRow(5).Selected = True
On Error Resume Next
Session.findById("wnd[1]/usr/tblSAPLMGMMTC_VIEW/txtMSICHTAUSW-DYTXT[0,5]").caretPosition = 0
Session.findById("wnd[1]").sendVKey 0
Session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").Text = COL2
Session.findById("wnd[1]/usr/ctxtRMMG1-WERKS").caretPosition = 4
Session.findById("wnd[1]").sendVKey 0
Session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP11/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2301/ctxtMARC-MMSTA").Text = COL3
On Error Resume Next
Session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP11/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2301/ctxtMARC-MMSTD").Text = COL4
On Error Resume Next
Session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP11/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2301/ctxtMARC-MMSTD").caretPosition = 10
Session.findById("wnd[0]").sendVKey 0
On Error Resume Next

If StopNow Then Exit For

'Session.findById("wnd[1]/usr/tblSAPLMGMMTC_VIEW").getAbsoluteRow(5).Selected = False
' Gets the message from the status bar and save it in column B
objSheet.Cells(iRow, 5) = objSBar.Text

' Update the Status to "Completed" and exit
objSheet.Cells(iRow, 5) = 2
'Exit Function
' Update the status to "Error"
objSheet.Cells(iRow, 5) = 3
Next iRow

'End Function
End Sub