The Dynamic Duo -- Word and Excel can work in tandem to boost your productivity.
The secret is Microsoft's ActiveX technology.
By Karen KenworthyDownload PWVBA.ZIP, as mentioned in this article.
Bradshaw needed Swann, Kareem needed Magic, and Doc Gooden needed Carter. History shows that it takes teamwork to reach the pinnacle of success. The same can be said for the latest Windows applications. When used in tandem, they offer capabilities that are far greater than the sum of their parts.
To test this team concept, I decided to make Microsoft Excel 7.0 and Word 7.0 work hand-in-hand. The glue that holds my team together is Microsoft ActiveX Scripting, formerly known as OLE Automation. This feature, built into many popular Windows applications, allows one piece of software to control another.
My team's game plan was written in a macro language, Visual Basic, Applications Edition (VBA). Thanks to ActiveX Scripting/OLE Automation, VBA lets Excel give orders to Word (or any other OLE Automation server).
According to my plan, Excel launches Word, then causes Word to open an existing document. Next, Excel tells Word to edit the document by adding text found in one of Excel's spreadsheet cells. Finally, Excel orders Word to print the document, then exit.
If you're an armchair quarterback and would like to play along at home, start by creating the document that Word will open, edit and print. To do this, launch Word, create a new document and type the text "YTD Sales:" on the first line. Save the document under the name PWTEST.DOC in Word's default document directory (the one displayed each time you select Open from Word's File menu).
Now, let's exit Word and get Excel ready to play. First, run Excel and create a new workbook. Then add a module sheet to the workbook, by selecting Macro/Module from Excel's Insert menu. This module sheet will contain the VBA statements that control Word. In the new blank module sheet, type the lines shown in the sidebar So Happy Together.
When you've finished, select the first spreadsheet in your new workbook, which should be named "Sheet1," type a number (for this example, 1,000) into cell A1, and press Enter. Next, place the cursor back in cell A1, and choose Cells from Excel's Format menu. When the Category: list appears, choose one of the Currency formats. Your number will be displayed with the appropriate dollar sign, decimal point and comma(s). Once you've done that, save your new workbook, using any name you like.
Now let's put our game plan to the test. Click on the Module1 tab (located near the bottom left side of the screen). Next, click anywhere inside the subroutine's text. Finally, click on the VBA toolbar's Run button (the one with the triangle-shaped green arrow pointing to the right). You should soon see a page emerging from your printer with a line reading "YTD Sales: $1,000" (or whatever number you entered into cell A1).
When the macro is running, Word opens, edits and prints a document, then exits invisibly. This entire process takes place behind the screen, er, scene, allowing you to create great teams, consisting of several applications, without scaring the user with the sight of several on-screen windows being eerily updated by an unseen hand.
Having everything happen as if by magic may satisfy most people. But I know you aren't happy until you understand what's going on behind the curtain. So let's review the VBA subroutine line by line and see how it works.
As you can see in the sidebar So Happy Together, it all starts with the line Sub RunWord(). This line tells Excel we're about to write a Subroutine named RunWord. The empty pair of parentheses at the end of the line indicates the subroutine expects no parameters.
The next line defines our subroutine's only variable, WordObj. According to its Dim statement, WordObj will hold something called an Object. In programmer-speak, an object is a collection of variables (places where data is stored) and procedures (instructions that process data). If you think that sounds a lot like a computer program, you're right. In fact, a particular running copy of an application such as Word is an object (though an object can be much smaller than a complete application). In VBA, Object variables hold pointers to objects. In our case, we are going to use it to store a pointer to a running copy of Word.
That brings us to the third line in our subroutine. This statement calls VBA's CreateObject function and stores the data it returns in our variable named WordObj. "Word.Basic" (the parameter we pass to CreateObject) is called an object class. It consists of two parts, separated by a period. The first part specifies an application (in our case, Word). The second part, Basic, lets us access Word's macro language, WordBasic.
Through WordBasic commands, we can open, edit and print Word documents, among other actions. As the term implies, CreateObject creates an object by launching a copy of the indicated program. It then returns a pointer to the object, which we store in our object variable WordObj and use each time we tell our copy of Word what to do.
That happens in lines 4, 5, 6 and 7. Each of these lines begins with the name of our object variable, WordObj, followed by a period. This is followed by the name of a method. In line 4, WordObj.FileOpen "PWTEST.DOC" executes WordBasic's FileOpen command, passing it the name of our test document (PWTEST.DOC). As you've likely guessed, FileOpen causes Word to open the specified document. Without a path name, the document is assumed to be in Word's default document directory. Line 5 is even simpler: It executes WordBasic's EndOfDocument command, which moves Word's text-insertion cursor to the document's end.
Line 6 uses WordBasic's Insert command to add text at the text-insertion cursor's current location. The text added is passed to Insert as a parameter. In our subroutine, the text is taken from cell A1 of the spreadsheet called "Sheet1." Sheets and Range are both special objects within Excel VBA, and Text is the name of the variable (often called a property) holding data inside a Range object. Hence, Sheets ("Sheet1").Range ("A1").Text is the data displayed in cell A1 of Sheet1.
Line 7 gives Word its last command, FilePrint. As you might imagine, this WordBasic command causes Word to print the document that it's editing. The parameter passed to FilePrint, 0, tells Word to temporarily disable its Background Printing feature while printing this document.
That's an important command. Background printing is a special feature of Word that lets you resume editing immediately after asking Word to print. Unfortunately, when this feature is enabled, our subroutine ends immediately, causing Word to exit before background printing can be completed. That's why we disable Background Printing.
Our subroutine's last line, End Sub, tells VBA where our subroutine ends. Once this line is reached, all our variables are discarded. This, in turn, causes our copy of Word to close.
The Excel workbook and Word document files we've been discussing can be found in the file PWVBA.ZIP. Download it here. Once you've copied the files (or re-created them using the information presented here), don't hesitate to experiment.
Our subroutine uses only four WordBasic commands. There are dozens more that, among other things, let a VBA subroutine perform all of the sophisticated editing, formatting and mail-merge functions that you're used to doing manually in Word. Our subroutine also doesn't use the many optional parameters available when executing commands such as FilePrint and FileOpen.
Other languages, including Microsoft's Visual Basic development system, can be used to create ActiveX Scripting/OLE Automation clients. This lets you build complete standalone applications that control Word, Excel and more. With enough imagination and the right information, there's almost no limit to what your team of applications can do.
Contributing Editor Karen Kenworthy is the author of Visual Basic for Applications, Revealed! (Prima Publishing, 1994) and the manager of WINDOWS Magazine forums on America Online and CompuServe. Contact Karen in the "Power Windows" topic of these areas. Karen Kenworthy's e-mail ID is: firstname.lastname@example.org