Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Automating Excel From a VB6 EXE

In the most common types of application that combine a VB6 EXE with Excel, the VB6 EXE is the initiating application. The VB6 EXE may either be the primary application automating Excel, or just a front loader for an Excel application yet to be started. In this section, we demonstrate a stripped-down example of a VB6 EXE application automating Excel. We then describe one of the most common real-world examples of a VB6 EXE combined with an Excel application, the front loader.

An Excel Automation Primer

Automating Excel from a VB6 EXE application is much simpler than using a VB6 ActiveX DLL from an Excel application. In fact we have already covered all of the points required to automate one application from another in Chapter 18 Controlling Other Office Applications. The process of controlling Excel from VB6 is no different from controlling an outside application from Excel.

We now demonstrate a very basic example of a VB6 EXE that automates Excel. The files for this example can be found on the CD in the \Concepts\Ch20Combining Excel and Visual Basic 6\AutomatingExcel folder. Creating an elaborate example using Excel would be time consuming and would tend to obscure the fundamentals. Instead we create a somewhat lighthearted example that uses an obscure function provided by Excel to convert whole numbers into Roman numerals. This is not as far from reality as it may seem at first. One of the primary reasons for automating Excel from VB6 is to utilize the powerful capabilities of Excel's calculation engine in a VB6 application.

To begin the project, start VB6 and select Standard EXE as the project type in the New Project dialog. In addition to the project itself, you will get a single VB6 form by default. This is all we need for this example. Rename your project ConvertToRoman, rename your form FRoman and then save your project. In the VB6 Project window, your project should now look like Figure 20-20.

Figure 20-20. The ConvertToRoman Project

The first step required in any project that intends to automate Excel is to set a reference to the Excel object library. Choose Project > References from the VB6 menu and select the Microsoft Excel X.0 Object Library, as shown in Figure 20-21, where X is the version number of the earliest version of Excel that you expect to automate with your VB6 application.

Figure 20-21. Referencing the Excel Object Library

The default form will be the only VB6 class we use in this example. Add three controls to the form: a TextBox control in which to enter the number to be converted to Roman numeral format, a CommandButton control to run the code that performs the conversion and a Label control in which to display the results. Our completed form is shown in Figure 20-22.

Figure 20-22. The Initial FRoman Form

We have not changed any of the default properties of our form or its controls. For a change of pace, we show how easy this is to do in code when the form loads. We do need to give our controls reasonable names, however, so we name the CommandButton cmdConvert, the TextBox txtConvert and the Label lblResult.

Now let's look at the code behind our form. All of this code will consist of event procedures for the form object and its controls. The first event procedure we'll examine is the Form_Load event shown in Listing 20-11.

Listing 20-11. The Form_Load Event Procedure

Private Sub Form_Load()
' Form properties
Me.BorderStyle = vbFixedDouble
Me.Caption = "Convert to Roman Numerals"
' CommandButton properties
cmdConvert.Caption = "Convert To Roman"
' TextBox properties
txtConvert.Alignment = vbRightJustify
txtConvert.MaxLength = 4
txtConvert.Text = "
' Label properties
lblResult.Alignment = vbCenter
lblResult.BackColor = &HE0E0E0
lblResult.BorderStyle = vbFixedSingle
lblResult.Caption = "
lblResult.Font.Name = "Courier"
End Sub

In this procedure we set all the properties of the form and its controls that are required to make the form user friendly. The result of displaying the form with only this procedure in place is shown in Figure 20-23. Compare this picture with the design-time version of the form shown in Figure 20-22.

Figure 20-23. The Display Appearance of the Form

The next thing we need to account for is the fact we can only convert whole numbers to Roman numerals. This means we need to prevent the user from entering anything into the txtConvert TextBox control other than the numerals 0 through 9. We accomplish this using the txtConvert_KeyPress event.

The KeyPress event allows you to examine each character the user tries to enter into a control before it actually gets there. This enables you to alter the character, for example by converting lowercase characters to uppercase characters, or cancel the character entirely, as we do for any character other than 0 through 9 in our example. The code for our txtConvert_KeyPress event is shown in Listing 20-12.

Listing 20-12. The txtConvert_KeyPress Event Procedure

Private Sub txtConvert_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case 8, 48 To 57
' Backspace and numerals 0 through 9
' these are all OK. Take no action.
Case Else
' No other characters are permitted.
KeyAscii = 0
End Select
End Sub

