Enter Data from an Excel Spreadsheet
HowTos > Enter Data from an Excel Spreadsheet

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

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 Reflection 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 PutDataIntoReflection macro.
Enter data from Excel
Copy Code 
Sub PutDataIntoReflection()
    'Declare an object variable for the Reflection object:
    Dim app As Attachmate_Reflection_Objects_Framework.ApplicationObject
    
    'Declare additional Reflection 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 Reflection:
    Set app = New Attachmate_Reflection_Objects_Framework.ApplicationObject
  
    'wait until Reflection 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 Reflection session as explained in Create a Session From a Microsoft Excel Macro. Then it navigates to the screen that has the data form as explained in Navigating Sessions.

For IBM sessions, another approach for screen navigation is to handle the NewScreenReady event for the new session. If you use this approach, you'll need to set up your macro for this event as shown in Using InfoConnect Events in Sessions Created at Runtime

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

 

 

2015 Attachmate