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

Scripting to copy sap comments to excel

1368 Views

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
Session.findById("wnd[0]").maximize

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
'Next
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)
'Else
'W_BPNumber = "xxxxxx"
'End If

'Seartch term
'If objSheet.Cells(iRow, 2) <> "" Then
'W_SearchTerm = objSheet.Cells(iRow, 2)
'Else
' 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]").SetFocus
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").SetFocus
Session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP11/ssubTABFRA1:SAPLMGMM:2000/subSUB2:SAPLMGD1:2301/ctxtMARC-MMSTD").caretPosition = 10
Session.findById("wnd[0]").sendVKey 0
Session.findById("wnd[1]/usr/btnSPOP-OPTION1").press
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
'myerr:
' Update the status to "Error"
objSheet.Cells(iRow, 5) = 3
Next iRow


'End Function
End Sub