VBA - Word and Excel Working Together

Visual Basic

Filed In:

Visual Basic

VBA - Word and Excel Working Together

Part 5: VBA Code Calling the Excel Server

More of this Feature

• Part 1: Word and Excel Working Together
• Part 2: Designing the System
• Part 3: The Word Document VBA Code
• Part 4: The Word Form
• Part 6: The Final Result

Join the Discussion

Have you created a system using VBA Automation ?
Tell us about it!

Related Resources

• Beginning Visual Basic
• Visual Basic 6

From Other Guides

• About Business Software

Elsewhere on the Web

• An Excel VBA book
From the great publisher, APress
• Updates from Microsoft
(Browse to Office 2000)

In a way, all of this has been preparation for the actual subject of this article: using Excel functions inside Word's VBA programming. The subroutines that handle the Click event for the bottom two command buttons are where this takes place. First here's the code, then I'll explain what it means:

Private Sub cmdUpdtTime_Click()
    HideListBoxes
    Dim myWB As Excel.Workbook
    Set myWB = _
        GetObject("{your path}\AboutWordExcel.xls")
    Selection.GoTo _
        What:=wdGoToBookmark, _
        Name:="DaysOverdue"
    Selection.TypeText _
        (myWB.Sheets("PayHist").Range("Payment_Overdue"))
    Set myWB = Nothing
End Sub

After the listboxes are hidden (just a user interface enhancement, not really required), the VBA code declares an Excel workbook object, myWB. If you just entered this in your code without doing anything else, it would generate an error because Word doesn't recognize Excel.Workbook as a legitimate object. To use objects from other systems, you have to add references to them. You do this using the Tools > References menu option in the Visual Basic editor. Here's how the Excel objects were added for this example:

Adding the Excel object library reference

Once the object is declared, it must be "instantiated" using the Set statement. Be sure to replace {your path} in the code shown with the actual path on your computer. The next statement positions the Word cursor at the correct bookmark and then the TypeText method in Word uses content from the cell named Payment_Overdue in the PayHist worksheet. Since a copy of the Excel workbook has been made available to the Word program in the myWB object, we get rid of the object again with the final Set myWB = Nothing statement.

Next page > The Final Result > Page 1, 2, 3, 4 5, 6

Guide since 2002

Dan Mabbutt
Visual Basic Guide

Sign up for my Newsletter

Explore Visual Basic

Must Reads