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

Create an Excel spreadsheet and add the data to the following cells on sheet1.
Name the sheet "ProjectInfo" and save the Excel file as ProjectData.xlsx.
In InfoConnect, create a new 3270 terminal session and enter "demo:ibm3270.sim" in the Host name /IP Address box.
On the first demo screen, enter any credentials.
On the second screen, enter "ISPF" at the prompt.
On the third screen, enter "1" to select the Browse option.

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

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

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

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

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