Dreams Do Come True - The MS Office Object Models - Part 2
In the kick off article I talked about several of the object models in the Microsoft Office 97. I mentioned numerous counts of properties and methods of the objects in those assorted models. Ive decided to focus on the largest and least documented of the suite. We are going to be discussing the Word Object Model.
But we have a new twist here. You see its now the year 2000. Everything that has a model or product name has a 2000 in it. Im Y2Kd out. I didnt work on any of those date fix projects because we did it right years ago. We were designing libraries for DOS C compilers that took the year into account.
However, this ever evolving Word object model has its own numbers that scare us. I mentioned before that the Word 8.0 model had 188 object and 3137 properties and methods. I was going to be really cool and update these numbers for the Word 9.0 model. But I give up. You see since the release of Office 2000 Microsoft has release a developers version of the product with new and improve documentation.
Using the documentation with Office 2000 premium version I counted almost 200 objects in the Word 9.0 model. But I now find is that my counts could be off. You see Word 2000 has added Click and Type formatting plus who knows what else and slid it in without any real documentation (Just like the DOS days). I couldnt find any references to it in the object models documentation that I had.
But it is now there in MSDN and Ill bet the NEW developer version is loaded with information. Right now the only information is for paragraph properties but no methods. I think this is one of those items we should ignore for now on the programming side because the tools are not there for us yet. But when Microsoft has developed this feature enough, we could have a whole new way of positioning objects in a document.
For now we will be teased and left to imagine what we could do with this feature while we type our documents. So let have some fun. Let me tell you a little about the Word Application Object. Word 8.0 it had 139 properties and methods plus two (it could be argued 3) events. But in the Word 9.0 application object there are now by my count 159 properties and methods and twelve events. So I think its safe to say that we have a vastly more improved MS Word on our hands.
So here is what Ive done. I created a project designed to fulfill the promise of showing how to replace the VB printer object with MS Word. Ive also created the debug print statements in the application objects events for Word. You can bring Word up again by running the program after you first comment out the line Objword.Quit in the Document_New( ) procedure. Then you can manipulate the visible version of MS Word and watch the events in the immediate window of Visual Basic. Which should display the following:
Creating a document with NORMAL.DOT
Word windowActivate event fired
Word NewDocument Event fired
running SQL Statement: SELECT Name, Price FROM Product
Word Document Change event fired
converting data to table
Word DocumentBeforePrint event fired
Word DocumentBeforeclose event fired
Word WindowDeactivate event fired
Word Document Change event fired
Word Quiting Event Fired
If your not using Word 2000 you can comment out all the events that start as Objword_ except the DocumentChange and Quit events and the program will work with Word 97.>
You can the find code in the demo at the end. Below is the an explaination of the code in the Sub Document_New( ) you will find it in the Form1 General Declarations section. This is the heart of printing with MS Word.
First we create a instance of Word:
' Create a new instance of the Word ' Note: I am NOT using GetObject or CreateObject ' on purpose. Set Objword = New Word.Application
Then we set our window state for the instance. We make it visible here for testing purposes. But it is should be set to be invisible in a finished application:
With Objword ' No explaination needed .WindowState = wdWindowStateNormal .Resize 300, 300 .Move 100, 100 ' toggle visible property of the object ' True during development and testing ' False when deployed because we don't want it seen ' also some speed reason too. .Visible = True
Next we create a Document to put our report in:
.Documents.Add "normal.dot", , , True
Now we are ready to open a database and get a record set:
sDBPath = App.Path & "\DemoData.mdb" ' Set up the ADO connection information sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Persist Security Info=False;" & _ "Data Source=" & sDBPath ' Make the ADO connection Conn.Open sConnection ' Set up the SQL Statement SQL = "SELECT Name, Price FROM Product" ' Make a recordset based on the SQL statement rsProducts.Open SQL, Conn, adOpenStatic, adLockReadOnly
Next we write that data in a delimited form to the ActiveDocument:
Do While Not rsProducts.EOF ' The simpler slower one line of text at a time ' notice it is delimited using vbTab constant ' This is faster but there is a faster method. sLine = rsProducts("Name") & vbTab & _ Format(rsProducts("Price"), "Currency") & vbCrLf ' Write a line of TAB delimited text .ActiveDocument.Range.InsertAfter sLine ' Move to the next record in the recordset rsProducts.MoveNext Loop '}-> Do While Not rsProducts.EOF
Now we do some Word Magic. Call the Range Objects ConvertToTable Method.
Then we print out the report to the default printer:
.ActiveDocument.PrintOut End With ' word
And we Quit word.
' This loop performs testing of printing status ' without this testing to delay the use of the Quit method ' you would get an error that would cause you heartache ' if word was invisible!!!!!! Do While Objword.BackgroundPrintingStatus > 0 Debug.Print Objword.BackgroundPrintingStatus Loop '}-> Do While Objword.BackgroundPrintingStatus > 0 Objword.Quit wdDoNotSaveChanges
That beats the heck out of using the printer object. As you can see while running the program that it is rather quick once your done accessing the database. All you are really waiting on is for Word to background print to the spooler and word closes. Leaving you to do other things while the printer outputs the report.
So now Ive given you a test program to study the Word application events and one of the quickest ways to build a report from a database. By studying this program you learn how to write text to the Word document in TAB delimited form. Which happens to be the default for the ConvertToTable method. You show really look the help over on this method as it has many options. It will amaze you.
So there you have it. I asked for it, and got it. More tools than I could ever have asked for. Let alone have time to write about. If you were counting, we only used about nine properties and methods from the Word 9.0 Object Model out of well over 3000 (bet you thought I was going to say 2000 right?).
So instead of surfing with an Internet Browser tonight. May I suggest surfing you windows system with the Object Browser?