Reflection Desktop VBA Guide
HowTos / Screen Scrape Data to a Spreadsheet
In This Topic
    Screen Scrape Data to a Spreadsheet
    In This Topic

    This sample opens an Excel spreadsheet and copies data from a terminal screen in the Reflection demo program to the spreadsheet row by row until it encounters an empty row. The GetText method is used to copy the data.

    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 screen-scrape-data-to-a-spreadsheet.rd3x (IBM) and screen-scrape-data-to-a-spreadsheet.rdox files. The download package contains everything you need to run the macros in these files. See Download the VBA Sample Sessions.

    The screen that displays the data must be visible in the terminal window when you run the macro. 

    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.

    To run this sample

    1. Create an IBM 3270 terminal session and enter demo:ibm3270.sim in the Host /IP Address box.
    2. On the log in screen, enter any credentials.
    3. On the second screen, enter Kayak.
    4. In the Visual Basic Editor Project Explorer, insert a module under the Project folder for this session, copy this code into the code pane, and then press F5 to run the macro.                     
      Screen scrape data to a spreadsheet
      Copy Code 
      Sub SaveDataToExcelSpreadSheet()
   
    Dim path As String
    Dim rowText As String
    Dim col, row As Integer
    Dim rowFromHost() As String
    Dim ExcelApp As Object
    Dim wkBook As Object
   
    Set ExcelApp = CreateObject("Excel.Application")


    ExcelApp.Visible = True
      
    Set wkBook = ExcelApp.Workbooks.Add
   
    wkBook.Sheets("sheet1").Activate
       
    'Starting on row 7, get the first row of data
    row = 7
    rowText = ThisIbmScreen.GetText(row, 9, 65)
   
    Do  
        'Replace spaces between words in first column and remove extra spaces
        rowText = Replace(rowText, " ", "_", 1, 1)
        rowText = ExcelApp.WorksheetFunction.Trim(rowText)
       
        'Place each column into an array
        rowFromHost = Split(rowText, " ")
              
        For col = LBound(rowFromHost) To UBound(rowFromHost)
            'Replace delimiter that was added to handle multiple words in first column
            rowFromHost(col) = Replace(rowFromHost(col), "_", " ", 1, 1)
            'Write column to spreadsheet
            wkBook.Sheets("sheet1").cells(row, (col + 5)).value = rowFromHost(col)
        Next col
       
        'Get the next row of data from the screen
        row = row + 1
        rowText = ThisIbmScreen.GetText(row, 9, 65)
       
    'Loop until the first empty row
    Loop While Len(ExcelApp.WorksheetFunction.Trim(rowText)) > 1
      
End Sub
    5. After the macro completes, the following data is displayed in an Excel spreadsheet.
                          

     To run this sample

    1. Create a Reflection VT terminal session and enter demo:UNIX in the Host /IP Address box.
    2. On the log in screen, enter any credentials and press enter.
    3. On the demo> prompt on the second screen, enter demodata.
    4. In the Visual Basic Editor Project Explorer, insert a module under the Project folder for the new session, copy this code into the code pane, and press F5 to run the macro.
    Screen scrape data to a spreadsheet
    Copy Code 
    Sub SaveDataToExcel()
    Dim path As String
    Dim rowText As String
                       
    Dim row As Integer, col As Integer
    Dim rowFromHost() As String
    Dim ExcelApp As Object
    Dim wkBook As Object
  
    Set ExcelApp = CreateObject("Excel.Application")
    ExcelApp.Visible = True
    Set wkBook = ExcelApp.Workbooks.Add
    wkBook.Sheets("sheet1").Activate
  
    'starting on row 5, get the first row of text from the screen
    row = 5
    rowText = ThisScreen.GetText(row, 24, 32)
  
    Do
        'replace spaces between words in first column and remove extra spaces
        rowText = Replace(rowText, "|", "")
        rowText = ExcelApp.WorksheetFunction.Trim(rowText)
     
        'Place each column into an array
        rowFromHost = Split(rowText, " ")
                         
        For col = LBound(rowFromHost) To UBound(rowFromHost)
            'Replace delimiter
            rowFromHost(col) = Replace(rowFromHost(col), "_", " ", 1, 1)
            'Write row to spreadsheet
            wkBook.Sheets("sheet1").cells(row, (col + 5)).value = rowFromHost(col)
        Next col
     
        row = row + 1
        rowText = ThisScreen.GetText(row, 24, 32)
  
    'Loop until the first empty row
    Loop While Len(ExcelApp.WorksheetFunction.Trim(rowText)) > 1
   
End Sub

    Concepts

    First, this sample opens Excel, adds a new workbook, and then activates a sheet in the workbook.

    Open Excel workbook and activate a sheet
    Copy Code 
    Dim ExcelApp As Object
Dim wkBook As Object
          
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
Set wkBook = ExcelApp.Workbooks.Add
wkBook.Sheets("sheet1").Activate


    Then it initializes the row variable to the first row it needs to copy and starts a loop to copy data until an empty row is encountered. The GetText method is used to get a row of data from the screen as a text string. This method retrieves a string of text from the screen, given starting row and column values and a length. 

    If you are copying structured data (as in our IBM sample), you can use the screen row coordinates for your starting point. For nonstructured data (such as Open Systems sessions), 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.
    Get the text from the screen
    Copy Code 
    'Get a row of text from the screen
rowText = ThisIbmScreen.GetText(row, 9, 65)
    Get the text from the screen
    Copy Code 
    'Get a row of text from the screen
'Using the row, column, and length values from a recorded macro
rowText = ThisScreen.GetText(row, 24, 32)

    After the macro gets the text, it combines labels with multiple words into single words, removes extra spaces between words, and then creates an array.

    Finally, the values from the array are copied into the Excel workbook. First the "_" added between compound words with spaces is replaced, and then the array values are put into the corresponding columns in the Excel worksheet.

    Copy values into Excel
    Copy Code 
     For col = LBound(rowFromHost) To UBound(rowFromHost)
     'Replace delimiter
    rowFromHost(col) = Replace(rowFromHost(col), "_", " ", 1, 1)
    'Write row to spreadsheet
    wkBook.Sheets("sheet1").cells(row, (col + 5)).value = rowFromHost(col)
Next col
    See Also

    Quickstart

    Navigating Through Open Systems Sessions