InfoConnect 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 InfoConnect 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 an InfoConnect 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