Enter Data Using an Excel Form

In This Topic

This sample shows how to use an Excel form to enter data into a terminal screen.

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

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

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.

To run this sample

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

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

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("Reflection 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) 'This demo waits 2 seconds so you can see the data after it is entered. Remove this code if you use this sample for your projects app.Wait (2000) '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

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("Reflection 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)

    'This demo waits 2 seconds so you can see the data after it is entered. Remove this code if you use this sample for your projects
    app.Wait (2000)
   
    '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

After the form opens, enter any text in the User name and Password fields and click Log In. (The demo program accepts any credentials.)
The form opens Reflection, logs in, and navigates to the Browse - Enter Panel screen.
After the Browse - Enter Panel screen is appears, enter some text in the Project and Member fields and select an item in the Group list.
Click Enter Data.
The form enters the data on the screen.

Note: This sample includes a Wait method to make it easier to see the data that is entered in the demo. If you want to use it as a starting point for you project, remove the following line:

'This demo waits 2 seconds so you can see the data after it is entered. Remove this code if you use this sample for your projects
app.Wait (2000)

Concepts

The UserForm_Initialize() Sub creates the form displayed at the top of this article. When the Login button is clicked, the LoginButton_Click() Sub 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.

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.

Quickstart

Create a Session From a Microsoft Excel Macro

Navigating Through Open Systems Sessions

Concepts

Handling Asynchronous Behavior

Using Reflection Events