Download Microsoft Office Automation with Visual FoxPro. Author: Della Martin, Tamar E. Granor, Ted Roche Type: eBook. Date Released: Format: chm. Microsoft Office Automation With Visual FoxPro Chapter 11 - Download as PDF File .pdf), Text File .txt) or read online. Ejemplo de automatización desde VFP. caite.info - Ebook download as PDF File .pdf), Text File .txt) or read book online. Microsoft Office Automation with Visual FoxPro. By Tamar E.
|Language:||English, Spanish, Hindi|
|Genre:||Fiction & Literature|
|ePub File Size:||23.63 MB|
|PDF File Size:||12.82 MB|
|Distribution:||Free* [*Regsitration Required]|
Microsoft Office Automation with Visual FoxPro. Home · Microsoft Microsoft Office for Windows: Visual QuickStart (Visual QuickStart Guide) · Read more. Read Download Microsoft Office Automation with Visual FoxPro |PDF books PDF Free Download Here. All other products and services identified throughout this book are trademarks or registered trademarks of their respective companies. They are used throughout.
The Indents and Spacing page shown here lets you indicate alignment. Many of its properties are references to collections, such as Paragraphs, Tables and Sections. So, to set a range for full justification, you'd use: Preset colors: Move wdStory oRange. This page has two sections:
It specifies that a parameter called Unit should have the value wdCharacter, but what's wdCharacter? It's one of dozens of defined constants available in Word's version of VBA. It turns out that wdCharacter is 1. Word's VBA Help doesn't supply the values of defined constants.
In fact, Help uses them exclusively and doesn't show their actual values anywhere. To find out what wdCharacter and all the others stand for, use the Object Browser available through the Visual Basic Editor. First, be aware that the Macro Recorder doesn't always produce the best code for a task. The code it produces gives you an idea of what can be done, but there may be better ways to accomplish the same task.
Second, the Visual Basic Editor has a feature called IntelliSense that makes writing code there easier. When you type an object name and a period like "oRange. When you choose a method from the list use Tab to choose without ending the line , a list of parameters for that method appears like a tooltip to guide you. As you enter parameters, your position in the parameter list is highlighted to keep you on track.
Take Me for a Browse One of the most powerful tools available for figuring out automation code is the Object Browser figure 1. It lets you drill into the various objects in the hierarchy to determine their properties and methods, see the parameters for methods, determine the value of constants, and more.
The easiest way to find out about a specific item is to type it into the search dropdown and press Enter or click the Find binoculars button.
The middle pane fills with possibly appropriate references — choose one to learn more about it. Figure 1 shows the Object Browser used to determine the value of the constant wdCharacter. At the very bottom, you can see that it's a constant with a value of 1. Here, it shows that wdCharacter is a constant with a value of 1 and is a member of a group of constants called WdUnits.
The Object Browser is also useful for moving around the object hierarchy to get a feel for what the various objects can do. In Figure 2, the Search Results pane has been closed and the members of Word's Table object are shown in the right pane.
The Sort method is highlighted, so the very bottom panel shows its complex calling syntax. The advantage of this approach over Help is that the Object Browser actually looks at the server itself, so the list it shows must be correct while Help can contain errors.
Even better, the Object Browser and Help can work together. Press F1 in the Browser and Help opens to the highlighted item. With these tools in hand, you can start the process of writing code to automate Word and Excel. A collection is the OOP world's version of an array. It contains references to a number of objects, generally of the same type, and provides access to those objects. Visual FoxPro has a few native collections Forms, Pages, etc. Most collections though not the native VFP collections have a few methods and properties in common.
The Count property typically tells you how many items are in the collection. Item, which is a method in some collections and a property in others it's a property in the Office apps , provides access to the individual members of the collection.
In many cases, you can specify the item you want by name rather than number. PJX"] provides a reference to the TasTrade project, if it's open, for example. PJX"] and still get a reference to the project. What's the Word? Automation to Word changed dramatically between Word 95 and Word Word 95 had a very simple object model consisting primarily of a single object Word. In Word 97 and later, the object model better reflects the internal structure of Word and its documents.
The key object in Word is Document, which represents a single, open document. The Word server has a Documents collection, containing references to all open documents. The server also has an ActiveDocument property that points to the currently active document.
The Document object has lots of properties and methods. Many of its properties are references to collections, such as Paragraphs, Tables and Sections. Each of those collections contains references to objects of the indicated type. Each object contains information about the appropriate piece of the document.
The Word server object, called Application, has its own set of properties and methods, including a number of other collections. The Quit method used above has several optional parameters — the first indicates what to do if any open documents have been changed and not saved. In addition to Quit, the application object has methods for converting measurements from one set of units to another, checking grammar and spelling, and much more. Word Visual Basic Help contains a diagram of Word's object model.
The figure is "live" — when you click on an object, you're taken to the Help topic for that object. Figure 3 shows the portion of the object model diagram that describes the Document object. Managing Documents The methods for creating, opening, saving and closing documents are fairly straightforward. The only complication is in what object provides which method. The methods for creating and opening a document belong to the Documents collection.
The methods for saving and closing a document belong to the Document object. Although confusing at first glance, this actually makes sense since you don't have a Document object to work with at the time you create or open a document. But when it comes time to save or close it, a document reference is available.
Files in VFP's Project object work the same way. Add is a method of the Files collection, but Remove belongs to the File object.
Open has many parameters, but most of the time, the only one that matters is the first, the name and path of the file to open.
The Word server doesn't recognize VFP's search path, so you usually need to provide the full path to the document, like this: Open "d: If the specified document doesn't exist, an error is generated and nothing is returned. This example, like the others below, assumes you have an instance of Word running, with oWord holding a reference to it. To create a new document, use the Add method, which has only two, optional, parameters. The important one is the first, which indicates the path to the template on which the new document should be based.
If it's omitted, the new document is based on the Normal template. Templates in Word allow you to define the framework for documents and then create as many documents as you want based on that framework. A template can contain both text and formatting, including style definitions see Working with Styles below , and can be quite simple or extremely complex.
It can also contain bookmarks, named points in the document, which you can use to quickly access to a particular position. Like Open, Add returns a reference to the newly created document. This line creates a new document based on the "Elegant Fax" template that comes with Word. Add ; "E: DOT" As with Open, the full path to the template is needed. To do so, though, you need to know where Word is installed or at least, where the templates are kept.
Fortunately, Word provides some clues. The DefaultFilePath method of the Options object which is a member of the Word Application object can return the root directory for both the user's own templates and user's workgroup's templates.
Using that information, we can search for the path to a specific template, given its name. The NewDocument method of the cusWord class on the conference CD accepts a template with or without a path and attempts to locate the template file before creating a new document.
When you're finished working with a document, two methods are available to save it. Save saves the document back to its current file; if it's never been saved, that pops up the Save As dialog box. SaveAs lets you specify the file name and a lot of other stuff without seeing a dialog, which is usually what you want with automation code. If the currently active document has already been saved, this line resaves it without user intervention: Save To save the document to a different file or to save a document for the first time without the user specifying a file name, call SaveAs and pass the file name, like this: SaveAs "D: When you pass a file name to SaveAs, it overwrites any existing file without prompting.
Accessing Parts of a Document Most of what you want to do with Word involves adding to, modifying or reading an existing document. There are a variety of ways to do these things, but the key to just about all of them is the Range object and, to a lesser extent, the Selection object.
The Selection object represents the currently highlighted that is, selected portion in a document. If nothing is highlighted, Selection refers to the insertion point. There's only one Selection object, accessed directly from the Word server.
For example, to find out how many paragraphs are in the current selection, you can use this code: Count A Range object can represent any portion of a document. Multiple ranges can be defined and used at the same time. Many Word objects have a Range property that contains an object reference to a range for the original object. For example, to create a Range from the third paragraph of the active document, you can use: Range Document has a Range method that lets you specify a range by character position.
For example, to get a reference to a Range from the th to th characters in the active document, use: Range , Document's Content property contains a reference to a Range consisting of the entire main document the body of the document without headers, footers, footnotes, and so on.
So the next two commands are equivalent: Content It's easy to switch between Selection and Range objects. Like many other objects, Selection has a Range property. Range has a Select method that highlights the range's contents, turning it into the Selection.
For example, to highlight the range from the previous example, use: Select Selection and Range seem quite similar and, in many ways, they are, but there are differences. The biggest, of course, is that you can have multiple Ranges, but only one Selection. In addition, working with a Range is usually faster than working with a Selection. The main reason is that using Selection is essentially duplicating screen actions with code; Range lets you operate more directly.
To import document contents, create an appropriate Range and read its Text property, like this: Text Text also lets you add or change the document contents. You can add text by assigning it to the Text property. Simple text manipulation does the trick.
Text Another possibility is to read text into VFP, manipulate it in some way and write back. Text Using this approach, we can send the data from a record to a new, blank document: Range oRange.
In other situations including the examples below , you need to add the paragraph marker explicitly. Of course, building a document by composing a single string doesn't take advantage of the special capabilities of Word. Range's InsertAfter and InsertBefore methods let you add text at the end or beginning, respectively, and expand the range to include the new text.
Here's an alternative, faster approach to the example: InsertAfter "Attn: Moving in a Range or Selection Besides changing a range or selection's content, you may need to modify its extent. A number of methods change the area covered by a range or selection. One of the simplest is the Move method, which changes the boundaries of the range or selection.
Move accepts two parameters. The first indicates the unit of movement — you can move by characters, words, paragraphs, rows in a table, or the whole document. The second parameter tells how many of the specified units to move — a positive number indicates forward movement toward the end of the document , while a negative number means backward movement toward the beginning of the document. In all cases, the range or selection is collapsed to a single point before moving. When moving forward, the range or selection is collapsed to its end point, then moved; when moving backward, it's collapsed to its beginning point before moving.
Constants from the wdUnits group are used for the units of movement. Table 1 shows the values for this group that can passed to the Move method. VFP constant definitions for these, as well as for other Word constants used in these notes, are in the file Word.
H on the conference CD. The notes use the constants for readability. Constant Value Description wdCharacter 1 One character.
Word allows you to divide documents into multiple sections with different formatting. Word considers the main body of the document to be one "story," the header to be another "story," the footnotes to be a third and so forth. Table 1 Word units — The constants in the wdUnits group represent portions of a document. To create a range at the end of the document, you can: Move wdStory, 1 To create the same document based on the Customer table as in the examples above, use this code: Move wdStory oRange.
The MoveStart and MoveEnd methods let you change one boundary without affecting the other. Collapse lets you reduce a range or selection to a single point. It takes one parameter, indicating the direction of the collapse. Passing the constant wdCollapseEnd, with a value of 0 collapses the range or selection to its end point the point closest to the end of the document.
Passing wdCollapseStart whose value is 1 reduces the range or selection to its starting point. The example can be rewritten yet again to use Collapse to control the range: Collapse wdCollapseEnd oRange. A number of other methods allow fine-tuning of movement. Some methods apply only to the selection, not to ranges. Before leaving the example, it's worth commenting that, for this particular task, the fastest approach of all is to concatenate all the strings in VFP, then send one string to the document: InsertAfter cText Formatting If all we could do was send text to Word and read the text already there, Automation would be useful, but not worth too much trouble.
However, there's much more to automating Word than just sending and receiving text. One of the big benefits of using Word rather than VFP is the ability to apply complex formatting to documents. Word allows documents to be formatted in a number of ways and the objects involved in formatting reflect that structure.
For example, the Font object contains properties for the settings found in Word's Font dialog Format-Font on the menu. The ParagraphFormat object controls the settings in the Paragraph dialog, such as indentation, spacing and alignment. Similarly, the settings from the Page Setup dialog are controlled by the PageSetup object.
Style objects represent the individual styles available in the document. These four objects manage most of the frequently used settings. Other objects control other aspects of formatting. Setting Fonts Range and Selection as well as some other objects each have a Font property that points to a Font object.
Changing the properties of the Font object modifies the font of the range or selection. For example, to change all the customer information in the example above to point Arial, use these lines: Here's an updated version of the code that uses point Arial from the start: It's better to use a template where the font of the Normal style has been set as needed.
See Working with Styles. Here's a function that lets the user specify a font, prompting with the font currently in use.
Then, it changes to the specified font: Code like: IF oFont. That's because Bold isn't just a logical setting with acceptable values of true and false; it accepts two other values, represented by the constants wdToggle and wdUndefined When you assign logical values, Word translates them somewhere along the way, but for comparison, you have to use the numeric values. To use this function, pass it a reference to a font object.
For example: SetUserFont oRange. Formatting paragraphs The ParagraphFormat object determines things like alignment of text, indentation both amount and type , spacing of lines and paragraphs, handling of widow and orphans, and much more.
The Paragraph object has a Format property that accesses a ParagraphFormat object. In all cases, it's accessed through the Format property. So, to set a range for full justification, you'd use: Its value is 3. A style in Word is a named format that you can apply to a portion of a document.
When you're working in Word, you can see the current style in the first dropdown on the Formatting toolbar. Word has two kinds of styles: Character styles are used for fragments and control only a few settings, primarily font-related. Paragraph styles, as the name implies, apply to entire paragraphs and include a lot more options.
Paragraph Styles can specify font and paragraph formatting, as well as tab settings and much more. Using styles is much like using classes in VFP. They make it easy to enforce uniformity throughout and across documents and let you change the characteristics of sections of text with a single change. Word's styles offer some other benefits, as well.
For example, each paragraph style knows the style for the paragraph to follow. So, a style normally used for a heading can be set to be followed by the style used for body text following that heading. With a little more work, styles can used to provide an outline of a document. The Document object includes a Styles collection, containing Style objects for all the styles stored in the document. You can add your own styles using the Add method. The Paragraph object's Style property points to the Style object for that paragraph's style.
What all this means is that, rather than writing a lot of code to change fonts and sizes, and to set alignment and leading and other things like that, you can simply define a few custom styles or modify built-in styles, then apply them to your paragraphs as needed. This example takes the simple customer address document from the previous examples and begins to create a document worthy of using Word. It creates several new styles to do the job. In practice, you could probably use the built-in Normal and Heading x there are multiple heading levels for this document.
But the example shows how easy it is to create new styles. Base body style on Normal. InsertAfter "Factory Blvd. InsertAfter "Phone: InsertAfter "Fax: Here's a look at the resulting document: Figure 4 Formatted document — The Font and ParagraphFormat objects control lots of formatting options. Style objects let you use predefined combinations easily. Printing Producing attractive documents is a good start, but users usually want printed copies, too. The PrintOut method of the Document object automates printing.
It accepts a huge array of parameters. This is one situation where named parameters start to look pretty good. Fortunately, all parameters are optional and you won't need most of those parameters for normal processing.
Unfortunately, the ones you're most likely to need are in the middle of the list rather than up front. To print three copies of pages 4 to 10 of a document referenced by oDocument , use: Note that the numeric values for page numbers and number of copies are passed as strings.
To print to a file, you have to specify both the file name and a flag that you're printing to file: PrintOut , , , "fileoutput", , , , , , ,. The resulting file is ready to print, complete with printer codes.
Keep in mind that the file is stored in Word's current directory by default, so it's a good idea to provide the full path.
Be forewarned that printing to file sets the Print to File checkbox in the Print dialog and leaves it set. A subsequent print to the printer resets it. Word can also automatically create and print an envelope document. To do so, use the document's Envelope object. This example assumes that oRange is a range containing the customer name and address in a mailing format. It bookmarks the address and asks Word to create the envelope. Add "EnvelopeAddress", oRange oDocument.
UserAddress A bookmark is a way of naming a range or location. To create one, call the Bookmarks collection's Add method. The call to PrintOut tells Word to use whatever is at the EnvelopeAddress bookmark for the address, and to include the user's stored address from the Tools-Options dialog's User Information page as the return address. Additional parameters let you set the type and size of the envelope as well as the printing orientation.
Omitting those parameters uses the current settings. Search and Replace The ability to find a text string and replace it with another throughout a document without retyping was one of the "killer" items that led to early acceptance of word processors. Word's version is extremely powerful — it includes the ability to search for text alone, for text in a particular format, for formatting only, for certain styles, and so forth. Searching is handled by the Find object.
It has an assortment of properties, such as Text, MatchCase and MatchWholeWord, that determine what it's searching for. Some properties, like Font and ParagraphFormat, are references to other objects. The Format property determines whether formatting is considered in the search.
Set it to. The Execute method actually performs the search. It accepts a number of parameters, most of which duplicate properties of the Find object. Omitting them uses the current settings of the Find object. Find is a member of Range and Selection, but not of Document. The search begins at the beginning of the range or selection, unless Forward is. The Wrap property determines what happens when it reaches the end or beginning, when searching backward of the range or selection.
To search the whole document, create a range at the beginning of the document. When Execute finds a match, the range or selection moves to cover only the matching item.
There are two ways to go about it. The last two parameters of Find's Execute method let you specify a replacement string and the number of replacements to perform none, one or all.
Execute , , , , , , , , , "Visual FoxPro! The alternative approach uses a Replacement object referenced through the Find object's Replacement property. You can fill in Replacement's properties to specify exactly what should replace the found item. WITH oRange. To change every occurrence of point Arial to point Times New Roman, use code like this: Imagine putting a collection of search and replacement strings in a table, then using automation to make all the changes without intervention.
I don't actually have to imagine this one. I've done it. Tables Word's tables seem like a natural fit for representing VFP data. A table can be formatted as a whole, but individual cells can be separately formatted, too. Borders of tables and cells can be visible or invisible, and can take on a range of sizes and styles. Both columns and rows can be individually sized.
The object hierarchy for tables is a little confusing. Each document has a Tables collection, which in turn contains individual Table objects. Somebody wants to create such-and-such server. Chapter 2. Outlook has its own outlook on the world. Section VI covers an assortment of advanced topics. You can make some educated guesses based on what the place looks like. To work with the application server efficiently.
Where do you go from here? See Chapter The server name might not be found in the registry. It then returns a reference to the newly executing program to the client. If anything goes wrong. But to use the restaurant efficiently. Spend some time as an end user before you try to program it. Introducing Automation 9 Windows goes off to the registry and says. Chapter 2 shows how to explore the Office servers to find out what they can do and how they do it.
This book does explain how various features of the Office applications work before showing how to automate them. You know his name. Exploring the Office servers All of the Office applications share a programming language—Visual Basic for Applications. Excel is a sophisticated spreadsheet program. PowerPoint builds and displays presentations. In most cases. Word and Excel. Word is an incredibly powerful word processor. It also incorporates an address book.
Certain features of the Office applications. On the other hand. Outlook is harder to describe because it has a little of everything. For Automation developers. There are three main approaches to take. For Automation. Along with them come a couple of others that are used by an increasing number of people who may not have planned to do so initially—PowerPoint and Outlook.
For others. It provides users with the ability to create anything from a simple note to complex reports and legal documents. Beyond those. For some. Microsoft Office incorporates a whole bunch of applications and applets. Section I Chapter 2: Like the other applications. To use the Office servers. It includes integrated graphing capabilities and has a significant collection of built-in financial.
In Office Getting to Visual Basic Help for Outlook 97 is extremely complex. Given all of these difficulties in getting to VBA Help. In PowerPoint In the big three—Word.
The whole set of four [Word. You start out the same way in Word. On the Contents page. When you choose that item. The method of getting to that Help file varies with the application. In Word and Excel To do so. For Office By default. In all three cases. By this point. Although accessible from within the main Help file for its respective application.
For the VBA Help files. End users probably find this approach more productive. The real challenge. So one way to figure out how to automate something is to record a macro to do it.
From the menu. Help files had three pages: Let the server write the code Word. Each of the VBA Help files includes a diagram of the object model for that product. The best substitute for the Index page is the separate alphabetical lists of Objects. Figure 1 shows the top level of the Word object model diagram. Give the macro a name preferably a meaningful one. Properties and Methods on the Contents page. The team at Microsoft merged the Index and Find pages to create a single Search page.
Index featured an alphabetic list of terms from the Help file like the index of a book. The Office Servers 13 For the most part. Then interactively perform the operation you want to automate. Despite the difficulties in getting directly to a particular item in Office Help a task that was incredibly easy in Office The Help entries for objects include small pieces of the object model diagram as well— clicking on these also jumps to the indicated entries.
We suspect that there are some good usability arguments for the new search mechanism in the Help files for the main products. Outlook supports macros. Office Help files have only two pages: Contents and Search. There is no way to move quickly to a keyword. To look at the recorded macro. This particular macro was recorded in Word. Highlight the macro you just created see. Clicking on one of the rectangles takes you to the Help entry for that object.
Clicking an arrow takes you to another level in the object model diagram. The Word object model diagram. The Help file for each Office product contains a diagram of the object model for that product. Live Help. Even within Help entries. In this case. The Macros dialog. The Office Servers 15 Figure 2. Figure 3. The line moves the insertion point the vertical bar that determines where the next character is inserted six words to the left.
Consider this line of Word VBA code as you read the following sections. In each Office application. At first glance. In the preceding line. Viewing macros. MoveLeft Unit: Since some VBA methods have a dozen or more parameters. Editing a macro takes you to the Visual Basic Editor. The macro shown in Figure 4 demonstrates all of them. Named parameters VBA allows methods to use what are called named parameters. In the example code line. The code you send to the Automation server must be addressed to the correct object.
Three parameters are passed. To find out what wdWord and all of the others stand for. Outlook is the exception here. The smallest set is for Outlook—it contains constant definitions. Check Help or the Object Browser. We do not recommend using these files as is in your Automation work. VBA constants is it. The number of constants contained in them is mind-boggling. The Developer Download files also include header files for each of the Office applications.
If there was ever a reason to use header files. It turns out that wdWord is 2. On one of our test machines.
It specifies that a parameter called Unit should have the value wdWord. Also check to ensure that the macro recorder really did put them in the proper order. But what is wdWord? By contrast. The Office Servers 17 However. Be sure to check Help for omitted values.
Help for many methods showed the parameters out of order. The tool. Lines and lines of code are generated. When you choose a method from the list use Tab to choose without ending the line. Take me for a browse One of the most powerful tools available for figuring out Automation code is the Object Browser see Figure 5. The middle pane fills with potential matches. The easiest way to find out about a specific item is to type it into the search dropdown.
The macro recorder is focused on interactive users. Perhaps 15 properties are set when you only changed one. Even better. The code it produces gives you an idea of what can be done. When you type an object name and a period like "oRange. In the bottom-most pane. A good deal of the time. Other times. Many times. The PrintOut method is highlighted. The advantage of this approach over Help is that the Object Browser actually looks at the type library.
It lets you drill into the various objects in the hierarchy to determine their properties and methods. Choose one to learn more about it in the main section of the Browser underneath. In Figure 5. As you enter parameters. Press F1 in the Browser. The Object Browser is also useful for moving around the object hierarchy to get a feel for what the various objects can do. Write the code in the VBE. The left pane is filled with the properties. This powerful tool lets you drill down into objects.
The Object Browser. The Office Servers Figure 5. The Browser is also useful for exploring the object hierarchy itself. Here it shows that wdWord is a constant. In the bottom pane. The Slides property is highlighted. Figure 7 shows the PowerPoint version of the Object Browser this time.
Clicking on the underlined Slides takes us to the Slides collection. When a method is highlighted. Check the libraries you want to add to the Object Browser. Using the Object Browser to determine parameters.
Be aware that. To open a type library so the Object Browser can display its contents. The dialog shown in Figure 9 is displayed. So be careful what you actually save. What does the Browser browse? For Figure 6. Since the Browser gets its information directly from the server.
You can use the Object Browser from any of the Office tools to open and explore any registered type library. You can even look at the objects from multiple type libraries at the same time. Click on Slides. The Office Servers 21 Figure 7.
Within the Object Browser. When an item is underlined in the bottom pane. Exploring the object model. Clicking on the reference to the Slides collection shown in Figure 7 produces this display in the Object Browser. The Browser makes it easy to explore the relationships among objects in the hierarchy. Just as it does in every other aspect of working in VFP. Even the VFP Debugger can be used in a limited way.
Create a reference to the appropriate server and try the sequence of commands one by one. Too bad. You can query the value of a property with?
The Slides collection. While trying to understand how a particular feature works. Some commands. Either set a variable in the Command Window. Try them in the Command Window.
The Office Servers 23 Figure 9. One big drawback: We encourage you to explore the servers in the Command Window. Perhaps more than any other. Adding type libraries.
That is. It really helps to see instantly just what that line of code does or it becomes an instant approach to finding a syntax error. Another really cool piece is that you can move back and forth between doing things interactively and doing them with Automation. There are on-line communities for the Office applications. Just remember that the collection is plural as is usually the property to access it.
See Appendix A. Check it out for official support. The Slides collection references a series of Slide objects. Advisor Expert: Microsoft Outlook and Exchange. Once you become comfortable enough reading VBA code. We have less experience with them. FoxPro Advisor www. Since Microsoft is in the habit of rearranging its web site regularly. The FoxPro community has a well-earned reputation for its helpfulness—enough members are doing Automation work that simple questions are answered quickly.
More difficult ones sometimes go unanswered.
Taking up a collection The object models of the Office applications along with most COM servers contain lots of collections. The Language Reference guides are available on the Microsoft web site. A collection contains references to a number of objects. The major FoxPro magazines. The Count property tells you how many items are in the collection.
Each is available both in book form and on-line. Most collections. Some collections have a fixed number of entries. The property frequently but not always has the same name as the collection or other object.
Self-referential object models When you start digging around the Office object models. So Add methods belong to collections. Word and PowerPoint also have similar Borders collections. If the TasTrade project is open and is the first open project. In many cases. Other collections are modified by other actions in the system. This collection always has four items. Although adding and removing may seem like complementary operations to us.
Projects and still get a reference to the project. Changing the collection Once you get past Count and Item. The Office Servers 25 Item typically takes a single parameter or index number and returns a reference to that member of the collection. The methods that do remove objects from collections tend to belong to the object itself.
Figure From there. From Document to Paragraphs. While this makes sense. That takes you to the Help page for the Documents collection shown in Figure 2—look behind the dialog.
Figure 10 shows the Document page and the resulting dialog. That brings you to the Help page for Document. Many objects have properties that reference collections. Choosing the Paragraphs property from the dialog brings up Help for that property, as shown in Figure To get to Help for the Paragraphs collection requires a click on Paragraphs; to get to the actual Paragraph object—usually our actual destination—requires yet another click once we get to Paragraphs.
This is one of our least favorite things about the VBA Help system. We wish it were smart enough to offer some kind of consolidated help for the property and collection. Not so helpful. Since the reference properties almost always use the object names, watch out for the special cases. Use the products. We use Office. Word, in particular, is part of our normal working environment, and much of what we know about automating it comes from hard-won expertise in using it.
Why would you expect to do it with Automation? This chapter takes a look at the things that stay the same across all servers and across the Office servers. Managing servers The first step with any server is getting a reference. There are two possibilities for an Automation server: CreateObject always creates a new instance of the server and returns a reference to it. For example, to open PowerPoint, issue this command: Note that the syntax for creating Automation objects is almost identical to that for creating native objects.
The only difference is the class of the object created. This is polymorphism one of the pillars of OOP at work. GetObject takes two parameters.
For this use, omit the first parameter and pass the server class as the second. If the server is already open, GetObject finds it and returns a reference to that instance. However, if the server is not open, this version of the function generates an error. For example, to attach to an open instance of Excel, use this command: In some situations, the filename may not be sufficient to determine what application to open or what to make of the file.
In that case, you can also pass the name of the class within the server.
For example, if the file has a TXT extension, but you want Word to open it as a document, pass "Word. Document" as the second parameter, like this: TXT", "Word. Most of the time, either VFP or the server application chokes when it receives the second parameter unnecessarily. However, while debugging and in some other circumstances, you may want to make the Automation process visible. Why not just make the server application visible all the time? This is one area where Outlook is the odd man out.
To make the Automation server visible, set its Visible property to. Actually, in PowerPoint, you can set Visible to. Word and Excel give you complete control over their visibility. Manipulating WindowState when the application is invisible generally makes it visible at least to the extent.
Each of the applications has a set of constants for the three possible values of WindowState. For example, for Excel, you can make these definitions: Word needs the following: You can manipulate these, unless the application is maximized. The following code SetSize. PRG in the Developer Download files available at www. PixelsToPoints nWindowHeight,. PixelsToPoints nWindowWidth,. As the example indicates, Height, Width, Top, and Left are measured in points. Word provides a number of methods for converting other measurements into points including, in Word , the PixelsToPoints method used in the example.
The other applications offer. The conversion factor between pixels and points is about 0. You can handle converting between inches and points in the same way. There are 72 points to an inch. Define your own constant for that task, like this: Each call to Word is expensive. Are we there yet? All three applications start with no document open and no document window when you call CreateObject , but they behave differently when GetObject is used to open a specific file.
Just set Visible to. PowerPoint and Excel are different. Setting Visible to. In PowerPoint, you need to call the NewWindow method to provide a document window for the specified presentation: Excel uses yet another approach to the same problem. Call the Activate method for the first window in the Windows collection: Working with servers A few Visual FoxPro language features and interface components make it easier to write and maintain Automation code.
Why does this command exist? The last place VFP looks to find an object is in the registry. Before searching the registry, it loads OLE support, which takes up memory. A series of commands that all begin with something like oWord. Tables [ 3 ]. Instead, surround the group like this: WITH oWord. Tables[ 3 ]. In fact, you can nest WITH commands. As you walk down the object hierarchy, doing a few things at each level, set up a WITH statement for each level—something like this: The nesting makes it clear to the reader which WITH each property belongs to.
VFP, of course, has no difficulty figuring it out. In our tests, a fairly simple example that. For example, the following code is acceptable: Use variables for object references Another way to make your code more readable and speed it up is to assign complex object references to local variables.
The variable name is easier to type and easier to read. We tested the same example as for WITH querying properties at various levels and found that setting a local variable was as fast as—or even a little faster than—using WITH. The more deeply nested the reference and the more times you need it, the more time you save. When you use local variables, you may need to clean up afterwards. In some situations, these references can prevent the server from closing when you call the Quit method.
FOR EACH lets you go through a collection or array without using a counter or worrying about how many members there are. Note, by the way, that using FOR EACH implies the use of a local variable as described in the previous section—the object reference used as the loop variable. Debugging Debugging Automation code is something of a challenge.
The Watch and Locals windows only show a property of an Automation object once that property has been accessed from VFP. So how do you debug Automation code? Carefully, and with a lot of advance planning.
We find this approach to be far more necessary we daresay mandatory with Automation code than with pure VFP code. Collections and objects Watch out for collections vs.
For example, when you see something like the following, keep in mind that the object it references is an object, not a collection: This is actually another argument for using local variables.
Setting oPresentation to refer to that presentation helps to avoid confusion. A handy hint: Recheck the appropriate sections in this book and in Help. Make sure you can perform the task or at least a prototype of the task interactively.
Things to check in that situation are matching parentheses. But other times. The third group of errors reflects the difficulties of communicating between two applications. These may occur if the server is shut down unexpectedly say. Unhelpful error messages When an error occurs on an Automation command.
When one of these errors occurs. But that seems to be asking for too much. In the second case. Sometimes though much less often than in older versions of Office. Along with it. Name when there is no active document. Search for the particular error code not the VFP error number.
Use the Object Browser to be sure of the proper order. In the first case a VFP problem. Add complexity back in. Ask around Still stuck? There are plenty of places to get on-line help with both VFP and the Office applications. Time for code At this point. The next four sections of the book look at each of the Office products covered.
Check out the list in Appendix A. Section I Chapter 3: Getting to Word Before you can work with Word. Figure 1 shows the portion of the object model diagram that describes the Document object.
Many of its properties are references to collections such as Paragraphs. Other methods of the Application object convert measurements from one set of units to another. Section II Chapter 4: Turn to Word instead. Microsoft Word is an incredibly powerful word processor.
It has several optional parameters—the first indicates what to do if any open documents have been changed but not saved. In addition to ActiveDocument. It also provides tools for processing and managing those documents in a number of ways. Users can work with multiple documents simultaneously.
Its document orientation provides formatting from fonts to outlining. Even for simple documents. The Document object has lots of properties and methods.
The Application object also has methods. Each object contains information about the appropriate piece of the document. Each of those collections contains references to objects of the indicated type. The server also has an ActiveDocument property that points to the currently active document. The Word server object. Formatting in Word is also quite powerful. The simplest to understand is Quit.
Word provides myriad tools that allow users to produce attractive output. Two VFP functions let you access Word. It lets users create documents ranging from simple memos to complex multi-part corporate reports.
CreateObject opens a new instance of Word and returns a reference to it. The Word server has a Documents collection. If not. If you pass it the name of an existing document. Either way. The Word object model. GetObject is a little more complex. It takes either of two parameters.
DOT" As with the filename in Open. The SaveAs method lets. To create a new document. This example. Both methods belong to the Document object. To open an existing document. The important one is the first. But when it comes time to save or close it. Managing documents The methods for creating. Open has many parameters. Open returns an object reference to the newly opened document. Add returns a reference to the newly created document.
Templates are discussed in more detail in Chapter 5. The only complication is in what object provides which method. Although it seems confusing at first glance. The Save method saves the document back to its current file. The methods for creating and opening a document belong to the Documents collection. Open "d: See Chapter 3.
The methods for saving and closing a document belong to the Document object. DOC" Be careful. If the currently active document has already been saved.
There are a variety of ways to do these things. When you save the document. Save To save the document to a different file. You can write code like this to figure out whether to use Save or SaveAs: If Saved is. ActiveDocument IF. The Name property of the Document contains just the file stem with no path or extension.
When you create a new document. When you pass a filename to SaveAs. Of course. You can check whether the document has been saved by testing its Saved property. SaveAs "D: The FullName property contains the complete filename. Count A Range object can represent any portion of a document. Selection has a Range property. Select Selection and Range seem quite similar.
Ranges are not the same as the Selection area. You can define or modify Ranges without affecting the current Selection. The biggest. Selection refers to the insertion point. So the next two commands are equivalent: Ranges can be obtained in many ways. If nothing is highlighted. You can even define multiple ranges for a document. On the whole.
The main reason is that using Selection is essentially duplicating screen actions with. Ranges are very useful for repeatedly referencing specific portions of a document. Like many other objects. Many Word objects. Range The Document object has a Range method that lets you specify a range by character position. Range Word Basics 45 The Selection object represents the currently highlighted that is. Content Beware: You can add text by assigning it to the Text property.
To bring document contents into FoxPro. Text Using this approach. Text The Text property also lets you add or change the document contents. Range lets you operate more directly. Manipulating text The Text property of Range and Selection contains whatever text is in the specified area. Text Another possibility is to read text into VFP. Simple text manipulation does the trick. In other situations including the examples that follow.
The new document looks like Figure 2. One of the simplest is the Move method. The second parameter tells how many of the specified units to move—a positive number indicates forward movement. Moving in a range or selection Besides changing the content of a range or selection. The first indicates the unit of movement—you can move by characters. Figure 2.
Text when executing this code. The return address shown can be sent to Word in many different ways. InsertAfter "Attn: Move accepts two parameters.
A number of methods change the area covered by a range or selection. One cell of a table. One column of a table. Table 1 shows the values for this group that can be passed to the Move method. Range oRange. One word. When moving forward. The constants in the wdUnits group represent portions of a document. One section of a document. To create a range at the end of the document. One paragraph. For the Automation programmer. It uses the Move method to move the Range object. The entire space of a table.
Word units. Word allows you to divide documents into multiple sections with different formatting. Although collapsing a range or selection sounds dire. Move wdStory. Constants from the wdUnits group are used for the units of movement.
One row of a table. In all cases. Table 1. One sentence. Word Basics 49 oRange. Collapse moves either the starting point of the range to the endpoint or the endpoint to the starting point.
Collapse wdCollapseEnd oRange. The Collapse method lets you explicitly reduce a range or selection to a single point. It takes one parameter. Move wdStory oRange. The example can be rewritten yet again to use Collapse to control the range: Passing wdCollapseStart whose value is 1 reduces the range or selection to its starting point.
Some methods apply only to the selection. They include MoveEnd. The range then consists of just a single point at what was previously either the end or the beginning of the range.
A number of other methods allow fine-tuning of movement. Passing the constant wdCollapseEnd with a value of 0 collapses the range or selection to its end point the point closest to the end of the document.
Style objects represent the preformatted and user-defined styles available in the document. Other objects control other aspects of formatting. More importantly. Bookmarks A bookmark is a way of naming a range or location.