mengyuxin

SAP GUI Scripting 10 - Reading GridView Control

Apr 10th, 2021 (edited)
746
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ' SAP GUI Scripting Exercise
  2. ' This file is provided as is. Please use and modify it for your own use.
  3. ' Check out my playlist of SAP GUI Scripting related videos:
  4. ' https://www.youtube.com/watch?v=oPPhA14Pm-8&list=PLk9erb9HGsPh28DftWcP8BIXmBtu2wnsL
  5. '
  6. ' This document uses transaction OAER to download documents attached to ERP documents. This example was created with sales order
  7. ' but could be adapted to use any document  type. This script works for documents that can be downloaded in Business Document Navigator
  8. '
  9. '
  10. ' Cheers, Csongor
  11.  
  12. Public SapGuiAuto, WScript, msgcol
  13. Public objGui  As GuiApplication
  14. Public objConn As GuiConnection
  15. Public objSess As GuiSession
  16. Public objSBar As GuiStatusbar
  17. Public objSheet As Worksheet
  18. Dim W_System
  19. Private LogCount As Integer
  20. Public shScript, shLog As Worksheet
  21.  
  22.  
  23.  
  24. Function Attach_Session() As Boolean
  25. Dim il, it
  26. Dim W_conn, W_Sess
  27.  
  28. If W_System = "" Then
  29.    Attach_Session = False
  30.    Exit Function
  31. End If
  32.  
  33. If Not objSess Is Nothing Then
  34.     If objSess.Info.SystemName & objSess.Info.Client = W_System Then
  35.         Attach_Session = True
  36.         Exit Function
  37.     End If
  38. End If
  39.  
  40. If objGui Is Nothing Then
  41.    Set SapGuiAuto = GetObject("SAPGUI")
  42.    Set objGui = SapGuiAuto.GetScriptingEngine
  43. End If
  44.  
  45. For il = 0 To objGui.Children.Count - 1
  46.     Set W_conn = objGui.Children(il + 0)
  47.     For it = 0 To W_conn.Children.Count - 1
  48.         Set W_Sess = W_conn.Children(it + 0)
  49.         If W_Sess.Info.SystemName & W_Sess.Info.Client = W_System Then
  50.             Set objConn = objGui.Children(il + 0)
  51.             Set objSess = objConn.Children(it + 0)
  52.             Exit For
  53.         End If
  54.     Next
  55. Next
  56.  
  57. If objSess Is Nothing Then
  58.    MsgBox "No active session to system " + W_System + ", or scripting is not enabled.", vbCritical + vbOKOnly
  59.    Attach_Session = False
  60.    Exit Function
  61. End If
  62.  
  63. If IsObject(WScript) Then
  64.    WScript.ConnectObject objSess, "on"
  65.    WScript.ConnectObject objGui, "on"
  66. End If
  67.  
  68. Set objSBar = objSess.findById("wnd[0]/sbar")
  69. objSess.findById("wnd[0]").maximize
  70. Attach_Session = True
  71.  
  72.  
  73. End Function
  74.  
  75. Public Sub RunGUIScript(currentline As Integer)
  76.  
  77. Dim W_Ret As Boolean
  78. Dim GridView As Object
  79.  
  80. W_Ret = Attach_Session
  81. If Not W_Ret Then
  82.     Exit Sub
  83. End If
  84.  
  85.  
  86.  
  87. 'On Error GoTo myerr
  88.  
  89. objSess.findById("wnd[0]").maximize
  90. objSess.findById("wnd[0]/tbar[0]/okcd").Text = "/nSE16"
  91. objSess.findById("wnd[0]").sendVKey 0
  92. objSess.findById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").Text = "USR02"
  93. objSess.findById("wnd[0]/usr/ctxtDATABROWSE-TABLENAME").caretPosition = 5
  94. objSess.findById("wnd[0]").sendVKey 0
  95. objSess.findById("wnd[0]/usr/txtI10-LOW").Text = "DL_YXMENG"
  96. objSess.findById("wnd[0]/usr/txtMAX_SEL").Text = ""
  97. objSess.findById("wnd[0]/usr/txtMAX_SEL").SetFocus
  98. objSess.findById("wnd[0]/usr/txtMAX_SEL").caretPosition = 11
  99. objSess.findById("wnd[0]/tbar[1]/btn[8]").Press
  100.  
  101. ' Save the gridview control as a local object
  102. 'Set GridView = objSess.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[0]/shell")
  103. Set GridView = objSess.findById("wnd[0]/usr/cntlGRID1/shellcont/shell")
  104.  
  105. 'Get the column list from the view and update the titles in Row 9
  106. For i = 0 To GridView.ColumnCount - 1
  107.     Cells(9, i + 1) = GridView.GetColumnTitles(GridView.ColumnOrder(i))(0)
  108. Next i
  109.  
  110. ' Extract all the cell contents the the SAP control and put it into the Excel from row 10
  111. For i = 0 To GridView.RowCount - 1
  112.     For j = 0 To GridView.ColumnCount - 1
  113.         Cells(10 + i, j + 1) = GridView.GetCellValue(i, GridView.ColumnOrder(j))
  114.     Next j
  115. Next i
  116.  
  117.  
  118. ' Setting the line status to completed
  119. Exit Sub
  120.  
  121. myerr:
  122.     MsgBox Err.Number & ":" & Err.Description
  123.  
  124. End Sub
  125.  
  126.  
  127. Function FolderCreate(ByVal path As String) As Boolean
  128.  
  129. FolderCreate = True
  130. Set fso = CreateObject("Scripting.FileSystemObject")
  131.  
  132. If FolderExists(path) Then
  133.     Exit Function
  134. Else
  135.     On Error GoTo DeadInTheWater
  136.     fso.CreateFolder path ' could there be any error with this, like if the path is really screwed up?
  137.    Exit Function
  138. End If
  139.  
  140. DeadInTheWater:
  141.     MsgBox "A folder could not be created for the following path: " & path & ". Check the path name and try again."
  142.     FolderCreate = False
  143.     Exit Function
  144.  
  145. End Function
  146.  
  147. Function FolderExists(ByVal path As String) As Boolean
  148.  
  149. FolderExists = False
  150. Set fso = CreateObject("Scripting.FileSystemObject")
  151.  
  152. If fso.FolderExists(path) Then FolderExists = True
  153.  
  154. End Function
  155.  
  156.  
  157.  
  158. Sub StartExtract()
  159. Dim currentline As Integer
  160.  
  161.     'Se the defaults
  162.    W_System = Range("System").Value
  163.     Set shScript = Worksheets("Script")
  164.     ResetLog
  165.    
  166.     RunGUIScript 10
  167.    
  168.     ' Update the current date and time
  169.    Cells(2, 2).Value = Now()
  170.     objSess.EndTransaction
  171.     MsgBox "Script completed"
  172.  
  173. End Sub
  174.  
  175. Sub ResetLog()
  176.     'This function deleted the current log and resets some values
  177.    
  178.     Dim LastRow As Integer
  179.    
  180.     Set shLog = Worksheets("Log")
  181.     LogCount = 0
  182.     shLog.Select
  183.     'Find the last row in the sheet
  184.    LastRow = shLog.UsedRange.Rows(shLog.UsedRange.Rows.Count).Row
  185.     If LastRow < 3 Then LastRow = 3
  186.     Rows("3:" + CStr(LastRow)).Select
  187.     Selection.Delete Shift:=xlUp
  188.     Range("A3").Select
  189.     shScript.Select
  190. End Sub
  191.  
  192. Sub AddLog(id As String, message As String, color As Integer)
  193.     'New line is added to the log
  194.    shLog.Cells(LogCount + 3, 1).Font.color = color
  195.     shLog.Cells(LogCount + 3, 2).Font.color = color
  196.     shLog.Cells(LogCount + 3, 1) = id
  197.     shLog.Cells(LogCount + 3, 2) = message
  198.    
  199.     'Add borders to the new cells
  200.    shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlDiagonalDown).LineStyle = xlNone
  201.     shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlDiagonalUp).LineStyle = xlNone
  202.     With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlEdgeLeft)
  203.         .LineStyle = xlContinuous
  204.         .ColorIndex = 0
  205.         .TintAndShade = 0
  206.         .Weight = xlThin
  207.     End With
  208.     With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlEdgeTop)
  209.         .LineStyle = xlContinuous
  210.         .ColorIndex = 0
  211.         .TintAndShade = 0
  212.         .Weight = xlThin
  213.     End With
  214.     With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlEdgeBottom)
  215.         .LineStyle = xlContinuous
  216.         .ColorIndex = 0
  217.         .TintAndShade = 0
  218.         .Weight = xlThin
  219.     End With
  220.     With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlEdgeRight)
  221.         .LineStyle = xlContinuous
  222.         .ColorIndex = 0
  223.         .TintAndShade = 0
  224.         .Weight = xlThin
  225.     End With
  226.     With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlInsideVertical)
  227.         .LineStyle = xlContinuous
  228.         .ColorIndex = 0
  229.         .TintAndShade = 0
  230.         .Weight = xlThin
  231.     End With
  232.     With shLog.Range("A" + CStr(LogCount + 3) + ":B" + CStr(LogCount + 3)).Borders(xlInsideHorizontal)
  233.         .LineStyle = xlContinuous
  234.         .ColorIndex = 0
  235.         .TintAndShade = 0
  236.         .Weight = xlThin
  237.     End With
  238.    
  239.    
  240.     LogCount = LogCount + 1
  241. End Sub
  242.  
  243.  
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×