InfoConnect VBA Guide
HowTos / Enter Data from an Excel Spreadsheet
In This Topic
    Enter Data from an Excel Spreadsheet
    In This Topic

    This sample shows how to create an Excel macro to enter data into an InfoConnect demo terminal session from an Excel spreadsheet.

    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 enter-data-from-an-excel-spreadsheet.xlsm (IBM) file. The download package contains everything you need to run the macros in this file. See Download the VBA Sample Sessions.

    This sample applies only to IBM terminals

    To run this sample

    1. Create an Excel workbook and enter the following data into a sheet on the workbook, using the cells and rows shown below:

    2. On the Excel VBA Editor Tools menu, select References and then select the following InfoConnect Libraries:
    • Attachmate_Reflection_Objects
    • Attachmate_Reflection_Objects_Emulation_IbmHosts
    • Attachmate_Reflection_Objects_Framework
    1. Copy the following code into the Excel Sheet object code window and then run the PutDataIntoScreen macro.
    Enter data from Excel
    Copy Code
    Sub PutDataIntoScreen()
        'Declare an object variable for the InfoConnect object:
        Dim app As Attachmate_Reflection_Objects_Framework.ApplicationObject
        
        'Declare additional InfoConnect objects, such as frame, terminal, and view:
        Dim frame As Attachmate_Reflection_Objects.frame
        Dim terminal As Attachmate_Reflection_Objects_Emulation_IbmHosts.IbmTerminal
        Dim view As Attachmate_Reflection_Objects.view
        Dim screen As Attachmate_Reflection_Objects_Emulation_IbmHosts.ibmScreen
        
        'Declare a variables for the cell data, return code, and counter
        Dim cellData As String, project As String, group As String, projectType As String, member As String
        Dim rCode As ReturnCode
        Dim row As Integer
      
        'Assign the Application object to the object variable. The following code creates an instance of InfoConnect:
        Set app = New Attachmate_Reflection_Objects_Framework.ApplicationObject
      
        'wait until InfoConnect initializes
        Do While app.IsInitialized = False
            app.Wait 200
        Loop
      
        'Create controls to open and display the session document.
        Set frame = app.GetObject("Frame")
        Set terminal = app.CreateControl2("09E5A1B4-0BA6-4546-A27D-FE4762B7ACE1")
                               
        terminal.HostAddress = "demo:ibm3270.sim"
        Set view = frame.CreateView(terminal)
        frame.Visible = True
        'navigate to the screen you want to enter data into
        Set screen = terminal.screen
        rCode = screen.SendControlKey(ControlKeyCode_Transmit)
        rCode = screen.WaitForHostSettle(3000, 2000)
      
        rCode = screen.SendKeys("ISPF")
        rCode = screen.SendControlKey(ControlKeyCode_Transmit)
        rCode = screen.WaitForHostSettle(3000, 2000)
      
        rCode = screen.SendKeys("2")
        rCode = screen.SendControlKey(ControlKeyCode_Transmit)
        rCode = screen.WaitForHostSettle(3000, 2000)
      
        'Get text and copy it in row by row, starting at row 6
        row = 6
      
        'Loop through rows until an empty cell is encountered
        Do
           'Get the data from Excel
           project = Cells(row, 2).Value
           group = Cells(row, 3).Value
           projectType = Cells(row, 4).Value
           member = Cells(row, 5).Value
     
           'Put the data into the appropriate fields
           rCode = screen.PutText2(project, 5, 18)
           rCode = screen.PutText2(group, 6, 18)
           rCode = screen.PutText2(projectType, 7, 18)
           rCode = screen.PutText2(member, 8, 18)
    
           'Transmit the data to the host and wait for the screen to be ready for more input
           rCode = screen.PutText2("autoexec", 2, 15)
           rCode = screen.SendControlKey(ControlKeyCode_Transmit)
           rCode = screen.WaitForHostSettle(3000, 2000)
           rCode = screen.SendControlKey(ControlKeyCode_F3)
           rCode = screen.WaitForHostSettle(3000, 2000)
     
           'Increment the counter to the next row
           row = row + 1
        
           'Find out if the next row contains data
           cellData = Cells(row, 2).Value
        
       Loop While cellData <> ""
     
    End Sub
    

    Concepts

    This macro creates a new InfoConnect session as explained in Create a Session From a Microsoft Excel Macro. Then it navigates to the screen that has the data form.

    Note: This example macro uses the NewScreenReady event to wait until the screen is ready for input while navigating screens. To get the best performance for macros that navigate screens, consider using a SmartWait method as shown in Navigating Through IBM Screens.

     

    It assigns the values from the current row in the Excel spreadsheet to local variables and puts these values into the appropriate fields on the first screen, using the PutText2 method. After putting the values into the form, it enters the data in the program using the SendControlKey method. 

     

     

     

     

    See Also