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