Creating an Instance of Word

Before we begin, let's outline the steps involved in creating a report with text and charts in Word from Excel VBA code:

1. Open the Word application.

2. Create a new Word document.

3. Add formatted headings and text.

4. Add charts with formatted headings and text.

5. Display Word and the new report.

Let's begin. Our first tasks are to open an instance of the Word application and load a new document into that instance.

1. In the MakeWordDoc subroutine, add the following statements:

Set m_oWordApp = CreateObject("Word.Application") Set m_oWordDoc = m_oWordApp.Documents.Add

We are using the CreateObject function to create an instance of the Word application. CreateObject creates a new instance ofWord even if one is already open. To use CreateObject to create an instance of a Microsoft Office product, you call the method and pass in an argument containing the class name of that application. The class name is a combination of the application name plus the object type. In this case (and in most cases), our object type is Application. All Microsoft Office products expose an Application object type. Word and Excel provide a few other object types that can be created with CreateObject as well, as shown in Table 8-1.

Table 8-1. Microsoft Office Object Types

Office Application

Object Type

Class

Access

Application

Access.Application

Excel

Application

Excel.Application

Excel

Worksheet

Excel.Worksheet

Excel

Chart

Excel.Chart

Outlook

Application

Outlook.Application

PowerPoint

Application

PowerPoint.Application

Word

Application

Word.Application

Word

Document

Word.Document

When using any of the additional object types provided by Word or Excel, a new instance ofWord or Excel is created. To use CreateObject to open an instance of Outlook, the syntax would be the following:

CreateObject(Outlook.Application)

CREATEOBJECT VS. GETOBJECT

Another way to return an instance of an Office application is to use the GetObject function. GetObject differs from CreateObject in that it uses an existing instance of the application. There are a couple of instances where GetObject may make sense for your applications. One is when you want to use an existing instance of an application object that is already loaded. The other is when you want to start an instance of an application with a file loaded.

The syntax for using GetObject is GetObject(pathname, class), where pathname is the path to a Microsoft Office document. The class parameter is not required in this case, as GetObject will find which application to launch. If no file name is given, the class parameter is required. The class parameter takes the same object class identifier as the CreateObject function.

2. Next, fill the variables with title and body text by adding the following lines of code:

sTitle = GetTitle sBody = GetTitleBody

3. Then, use the Word Application object to insert the title and body text into the Word document, by adding the following code to the MakeWordDoc subroutine:

With m_oWordApp

.Selection.Style = .ActiveDocument.Styles("Heading 1") .Selection.TypeText sTitle .Selection.TypeParagraph .Selection.TypeText sBody End With

This code defines the document style for the first line of text and inserts the title text. Next, we add a new paragraph break and insert the report's descriptive body text.

Before we begin inserting our charts, let's add some cleanup code and take a quick look at our progress by running and displaying Word from the VBE.

4. Add the following code after the With...End With block:

m_oWordApp.Visible = True MsgBox "word s/b open now" m_oWordApp.Quit Set m_oWordApp = Nothing

Here we are displaying the Word application and our new document. The message box is here to stop the code from running so we can navigate over to the Word window (if it's not already the active window). Once we click the message box to close it, our cleanup code runs and shuts down the instance ofWord and kills the Word Application object.

5. Save your work.

The code so far should look like Listing 8-4.

Listing 8-4. MakeWordDoc Subroutine

Sub MakeWordDoc() Dim i As Integer Dim sTitle As String Dim sBody As String

Set m_oWordApp = CreateObject("Word.Application") Set m_oWordDoc = m_oWordApp.Documents.Add sTitle = GetTitle sBody = GetTitleBody

With m_oWordApp

.Selection.Style = .ActiveDocument.Styles("Heading 1") .Selection.TypeText sTitle .Selection.TypeParagraph .Selection.TypeText sBody End With m_oWordApp.Visible = True MsgBox "word s/b open now" m_oWordApp.Quit Set m_oWordApp = Nothing End Sub

Let's run the code we've generated so far and see what we get.

6. Put your cursor anywhere in the MakeWordDoc subroutine.

7. Click the Run button on the VBE toolbar or press F5.

Note Depending on the speed of your computer, the code may run for a bit before Word appears. Remember, you are loading an actual instance of Word, so this will take about as long as Word normally takes to load on your machine.

Figure 8-4 shows the Word instance displaying the report heading in Heading 1 style and descriptive text in the default (Normal) style formatting.

Note We did not tell our code to format the body text in Normal style, did we? The default behavior of Word's heading styles is to format text at the following paragraph marker to Normal style (saving us a line of code in the process!).

Figure 8-4. The report heading and text

Note The formatting of the Heading 1 and Normal styles may differ in your Word installation.

8. Navigate back to the main Excel window and click OK on the message box to let the code finish running, as shown in Figure 8-5.

mumij m xi word s/b open now

Figure 8-5. The message box stops the code so that we can check results. 9. Click No when prompted to save the Word document (unless you really want it).

0 0

Post a comment