Reflection Desktop 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 Reflection 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 Reflection, 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 Reflection 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 <> ""