Enter Data Using an Excel Form

This sample shows how to use an Excel form to enter data into a terminal screen. When the login button is clicked, the macro creates a session, enters the User name and Password into the program, and then navigates to the screen that has the data form. When the Enter Data button is pressed, the macro checks to make sure the user has logged in and displays a message to log in if required. If the user has logged in, the macro enters the data from the user form into the program.   

 

This sample applies only to IBM terminals

To run this sample

  1. 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. Create a User Form with the following controls:
    Properties Names
    Text Boxes
        
       
    txtUserName
    txtPassword 
    txtProject
    txtMember
    List Box lstGroup
    Check Box optType
    Command Buttons cmdEnterData, cmdLogin
  2. Copy the following code into the form code window and then place the cursor in UserForm_Initialize and press F5 to initialize the form.
    Enter data from an Excel user form
    Copy Code
    Dim screen As Attachmate_Reflection_Objects_Emulation_IbmHosts.ibmScreen
    Private Sub UserForm_Initialize()
      
        'Empty ProjectTextBox
        txtUserName.Value = ""
     
        'Empty TypeRadioButtons
        txtPassword.Value = ""
          
        'Empty ProjectTextBox
        txtProject.Value = ""
          
        'Empty MemberTextBox
        txtMember.Value = ""
     
        'Empty GroupListBox
        lstGroup.Clear
         
        With lstGroup
            .AddItem "Acct"
            .AddItem "Eng"
            .AddItem "Mktng"
        End With
      
        lstGroup.Value = "Acct"
        optType.Value = "True"
      
    End Sub
    Private Sub cmdLogin_Click()
        '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 path As String
        Dim rCode As ReturnCode
     
        '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
        Set screen = terminal.screen
      
        'Enter the password data from the form
        rCode = screen.PutText2(txtUserName.Value, 20, 16)
        rCode = screen.PutText2(txtPassword.Value, 21, 16)
      
        'navigate to the screen you want to screen scrape
        rCode = screen.SendControlKey(ControlKeyCode_Transmit)
        rCode = screen.WaitForHostSettle(3000, 2000)
        rCode = screen.SendKeys("ISPF")
        rCode = screen.SendControlKey(ControlKeyCode_Transmit)
        rCode = screen.SendKeys("1")
        rCode = screen.SendControlKey(ControlKeyCode_Transmit)
      
        'Clear the form data
        UserForm_Initialize
    End Sub
    Private Sub cmdEnterData_Click()
        Dim project, projectType, group, member  As String
        Dim rCode As ReturnCode
        Dim app As Attachmate_Reflection_Objects_Framework.ApplicationObject
      
        'If reflection isn't open, exit and display a message to log in.
        On Error GoTo ErrorHandler
        Set app = GetObject("InfoConnect Workspace")
      
        project = txtProject.Value
        group = lstGroup.Value
        member = txtMember.Value
      
        If optType.Value = "True" Then
            projectType = "New"
            Else
            projectType = "Funded"
        End If
      
        'Put the text for each value into the appropriate field
        rCode = screen.PutText2(project, 5, 18)
        rCode = screen.PutText2(group, 6, 18)
        rCode = screen.PutText2(projectType, 7, 18)
        rCode = screen.PutText2(member, 8, 18)
       
        'put in the command to enter the data and go to the next screen
        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)
      
        'Clear the form data
        UserForm_Initialize
      
        'Exit to avoid running the error handler when there is no error
        Exit Sub
        ErrorHandler:
            MsgBox "You need to log in"
        Exit Sub
    End Sub
    
  3. Log in using any credentials, wait for Reflection to open, and enter some data.
        

Concepts

The UserForm_Initialize() Sub creates the form displayed at the top of this article. When the Login button is clicked, the LoginButton_Click() creates a new Reflection session as explained in Create a Session From a Microsoft Excel Macro. Then it enters the user name and password from the form into the appropriate fields on the first screen, using the ibmScreen.PutText2 method. Finally, 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

When the Enter Data button is clicked, the EnterDataButton_Click() Sub checks to make sure the user has logged in. Then it assigns the form field values to local variables and uses the PutText2 method to put the text from each form field into the appropriate program field. After the data is entered using the SendControlKey method, the user form is initialized again.

 

 

See Also

 

 


2015 Attachmate