Using User Forms for Input in Excel

This article covers how to create a user form and then write a macro to call a stored procedure on the iSeries and return data to a spreadsheet.

Dear Howard,

I enjoyed your article, 'How to Call a Stored Procedure from Excel'.  The only concern that I have is providing the password to retrieve the data. I'm not sure how but can't the VB code be changed so that the password for the system connection is prompted?  My preference is to use the 'Worksheets().Columns().Autofit' for column resizing and to use the 'Worksheets().Range().Select, ActiveWindow.FreezePanes = True' for locking headings in the spreadsheet.  Thanks for the article. ~Samuel

Dear Samuel,

I like the autofit also, but was having trouble getting my spreadsheet to look good and it was late and I was tired so I punted. As to the password, I agree that it should not be on the sheet.  What you could do is in the MACRO add the following:

Pwd = InputBox("Please Enter Your Password", “Password”)
To replace the line 
Pwd = Range("B3").Value

InputBox is a function that will cause the program to prompt the user with a dialog asking them to “Please Enter You Password”. The dialog will have the caption “Password”. The InputBox function will pop up a VB input box and prompt for password and return it to the Pwd variable.  Then you could remove it from the sheet and thus prompt the user for the password dynamically.

More on security

The only problem with the InputBox function is that it displays the password in clear text. To get around this you can use a form for data entry. To do this, start the macro editor, (Visual Basic), and then click the Insert menu and then click User Form.

Press F4 to change the properties of the form and rename the form usrInput. Then proceed to create five text boxes on the form. Once the text boxes are created, use the F4 key to adjust their properties and name them txtDSN, txtUID, txtPWD, txtLIB and txtFN. Next, add labels to the form to make data entry easier and then select the txtPWD input box. Press F4 and set the PasswordChar property to *. This will cause any entries made into the input box to be represented as the * character, thus hiding any users passwords from prying eyes.

Your form should look something like this:

Now, the other thing you might want to do is set defaults for the different prompts on the form. To do this, click the form element and then press F4 to get the properties for that element. I set the VALUE property for my txtDSN text box to BAKERTEST as this is the AS/400 I most always access. I set the VALUE property for the txtUID text box to HARNER as this is the user ID that I most always use. I set the default for the txtLIB field to HD1100PD as this is the library I most always inquire about. You should set the defaults according to what you most always prompt to.

Next, you need to add code to the GO button that causes the procedure to be called and the information in the form to be passed to the procedure. To do this, double click the go button which should reveal the Click event for the command button. Place the following code into the click event:

Dim DsnSTR As String
Dim CON1 As New ADODB.Connection
DsnSTR = "DSN=" & txtODBC.Value & _
    ";UID=" & txtUID.Value & _
    ";PWD=" & txtPWD.Value
'Open the connection
On Error GoTo BadOpen
CON1.Open DsnSTR
On Error GoTo 0
ThisWorkbook.RunProcDSPDBR txtLib.Value, txtFN.Value, CON1
Unload Me
Exit Sub
'handle the errors
MsgBox "Opening the connection cause the following error: " _
	& vbLf & CON1.Errors(0).Description


The code above is invoked once the user fills in the form and clicks the Go button. The program first puts together a string that will hold the sign-on information for the ODBC data source that you will use to communicate with your iSeries. Notice the program uses the Value property of the various text boxes on the form to get their contents and concatenate the contents into a string. Once the string is prepared, the program use On Error to set an error handler and then attempts to open a connection to the iSeries using an ADO Connection object. ADO lets you communicate with the iSeries using ODBC. To make sure your macro will run properly, ensure that you have referenced ADO by choosing the Tools and then References menu items and select Microsoft Active X Data Objects 2.x Library from the supplied list of objects where the x is any number from 0 to 7. I recommend that you use ADO 2.7. If ADO 2.7 is not listed in your references dialog, you can go to www.microsoft.com/Data to get the latest version of the Microsoft Data Access Components.

If the connection is opened successfully, processing will continue to the next line of code which tells the program to stop processing if it receives an error. The program then calls a procedure called RunProcDSPDBR and passes it the value of the txtLib text box, the value of the txtFN text box and the connection object Con1. Once the procedure finishes processing, the program closes the CON1 connection and then unloads the form to return processing to the spreadsheet.

The RunProcDSPDBR Procedure

The following code needs to be added to the form. This code is the RunProcDSPDBR procedure which attempts to run the DSPDBR command for the supplied library/file over the supplied ADO connection. It also contains the MakeHeader subroutine which draws the headers for the returned data in the spreadsheet. I won’t go into how this code works, as I covered it in my last article on running procedures.  To place the code into the form, simply double click the form to get into the forms code window, press CTRL+End to get to the end of the code in the code window and then paste this code into the window.

