How to Call a Stored Procedure from Excel, Part 2

Hey, Howard:

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

--Samuel

I like the auto-fit also but was having trouble getting my spreadsheet to look good, and it was late and I was tired, so I punted. As for 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”)

Replace the line:

Pwd = Range("B3").Value

InputBox is a function that causes the program to prompt the user, with a dialog box, for a password. The dialog will have the caption Password. The InputBox function will pop up a VB input box and prompt for a 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), then click the Insert menu and 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, then select the txtPWD input box. Press F4 and set the PasswordChar property to an asterisk (*). This will cause any entries made into the input box to be represented as the asterisk character, thus hiding any user passwords from prying eyes.

The other thing you might want to do is to set defaults for the different prompts on the form. To do this, click the form element and press F4 to get the properties for that element. I set the VALUE property for my txtDSN text box to BAKERTEST, since this is the AS/400 I always access. I set the VALUE property for the txtUID text box to HARNER, since this is the user ID I always use. I set the default for the txtLIB field to HD1100PD, since this is the library I usually inquire about. You should set the defaults according to what you usually 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 when 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 that the program uses the Value property of the various text boxes on the form to get their contents and to concatenate the contents into a string.

Once the string is prepared, the program uses 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 is the RunProcDSPDBR procedure, which attempts to run the DSPDBR command for the supplied library and 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, because I covered it in my last article on running procedures. To place the code in 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, 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 that invokes the DSPDBR command for the supplied library/file and writes the output to file 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, go back to Sheet 1 in the Excel spreadsheet and right-click your toolbar to get a list of toolbars. Make sure the Forms toolbar is checked, then select a button from the Forms toolbar and place it on Sheet 1. Right-click the button and choose Assign Macro, then assign the button to Macro Button1_Click and 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, exit design mode and 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, will execute the sub routines, and will 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 copy of this spreadsheet from the Resources area of my Web site.

Howard F. Arner, Jr., is a writer and consultant with Client Server Development, and author of iSeries and AS/400 SQL at Work. Howard also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400's unique capabilities. You can purchase a copy of Howard's book or learn more about SQLThing at www.sqlthing.com. Send your SQL questions to harner@sqlthing.com or go to www.sqlthing.com to find out more about SQL on the iSeries.

Sponsored By ADVANCED SYSTEMS CONCEPTS

E-Mail Report and File Distribution for the iSeries & AS/400 - ESEND - - Saves paper and shipping costs - - Eliminates report distribution hassles - - Improves information access - - Archives important information - - Saves YOUR time - Get a Free Trial today! Read More