|
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
CON1.Close
Unload Me
Exit Sub
BadOpen:
'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)
Cmd1.Execute
Dim Stmt As String
Stmt = ""
Stmt = Stmt & "SELECT WHREFI, WHRELI, DBXATR, DBXTXT "
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
MakeHeaders LIBRARY, TABLE
Range("A5").Activate
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
Rs.MoveNext
Wend
Application.ScreenUpdating = True
Worksheets("Sheet2").PageSetup.PrintArea = "A1:D" & R + 4
Range("A1").Activate
Rs.Close
Set Rs = Nothing
Exit Sub
BadProblems:
'you got here becasue of an error!
Application.ScreenUpdating = True
MsgBox "An error occurred!"
End Sub
Public Sub MakeHeaders(LIBRARY, TABLE)
Worksheets("Sheet2").Activate
Cells.ClearContents
Cells.ClearFormats
Range("A1").Activate
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
Range("A4").Activate
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)
DSPDBR FILE(&LIB/&FIL) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/MYDSPDBR)
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:
CREATE PROCEDURE SQLBOOK.CLDSPDBR
( IN LIB CHARACTER(10),
IN FIL CHARACTER(10))
RESULT SETS 0
EXTERNAL NAME SQLBOOK.DSPDBRP
LANGUAGE CL
PARAMETER STYLE GENERAL
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. | 
