Spreadsheet Playback from Excel 

Procedure

  1. When starting work with the SAP Automation GUI Component, you would need to perform the following two preliminary operations:
  1. After logging onto an R/3 system, there are only five basic operations for driving the SAP Automation GUI Component:
  1. To finish working with the GUI Component perform the following two closing operations:

Example

The following example plays back Excel spreadsheets that have been created or recorded using a simple format.

The initial column in a row represents one of the nine operations listed above.

The additional columns in the row represent the data values being set.

For simplicity, controls are represented by their index on the screen. Keys are represented by their Windows Virtual-Key value.

Option Explicit

' Interpret an encoded Excel spreadsheet as an SAP session
' The first column in the row determines the operation, and
' the remaining columns are arguments.

' Operations:
' C: connect
' L: logon
' T: set text (or matchcode) fields
' X: set check boxes or radio buttons
' O: set OK code
' P: position cursor to a control
' K: send a key
' M: send a menu
' F: logoff
' Q: quit

Sub SapSheet()
Dim Sap As Object ' Dim as SapEvent if using Excel 97
Dim nRow As Integer
Dim nCol As Integer
Dim Ctrl As Integer
Dim CtrlVal As String
Dim OkCode As String
Dim KeyNum As Integer
Dim OnOff As Boolean
Dim HostName As String ' for C
Dim SystemNumber As String ' for C
Dim Client As String ' for L
Dim UserID As String ' for L
Dim Password As String ' for L
Dim Language As String ' for L
Dim MenuName As String ' for M
Dim OK As Boolean

' Start with the first cell in the speadsheet.

nRow = 1
nCol = 1

' Start the OLE Automation server.

Set Sap = CreateObject("SapAutoGui.Event")

' Loop through the speadsheet until we find an empty entry
' in column 1. Ignore rows with unrecognized letters or
' numbers in column 1.

Do
Cells(nRow, 1).Show
Select Case Cells(nRow, 1)

Case "C" ' connect
HostName = Cells(nRow, 2)
SystemNumber = Cells(nRow, 3)
OnOff = Cells(nRow, 4)
If OnOff Then
OK = Sap.Connect(HostName, SystemNumber, _
SapGuiFront)
Else
OK = Sap.Connect(HostName, SystemNumber, 0)
End If
If Not OK Then
MsgBox "Could not connect to " & HostName & " " & SystemNumber
End If

Case "L" ' logon
Client = Cells(nRow, 2)
UserID = Cells(nRow, 3)
Password = Cells(nRow, 4)
Language = Cells(nRow, 5)
Sap.Logon Client, UserID, Password, Language

Case "T" ' set text/matchcode data
nCol = 2

' Loop through the row for pairs of control indices and
' new text values. Stop when we find an empty column where
' we expect to find a control index (empty values are OK).

Do
If IsEmpty(Cells(nRow, nCol)) Then
Exit Do
End If
Ctrl = Cells(nRow, nCol)
If IsEmpty(Cells(nRow, nCol + 1)) Then
CtrlVal = ""
Else
CtrlVal = Cells(nRow, nCol + 1)
End If
Sap.Controls(Ctrl) = CtrlVal
nCol = nCol + 2
Loop

Case "X" ' set check box or radio button data
nCol = 2

' Loop through the row for pairs of control indices and
' new button values (1 for on, 0 or anything else for off).
' Stop when we find an empty column.

Do
If IsEmpty(Cells(nRow, nCol)) Then
Exit Do
End If
Ctrl = Cells(nRow, nCol)
If IsEmpty(Cells(nRow, nCol + 1)) Then
Exit Do
End If
OnOff = (Cells(nRow, nCol + 1) = 1)
Sap.Controls(Ctrl).Selected = OnOff
nCol = nCol + 2
Loop

Case "K" ' send key

' If column 2 has a value, use it as the virtual-key value to
' send. Otherwise send the default key.

If Not IsEmpty(Cells(nRow, 2)) Then
KeyNum = Cells(nRow, 2)
Sap.SendKey (KeyNum)
Else
Sap.SendEvent
End If

Case "M" ' send menu

' If column 2 has a value, use it as the menu name to
' send. Otherwise ignore this line.

If Not IsEmpty(Cells(nRow, 2)) Then
MenuName = Cells(nRow, 2)
Sap.SendMenuName (MenuName)
End If

Case "O" ' set OK code
OkCode = Cells(nRow, 2)
Sap.OkCode = OkCode

Case "P" ' position cursor to a control
Ctrl = Cells(nRow, 2)
Sap.SetCursorByControl Ctrl

Case "F" ' logoff
Sap.Logoff

Case "Q" ' quit
Sap.Quit

Case Empty ' stop spreadsheet interpretation
Exit Do

End Select
nRow = nRow + 1
nCol = 1
DoEvents
Loop
End Sub