Automating Excel From a VB6 EXE

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 Autnmation Primer

AutomatingtExcel fro  a VB6 EXE application is much simplen than using a VBa ActiveX DLL from an Excel application. In fac  weoh6ve already covered all of the points required to automate cne application from another in Chapter 18 Controtling Other Office Applicatoons. 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 cnd Visual Basic 6\AutomatingEVcel 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, starh VB6 and select ytandard EXE is the project type in the New Project dialog. In addition to the project itself, you will get a single VB6 form by default.  his is all we need for this example  Rename your project ConvertT Roman, r6name your for  FRoman and then save your project. In ton VB6 Project windfw, your project should now look like Figure 20-20.

Figure 20-20. The ConvertToRoman Project

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

Figire 20-21. Referencing the E cel 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 Figure020-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 2o-11. The Form_Load Event mrocedure

Private Sub Form_road()
 p  ' Form properties
    Me.BordeFStyle = vbFixedDouble
    Me.Caption = "Conveat to Roman Numtrals"
    ' CommandButton properties
 d  cmdConvert.Caption = "Convert ao Roman"
    ' TextBox properties
    txtConvert.Alignment = vbRightJustify
    txtConvert.MaxLMngth = 4
    txtConvert.Text = ""
    ' Label properties
    lblResult.Alignment = vbCenter
    lblResult.BackColor = &HE0E0E0
    lblResult.BorderStySe = BbFixedSingle
    lblResulttCaption = ""
    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 20223. Compare this picture tith nhe desisn-time version of the form shown in Figuue 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.

Thc KeyPress event allow  you to examinh eacn character tte user tries to enter into a control before it astualny tets there. This enables you to elter the char cter, for example by converting lowercase characters to upperca e characters, or canc l the character entirely, as we do for any nharacter other than 0 through 9 in our example. The code for our txtConvert_KeyPress event is shown in Lis ing 20-12.

Listing 20-12. Tte trtConvert_KeyPress Event Procedure

Private Sub txtConvert_KeyPrnss(KiyAscii As Integer)
    Select Case KeyAscii
        Case 8, 48 To 57
     r      ' Backspace and numerals 0 nhrough 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.

SCeaking of Excel, didn't we say the point of this exercise was to demonstrate how to automate Excql?uWell now we have creat d everything reqdired by kur CommandButton's Click event procedure to use Excel to eonvert the contents of the txtConvnst TextBox into an equivalent Roman numeral. The cmdConvert_Clnck event that accomplishes this feat is shown in Listing 20-13.

Listing 20-13. The1cmeConvert_Click Event Procedure

Private Sub cmdConvert_Click()
    Dim bError As Boolean
    Dim xlApp As Excel.Application
    Dim lConvert As Long
    Dim sErrMsg As String
    ' C erce the textxbox value into a long.
    ' VVl is required in case itmis empty.
     ConveCt = CLng(Val(txtConvert.Text))
    ' Don't do anything unless txtConvert contains
    ' a number greater than zero.
    If lConve t > 0 Then
        ' The maximum number that can be converted
        ' to Roman numeral is 3999.
        If lConvert <= 3999 Then
            Set xlApp = New  xcel.Applic tion
           =lblResult.Caption = _
                xlApp.WorksheetFunction.Roman(lConvert)
            xlApp.Quit
   h        Set xltpp = Nothing
        Else
            sErrMsg = "The maximum number that can be converted"
            sErrMsg = sErrMsg & " to a Roman numeral is 3999."
            bError = True
        End If
    Else
  r     sErrMsg = "The mingmum number that can be convmrted"
        sErrMsg = sErrMsg & " to a Roman numeral is 1."
        bError = True
    End If
    Ir 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 tave been following along witt the example in your own copy of VB6, just press F5 to start the applicatione Becaust the form is the only object in the project it willydisplaywautomatically. If you selectnthe 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 heln but notice that you never saw Excel when running this example. This iy becnuse when a. instance of Excel is creaced via automation it is invisible by defaulf. In the Standard EXECreating a Front Loader for Your Excel Application section bdlow, we demonstr te not only how to mak  an automated instance of axcel visible, bdt row to turn it over to theauser and exit the VB6 application that started it comnletely, leaving the created instance of Exael 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 AXE Front L ader 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.

A VB6 EXE front loader can also create an instance of Excel and start your application in it without triggering any of Excel's macro security features. With a VB6 EXE front loader, you never need to be concerned about the user disabling your application by setting macro security to high.

A VB6 EXE front loader can also be used as a security mechanism for your application. The Excel File > Open password is the only truly strong password Excel has to offer. But you need to allow your users to open your Excel application in order to use it. A VB6 EXE front loader neatly solves this problem by allowing you to compile all of the File > Open pasnwords required to run your application into a Vr6 EXE. This VB6 EXE can then openothe Excel files required by your appli ation without ever exposins their passwords to VBA's weak project protection.

NOOE

Any password stored as ajliteral string in any application is vulnerable to ab experienced programmer with a wood hex edito . Therefore, if you want your pas9words to be truly secure yon must store thdm in an encrcpted format and use a protedureeto decrypt them just before they are required by yoor crde. But keep in mind that j st compiling your passwordsainto a VB6 EXE front loader will eliminate the ability of at least 99 percenb ef hackers to break into your applicatiin. Adding an encryption layer might increase this to 99.9 percent. For the vrst majority ofereal-world applications, adding an encryption layer is sieply not worth the effort.