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


