InfoConnect VBA Guide
HowTos / Get Data From a Spreadsheet
In This Topic
    Get Data From a Spreadsheet
    In This Topic

    This sample opens an Excel spreadsheet and copies data from the spreadsheet to a terminal screen in the InfoConnect demo, row by row, until it encounters an empty row. The PutText method is used to enter the data.

    If you prefer to run macros in a pre-configured session instead of creating your own sessions, you can download the VBA Sample Sessions and open the get-data-from-spreadsheet.xlsx  (IBM) file. The download package contains everything you need to run the macros in this file. See Download the VBA Sample Sessions.

    The screen in which the data is entered must be visible in the terminal window when you run the macro. 

    This sample applies only to IBM terminals

    To run this sample

    1. Create an Excel spreadsheet and add the data to the following cells on sheet1.
    2. Name the sheet "ProjectInfo" and save the Excel file as ProjectData.xlsx.
    3. In InfoConnect, create a new 3270 terminal session and enter "demo:ibm3270.sim" in the Host name /IP Address box.
    4. On the first demo screen, enter any credentials.
    5. On the second screen, enter "ISPF" at the prompt.
    6. On the third screen, enter "1" to select the Browse option.
    7. In the Visual Basic Project Explorer, insert a module under the Project folder for the new session and copy the sample code into the code pane, and press F5 to run the macro. (To see each row of data entered in the screen fields, press F8 to step through the procedure with the debugger.)  
      Get data from a spreadsheet
      Copy Code
      Sub GetDataFromExcel()
          Dim path As String
          Dim rCode As ReturnCode
          Dim row As Integer, col As Integer
          Dim wsData As String
          Dim ExcelApp As Object
          Dim wkBook As Object
       
          'set the full path to the Excel workbook
          path = Environ$("USERPROFILE") & "\Documents\" & "ProjectData.xlsx"
       
          'Open the Excel workbook and file
          Set ExcelApp = CreateObject("Excel.Application")
                                 
      
          ExcelApp.Visible = True
       
          'Open the workbook and activate the "ProjectInfo" sheet.
          Set wkBook = ExcelApp.Workbooks.Open(path)
          wkBook.Sheets("ProjectInfo").Activate
       
          'start on row 6
          row = 6
       
          'Loop until the data row is empty
          Do
       
              'Get a row of data and put it into the terminal form
              For col = 0 To 3
                  wsData = wkBook.Sheets("ProjectInfo").cells(row, (col + 2)).value
                  'Put text into field
                  rCode = ThisIbmScreen.PutText2(wsData, (col + 5), 18)
              Next col
         
              ThisIbmScreen.PutText2 "autoexec", 2, 15
              'Transmit the data
              ThisIbmScreen.SendControlKey (ControlKeyCode_Transmit)
                                 
             'Wait for the screen to be ready for input
              rCode = ThisIbmScreen.WaitForHostSettle(3000, 2000)
             
              'Go back to previous screen
              ThisIbmScreen.SendControlKey (ControlKeyCode_F3)
             
              'Wait for the screen to be ready for input
              rCode = ThisIbmScreen.WaitForHostSettle(3000, 2000)
         
              'Go to the next row in Excel
              row = row + 1
            
              'get the value of the first cell in the row so we can check for an empty row
              wsData = wkBook.Sheets("ProjectInfo").cells(row, 2).value
                     
          Loop While wsData <> ""
       
      End Sub
      

    Concepts

    First, this sample opens the Excel spreadsheet.

    Open the ProjectData.xlsl Excel worksheet
    Copy Code
    'set the full path to the Excel workbook
    path = Environ$("USERPROFILE") & "\Documents\" & "ProjectData.xlsx"
       
    'Open the Excel workbook and file
    Set ExcelApp = CreateObject("Excel.Application")
    ExcelApp.Visible = True
        
    'Open the workbook and activate the "ProjectInfo" sheet.
    Set wkBook = ExcelApp.Workbooks.Open(path)
    wkBook.Sheets("ProjectInfo").Activate
    

     

    Then it initializes the row to start on the spreadsheet . It also starts a loop to input each row of data in the spreadsheet into InfoConnect until an empty row is encountered.

    Get data in each cell on the row and enter it into the terminal form
    Copy Code
    'Get a row of data and put it into the terminal form
    For col = 0 To 3
           wsData = wkBook.Sheets("ProjectInfo").cells(row, (col + 2)).value
                             
            'Put text into field
            rCode = ThisIbmScreen.PutText2(wsData, (col + 5), 18)      
    Next col
    


    After the form is filled out, the data is entered into the program.

    Enter the data
    Copy Code
    'Transmit the data
    ThisIbmScreen.SendControlKey (ControlKeyCode_Transmit)
                                   
    


    After entering the data, it waits until the screen is ready for input. Then it checks to find out if the next row in the spreadsheet has any data before it enters the next row in Excel. If the cell is empty, the loop ends.

    Note: This example macro uses the WaitForHostSettle method to wait until the screen is ready for input. To get the best performance for macros that navigate screens, consider using a SmartWait method as shown in Navigating Through IBM Screens.
    Go to the next row
    Copy Code
        'wait until the screen is ready for input
        rCode = ThisIbmScreen.WaitForHostSettle(3000, 2000)
       
        'Go to the next row in Excel
        row = row + 1
           
         'get the value of the first cell in the row so we can check for an empty row
        wsData = wkBook.Sheets("ProjectInfo").cells(row, 2).value
                   
    Loop While wsData <> ""