Get Screen Data With an Excel Macro
You can use an Excel Macro to get data from a terminal screen and put it into a spreadsheet.
If you prefer to run macros in pre-configured sessions instead of creating your own sessions, you can download the VBA Sample Sessions and open the get-ibm-screen-data-with-an-excel-macro.xlsm (IBM) and the get-os-screen-data-with-an-excel-macro.xlsm (Open Systems) files. The download package contains everything you need to run the macros in these files. See Download the VBA Sample Sessions.
This sample uses the GetText method to get data from a session.
This Excel macro gets data from a terminal screen and copies it to an Excel spreadsheet. After the macro runs, the spreadsheet contains the following data:
Note: This example macro uses the NewScreenReady event to wait until the screen is ready for input while navigating screens. To get the best performance for macros that navigate screens, consider using a SmartWait method as shown in
Navigating Through IBM Screens.
To run this sample
- On the Excel VBA Editor Tools menu, select References and then select the following Reflection Libraries:
- Attachmate_Reflection_Objects
- Attachmate_Reflection_Objects_Emulation_IbmHosts
- Attachmate_Reflection_Objects_Framework
- Copy the following code into an Excel Sheet object code window and then press F5 to run the macro.
| Get screen data with an Excel macro |
Copy Code
|
Public WithEvents screen As Attachmate_Reflection_Objects_Emulation_IbmHosts.ibmScreen
Public Sub GetDataFromIBMScreen()
'Declare an object variable for the Reflection object
Dim app As Attachmate_Reflection_Objects_Framework.ApplicationObject
'Declare additional Reflection objects, such as frame, terminal, and view
Dim frame As Attachmate_Reflection_Objects.frame
Dim terminal As Attachmate_Reflection_Objects_Emulation_IbmHosts.IbmTerminal
Dim view As Attachmate_Reflection_Objects.view
'Create a new instance of Reflection
Set app = New Attachmate_Reflection_Objects_Framework.ApplicationObject
'wait until Reflection initializes
Do While app.IsInitialized = False
app.Wait (200)
Loop
'Create controls to open and display the session document.
Set frame = app.GetObject("Frame")
frame.Visible = True
Set terminal = app.CreateControl2("09E5A1B4-0BA6-4546-A27D-FE4762B7ACE1")
terminal.HostAddress = "demo:ibm3270.sim"
Set view = frame.CreateView(terminal)
Set screen = terminal.screen
End Sub
Private Sub screen_NewScreenReady(ByVal sender As Variant)
'In this IbmScreen
Dim screenID1 As String, screenID2 As String, screenID3 As String
Dim rCode As ReturnCode
Dim rowText As String
Dim rowFromHost() As String
Dim col As Integer, row As Integer
screenID1 = screen.GetText(1, 2, 6) 'ATM VM ispf
screenID2 = screen.GetText(1, 7, 4) 'option 2
screenID3 = screen.GetText(1, 25, 13) 'option 2
If screenID1 = "ATM VM" Then
rCode = screen.SendControlKey(ControlKeyCode_Transmit)
End If
If screenID2 = "ATM5" Then
rCode = screen.PutText2("kayak", 23, 1)
rCode = screen.SendControlKey(ControlKeyCode_Transmit)
End If
If screenID3 = "INTERNATIONAL" Then
'Start on row 7 and get the first row of text from the screen
row = 7
rowText = screen.GetText(row, 9, 65)
'Gather data until an empty row is encountered
Do
'Replace spaces between compound words in first column and remove extra spaces
rowText = Replace(rowText, " ", "_", 1, 1)
rowText = Application.WorksheetFunction.Trim(rowText)
'Place each column into an array
rowFromHost = Split(rowText, " ")
For col = LBound(rowFromHost) To UBound(rowFromHost)
'Replace delimiter that was added for compound words
rowFromHost(col) = Replace(rowFromHost(col), "_", " ", 1, 1)
'Write each column in the row to the spreadsheet
Cells(row, (col + 5)).Value = rowFromHost(col)
Next col
row = row + 1
'Get the new row of text from the screen
rowText = screen.GetText(row, 9, 65)
Debug.Print row
'Check string length after removing all extra spaces
Loop While Len(Application.WorksheetFunction.Trim(rowText)) > 0
End If
End Sub
|
This Excel macro gets data from a terminal screen and copies it to an Excel spreadsheet. After the macro runs, the spreadsheet contains the following data:
To run this sample
- In Reflection, create a new VT terminal session and in the Host Name /IP address box, enter "Demo:UNIX".
- Save the new session in your "My Documents\Micro Focus\Reflection" folder as GetData.rdox.
- Open Microsoft Excel and on the Excel VBA Editor Tools menu, select References and then select the following Reflection Libraries:
- Attachmate_Reflection_Objects
- Attachmate_Reflection_Objects_Emulation_OpenSystems
- Attachmate_Reflection_Objects_Framework
- Copy the following code into an Excel Sheet object code window and then run the GetDataFromReflection macro.
| Get screen data with an Excel macro |
Copy Code
|
Sub GetDataFromOSScreen()
'Declare an object variable for the Reflection object:
Dim app As Attachmate_Reflection_Objects_Framework.ApplicationObject
'Declare additional Reflection objects required to create and display a session
Dim frame As Attachmate_Reflection_Objects.frame
Dim screen As Attachmate_Reflection_Objects_Emulation_OpenSystems.screen
Dim terminal As Attachmate_Reflection_Objects_Emulation_OpenSystems.terminal
Dim view As Attachmate_Reflection_Objects.view
Dim rCode As Integer
Dim path As String
Dim rowText As String
Dim row As Integer, col As Integer
Dim rowFromHost() As String
Const NEVER_TIME_OUT = 0
'Create an instance of Reflection
Set app = New Attachmate_Reflection_Objects_Framework.ApplicationObject
'Wait until Reflection initializes
Do While app.IsInitialized = False
app.Wait (200)
Loop
'Create controls to open and display the session document.
Set frame = app.GetObject("Frame")
Set terminal = app.CreateControl(Environ$("USERPROFILE") & "\Documents\Micro Focus\Reflection\" & "GetData.rdox")
Set view = frame.CreateView(terminal)
frame.Visible = True
'Send keys to navigate to screen that contains data
'wait after keys are sent to make sure host screen is ready before sending more keys
Set screen = terminal.screen
screen.SendKeys "userID"
screen.SendControlKey ControlKeyCode_Return
rCode = screen.WaitForHostSettle(3000)
screen.SendKeys "DemoPassWord"
screen.SendControlKey ControlKeyCode_Return
rCode = screen.WaitForHostSettle(3000)
screen.SendKeys "demodata"
screen.SendControlKey ControlKeyCode_Return
rCode = screen.WaitForHostSettle(3000)
'Start on row 5 and get the first row of text using the row,
'column, and length values from a recorded macro
row = 5
rowText = screen.GetText(row, 24, 32)
'Get data as long as the row on the screen is not empty
Do
'Remove lines between columns and remove extra spaces
rowText = Replace(rowText, "|", "")
rowText = WorksheetFunction.Trim(rowText)
'Place each column into an array
rowFromHost = Split(rowText, " ")
'Write row to spreadsheet
For col = LBound(rowFromHost) To UBound(rowFromHost)
Cells(row, (col + 5)).Value = rowFromHost(col)
Next col
row = row + 1
'Get a row of text from the screen
'Using the row, column, and length values from a recorded macro
rowText = screen.GetText(row, 24, 32)
'Remove spaces and check for an empty row
Loop While Len(WorksheetFunction.Trim(rowText)) > 0
End Sub
|
Concepts
First, the sample creates a Reflection session. The IBM sample does this by creating a new session and then assigning it the demo Host/IP address. The Open System sample opens an existing session. For details about concepts related to this part of the sample, see Create a Session From a Microsoft Excel Macro and Open a Session From a Microsoft Excel Macro.
Then it navigates through the session to get to the screen data we want to copy.
This sample uses the NewScreenReady event to navigate to the host screen that contains the data. Declaring a global IbmScreen object variable using the WithEvents keyword allows us to access the Reflection screen events from the Excel macro.
The ScreenID variables hold text from specific locations on each screen. The text in each variable is compared with known text to determine which screen the program is on and then keys are sent to navigate to the next screen until the program is on the screen with the INTERNATIONAL text. (This is the screen that has the data we need to copy to Excel.)
| Navigate to the screen that has the data |
Copy Code
|
Public WithEvents screen As Attachmate_Reflection_Objects_Emulation_IbmHosts.IbmScreen
Private Sub screen_NewScreenReady(ByVal sender As Variant)
'In this IbmScreen
Dim screenID1 As String, screenID2 As String, screenID3 As String
Dim rCode As ReturnCode
Dim rowText As String
Dim rowFromHost() As String
Dim col As Integer, row As Integer
screenID1 = screen.GetText(1, 2, 6) 'ATM VM ispf
screenID2 = screen.GetText(1, 7, 4) 'option 2
screenID3 = screen.GetText(1, 25, 13) 'option 2
If screenID1 = "ATM VM" Then
rCode = screen.SendControlKey(ControlKeyCode_Transmit)
End If
If screenID2 = "ATM5" Then
rCode = screen.PutText2("kayak", 23, 1)
rCode = screen.SendControlKey(ControlKeyCode_Transmit)
End If
If screenID3 = "INTERNATIONAL" Then
...
|
This sample enters commands to navigate to the screen that contains the data. After sending each command, it waits until the screen is ready before entering the next command.
| Navigate to the screen that has the data |
Copy Code
|
Set screen = terminal.screen
screen.SendKeys "userID"
screen.SendControlKey ControlKeyCode_Return
rCode = screen.WaitForHostSettle(3000)
screen.SendKeys "DemoPassWord"
screen.SendControlKey ControlKeyCode_Return
rCode = screen.WaitForHostSettle(3000)
screen.SendKeys "demodata"
screen.SendControlKey ControlKeyCode_Return
rCode = screen.WaitForHostSettle(3000)
|
When the program is on the screen with the data, the GetText method is used to get a row or data as a string. The Replace function is used to handle compound words and to remove unwanted characters and the Excel Trim function is used to remove all extra leading and trailing spaces and spaces between words. Then the string is converted to an array.
If you are copying structured data (as in our IBM sample), you can use the screen row coordinates for your starting point. For unstructured data (Open Systems), you'll need to get the starting point coordinates by recording a macro or by searching for unique text on the screen as shown in
Navigating Through Open Systems Sessions.
Finally, the sample loops through the array and assigns the value of each element to a cell in the spreadsheet.
This process is repeated until the macro encounters an empty row.
