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