Get Data From a Spreadsheet

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.

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.

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 <> ""