Public Sub RunProcDSPDBR(LIBRARY, TABLE, CON1 As ADODB.Connection)
'dim the objects we need to communicate
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset
'turn off the screen so excel is faster
Application.ScreenUpdating = False
On Error GoTo BadProblems
Cmd1.ActiveConnection = CON1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "Call SQLBOOK.CLDSPDBR (?,?)"
Cmd1.Parameters.Append Cmd1.CreateParameter("LIB", adChar, adParamInput, 10, LIBRARY)
Cmd1.Parameters.Append Cmd1.CreateParameter("FIL", adChar, adParamInput, 10, TABLE)
Dim Stmt As String
Stmt = ""
Stmt = Stmt & " FROM qtemp.mydspdbr INNER JOIN "
Stmt = Stmt & "     qsys.QADBXFIL ON"
Stmt = Stmt & "        (WHREFI = DBXFIL AND WHRELI = DBXLIB)"
Stmt = Stmt & " ORDER BY 1"
Cmd1.CommandText = Stmt
Rs.CursorLocation = adUseClient
Rs.CacheSize = 100
Rs.Open Cmd1
R = 0
While Not Rs.EOF
  ActiveCell.Offset(R, 0).Font.Size = 10
  ActiveCell.Offset(R, 0).Font.Bold = True
  ActiveCell.Offset(R, 0).Value = Rs.Fields("WHRELI").Value
  ActiveCell.Offset(R, 1).Value = Rs.Fields("WHREFI").Value
  ActiveCell.Offset(R, 2).Value = Rs.Fields("DBXATR").Value
  ActiveCell.Offset(R, 3).Value = Rs.Fields("DBXTXT").Value
  R = R + 1
Application.ScreenUpdating = True
Worksheets("Sheet2").PageSetup.PrintArea = "A1:D" & R + 4
Set Rs = Nothing
Exit Sub 
'you got here becasue of an error!
Application.ScreenUpdating = True
MsgBox "An error occurred!"
End Sub
Public Sub MakeHeaders(LIBRARY, TABLE)
Range("A1").ColumnWidth = 12
Range("B1").ColumnWidth = 12
Range("C1").ColumnWidth = 6
Range("D1").ColumnWidth = 52
Range("A1").Value = "Relations Listing"
Range("A1").Font.Size = 12
Range("A1").Font.Bold = True
Range("A1", "D1").MergeCells = True
Range("A2").Value = LIBRARY & "/" & TABLE
Range("A2").Font.Size = 12
Range("A2").Font.Bold = True
Range("A2", "D2").MergeCells = True
R = 0
ActiveCell.Offset(R, 0).Font.Size = 10
ActiveCell.Offset(R, 0).Font.Bold = True
ActiveCell.Offset(R, 0).Font.Underline = True
ActiveCell.Offset(R, 0).Value = "Library"
ActiveCell.Offset(R, 1).Font.Size = 10
ActiveCell.Offset(R, 1).Font.Bold = True
ActiveCell.Offset(R, 1).Font.Underline = True
ActiveCell.Offset(R, 1).Value = "File Name"
ActiveCell.Offset(R, 2).Font.Size = 10
ActiveCell.Offset(R, 2).Font.Bold = True
ActiveCell.Offset(R, 2).Font.Underline = True
ActiveCell.Offset(R, 2).Value = "Type"
ActiveCell.Offset(R, 3).Font.Size = 10
ActiveCell.Offset(R, 3).Font.Bold = True
ActiveCell.Offset(R, 3).Font.Underline = True
ActiveCell.Offset(R, 3).Value = "Description"
End Sub

Adding the procedure to your iSeries

This program calls a CL program which invokes DSPDBR command for the supplied library/file and writes the output to QTEMP/MYDSPDBR. To create the procedure on your iSeries, create a source physical file and input the following CL program then save the source and compile the program to an object named DSPDBRP in the SQLBOOK library.

PGM         PARM(&LIB &FIL)
DCL         VAR(&LIB) TYPE(*CHAR) LEN(10)
DCL         VAR(&FIL) TYPE(*CHAR) LEN(10)

Next, start an SQL session using my SQLThing tool or Client Access Express and issue the following create procedure statement to define the procedure on your iSeries:


Now that the procedure program has been created and the procedure declared to your iSeries, you are ready to attempt to run the macro. If you used a library other than SQLBOOK, you will need to change all of the references to the SQLBOOK library to point to the library where you created the procedure.

Adding a button to Invoke the Form

To make the dialog box display, you need to go back to Sheet1 in the excel spreadsheet and right click on your toolbar to get a list of toolbars. Make sure the Forms toolbar is checked and then select a button from the Forms toolbar and place it onto Sheet 1. Right click on the button and choose Assign Macro then assign the button to Macro Button1_Click and then choose the Edit button to edit the Button1_Click macro. In the VB Editor, type usrInput.Show 1. This causes the usrInput form to be shown modally. Modal forms interrupt processing until they are unloaded or hidden.

To run the spreadsheet, simply exist design mode and then click the button on Sheet 1. This should cause the dialog box to be displayed. Enter information about an ODBC data source, your user ID and password and the library and file you wish to examine. Once you press the GO! Button, the program will connect, execute the sub routines and fill in the DSPDBR data into Sheet 2 of the spreadsheet.

If you do not want to mess with inputting the code, you can download a fully functional copy of this spreadsheet from this link.