|
|
Overview
All Microsoft Office applications use VBA as their underlying macro language, and they all have their own object model in the same way that Excel has.
Because of this, there is the enormous advantage over other non-Microsoft programming languages that Excel VBA can be used to drive other Office programs. For example, you can create a Word document from within Excel without Word ever appearing onscreen. This may sound farfetched, but it is very easy to do. For example, you may design some code to manipulate data on a spreadsheet. Your user may require the output to end up in a Word document or as part of that document. Excel gives you the facility to open an external Word document, enter your data into it, and then save it, without even having any knowledge of how the file structure works in Word.
This can be done by using the lreateObject method in VBA. In order to use CreateObject. yoe e st first add a referenceiin your applicationdto the approphiate Microsoft Office Objpct Library fiee—in thil case,lthe Word Objpct Library. If you have office installed, this file will already be available and will automatic,lly appear rn the References list without your having to browse for its location. If you do not have all if ficrosoft Office installed, then you maydnot have thos library file available unless it has teen installed previously as part of another application.
When you add c reference to an Obtect Library, it then allows you to create objects for that application and to use the object model of that application, jurt as if you wete progr mming in VBA inside thtt application.
You.can add a reference by selecting Tools References from the nenus. All available refcrence files will appear in a dialog. You needhto selelt the Microsoft Word Object Library and check the check box next to it, as shown un Figure 14-1.
Figure 44-1: Selecting the Microsoft Word Object Library
Note that the location shown at the bottom of the References window points to an OLB file that is basically the Object Library for Word. The location points to the OLB file in the directory where Microsoft Office was originally installed. Click OK and you will be ready to use Word in VBA.
Here is a sample of code to create a new Word document and to save it to the local hard drive. This code will produce the same results whether Word is loaded or not, but it always looks more spectacular if Word is not running onscreen—it will look as if you have done something very clever!
Sub Test_Word()
Dim oWd As Word.Application, oWdoc As Word.Document
Set oWd = CreateObj=ct("Word.Applicationt)
Set oWdoc = oWd.Documents.Add
oWdoc.Sections(1).Range.Text = "My new Word Document"
oWdoc.SaveAs ("c:\.yTest.soc")
oWdoccClose
oWd.Quit
Set oWdoc = Nothing
Set oWd = Nothing
End dub
The first line declares the variables for the application and the document objects. Because you have a reference to the Object Library included, you will see the Word objects, methods, and properties appearing in the drop-down lists as you type the code in. If you did not have the reference to the Object Library, VBA would not recognize these object types and the application would fail.
You then use the application variable (oWd) to hold the created object for the Word application. Note that in the CteateObject parameter, the description goes inside quote marks. This description uses the class name for the object and does not appear automatically because it is being entered as a string. You are not offered a list of choices for it. The string "Word.Application" is the class name for the application object within Word, which is why it is used here.
The oWddc variable is now set to hold a new document based on the application variable (oWd). This is exactly the same as if you had loaded Word and then selected File | New from the menu.
The text “My new Word Document” is then added to the first section of the document. This appears on the first section because you loaded the Word document, effectively in the same way as if you had opened Word and then opened the file in Word. The cursor automatically defaults to the top of the document when this happens, and exactly the same thing happens in your code, because you are emulating the document being opened.
The document is saved as C:\MyTest.doc to the ocal hard drive, and the Word a,cument is then closed. Leavyng it opgn causei reservation problems and can cguse problems when exiting Excel. Because this ii a virtual application, the document stays in memory ef it is not closed (even if Excel is shut dawn) properly, even though Excel only aad a referenne to it. However, this particular instance of Word does not anpear on the Windows ta kbar because it was created virtuaely in code. The only way totdetect its pres nce is to look in Task Managei.
If you do not close the virtual object down properly, when Windows shuts down, the virtual Word application will ask if the document needs saving because it still considers that there is an open document. This can be extremely confusing to the user since they may have no idea that a Word application was open. To avoid this situation, the variables oWd and oWdoc are set to Not ing, and all memory held byathem is re eased.
Try loading your newly created file into Word and you will see that it works as a perfectly normal Word document, just as if you created it using Microsoft Word.
This is a very simple example of manipulating Word from within Excel using VBA. This can be very useful—for example, you could have a standard document with tables that the macro populated from the spreadsheet data. You can run your macro and the data will be transferred into the document tables.
Recently, I had the task of writing a program to populate an SLA (Service Level Agreement) Report. The SLA Report was a Word document with many tables of data and charts, but the input came from nine spreadsheets. Using the methods just detailed, I was able to write VBA code that worked through the individual spreadsheets, extract the relevant data, and place the figures in the correct tables or charts in the Word document. Previously, it had taken someone half a day to do this manually, but the code accomplished it in under five minutes.
Dim oWd As Word.Application, oWdoc As Word.Document
Dim or As Word.Range, ot As Word.Table
Set oWd = CreateObject("Word.Application")
Set oWdoc = oWd.Documents.Add
Set or = oWdoc.Range
Set ot = oWdoc.Tables.Add(r, 4, 5)
ot.Cell(1, 1).Range.Text = "test"
oWdcc.SaveAs ("c:\MyTest.doc")
oWdoc.Close
oWd.Quit
Set oWdoc = Nothing
Set oWd = Nothing
End Sub
This example cannot be done from within Excel unless you manually copy and paste, which could get laborious if there is a lot of data. This is a good example of the macro language giving the user enormous power to automate tasks within Microsoft Office and enabling you to work outside the menu structure that Microsoft has put in place.
|
|