The KeyPress event passes us a KeyAscii argument that is the ASCII value of the character the user is trying to enter. You can modify that character by changing the value of the KeyAscii argument with the KeyPress event. To cancel a character you just change the KeyAscii argument to zero, which is the equivalent of Chr$(0) or vbNullChar, and therefore has the effect of entering nothing.

In our txtConvert_KeyPress event we use a Select Case statement to handle the incoming characters. This construct makes it easier to enable one whole series of characters and disable all others. We enable the Backspace character (to allow editing) and the numerals 0 through 9 by including their ASCII values in a Case expression that takes no action. All other characters are caught by the Case Else clause, which cancels them by setting the KeyAscii argument to 0. In this manner the KeyPress event ensures the value in the txtConvert TextBox will always be within the domain of values convertible to a Roman numeral.

Speaking of Excel, didn't we say the point of this exercise was to demonstrate how to automate Excel? Well now we have created everything required by our CommandButton's Click event procedure to use Excel to convert the contents of the txtConvert TextBox into an equivalent Roman numeral. The cmdConvert_Click event that accomplishes this feat is shown in Listing 20-13.

Listing 20-13. The cmdConvert_Click Event Procedure

Private Sub cmdConvert_Click()
Dim bError As Boolean
Dim xlApp As Excel.Application
Dim lConvert As Long
Dim sErrMsg As String
' Coerce the text box value into a long.
' Val is required in case it is empty.
lConvert = CLng(Val(txtConvert.Text))
' Don't do anything unless txtConvert contains
' a number greater than zero.
If lConvert > 0 Then
' The maximum number that can be converted
' to Roman numeral is 3999.
If lConvert <= 3999 Then
Set xlApp = New Excel.Application
lblResult.Caption = _
xlApp.WorksheetFunction.Roman(lConvert)
xlApp.Quit
Set xlApp = Nothing
Else
sErrMsg = "The maximum number that can be converted"
sErrMsg = sErrMsg & " to a Roman numeral is 3999."
bError = True
End If
Else
sErrMsg = "The minimum number that can be converted"
sErrMsg = sErrMsg & " to a Roman numeral is 1."
bError = True
End If
If bError Then
MsgBox sErrMsg, vbCritical, "Error"
txtConvert.SetFocus
txtConvert.SelStart = 0
txtConvert.SelLength = Len(txtConvert.Text)
End If
End Sub

Before we go into a detailed discussion of the cmdConvert_Click event procedure we present a working example of the form in Figure 20-24. If you have been following along with the example in your own copy of VB6, just press F5 to start the application. Because the form is the only object in the project it will display automatically. If you select the File > Make ConvertToRoman.exe menu you will generate a standalone executable file that can be run by just double-clicking it from Windows Explorer.

Figure 20-24. The Completed Convert to Roman Numerals Dialog

One of the first things you'll notice about the cmdConvert_Click event procedure is the vast majority of it has nothing to do with automating Excel. This is a very important point. It often requires a significant amount of preparation on the part of the automating application to ensure Excel will run without error when it is finally started.

The first thing the cmdConvert_Click event procedure does is to verify that the input from the txtConvert TextBox is within the bounds that can be handled by the Excel Roman function. The number to be converted to a Roman numeral must be greater than 0 and less than or equal to 3999. If the number that has been entered is out of bounds, an error message is displayed to the user and they are sent back to the txtConvert TextBox to try again.

If the number to be converted is within bounds, the event procedure creates an instance of Excel and goes to work. Using Excel to convert a whole number into a Roman numeral requires merely four lines of code (not counting variable declarations), but it illustrates several of the most important points to follow when automating Excel from VB6:

In addition, you cannot help but notice that you never saw Excel when running this example. This is because when an instance of Excel is created via automation it is invisible by default. In the Standard EXECreating a Front Loader for Your Excel Application section below, we demonstrate not only how to make an automated instance of Excel visible, but how to turn it over to the user and exit the VB6 application that started it completely, leaving the created instance of Excel running for the user.

Lastly, if you are expecting any add-ins or other auto-loaded files to be open when you create an instance of Excel via automation you will be disappointed. An instance of Excel created via automation does not load any add-ins that are selected in the Tools > Add-ins list nor does it load any files located in auto-start directories like XLStart. If your application depends on these files being loaded you will need to write the code to open them yourself.

Using a VB6 EXE Front Loader for Your Excel Application

A VB6 EXE front loader has the unique ability to examine the configuration of a user's computer without committing itself to any specific version of Excel or any other application. If your Excel application will be distributed widely and/or run on hardware and software over which you have no control, the front loader can verify that everything required to run your application is in place prior to starting Excel, running your application and exiting.