Reflection Desktop VBA Guide
HowTos / Get Screen Data With an Excel Macro
In This Topic
    Get Screen Data With an Excel Macro
    In This Topic

    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 article contains tabbed content that is specific to each terminal type. Be sure the tab for your Which Terminal Type are you Using? is selected.

    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

    1. 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
    1. 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

    1. In Reflection, create a new VT terminal session and in the Host Name /IP address box, enter "Demo:UNIX".
    2. Save the new session in your "My Documents\Micro Focus\Reflection" folder as GetData.rdox.
    3. 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
    1. 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.

     

    See Also

    Quickstart

    Navigating Through Open Systems Sessions