| VBA - Word and Excel Working Together | |||||||||||||||||||||||||
| Part 5: VBA Code Calling the Excel Server | |||||||||||||||||||||||||
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:
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:
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 |
|||||||||||||||||||||||||


