1. Home
  2. Computing & Technology
  3. 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
 
 Elsewhere on the Web
• An Excel VBA book
From the great publisher, APress

• Updates from Microsoft
(Browse to Office 2000)

 

In a way, everything in this lesson so far has been preparation for the actual subject of this page: 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 an object without this step. To use objects from other systems, you also 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. This makes a copy of the object for the use of this program. 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
Explore Visual Basic
About.com Special Features

Holiday Central

What to eat, where to go, fun things to do and how to save money on the perfect gifts. More >

Family Tech Center

Stay connected and entertained with reviews on tips on the latest HDTVs, cellphones and more. More >

  1. Home
  2. Computing & Technology
  3. Visual Basic

©2009 About.com, a part of The New York Times Company.

All rights reserved.