Opening, Closing, and Creating New Documents
The most fundamental VBA commands look similar in Word, Excel, PowerPoint, and FrontPage—and very different in Outlook and Access.
Using VBA to Open, Close, and Create Word Documents
In Word, the command to create a new document based on the normal document template (Normal.dot) looks like this:
Documents.Add
If you want to base the new document on a different template, add the full path to the template:
Documents.Add "c: Program Files Microsoft Office Templates Company Invoice.dot"
To open an existing file—C: My Documents Invoice1049.doc, for example—use the Open method:
|Open method
|methods|Open
Documents.Open "C: My Documents Invoice1049.doc"
The Open method not only opens the document, it makes the newly opened document the active document—just as Word would, if you opened it by choosing File, Open.
There are three common ways to save a document. If you just want to save the currently active document, use
|Save method
|methods|Save
ActiveDocument.Save
On the other hand, if you know you want to save the document called "Memo to Justin.doc"—but you don't know whether it's the currently active document, you can activate it, and then close it, like this:
Documents("Memo to Justin.doc").Activate
ActiveDocument.Save
In fact, VBA/Word doesn't require you to activate the document before you save it. This will save "Memo to Justin.doc," too:
Documents("Memo to Justin.doc").Save
The Close method works just like the Save method. All three of these are valid:
|Close method
|methods|Close
ActiveDocument.Close
Documents("Memo to Justin.doc").Activate
ActiveDocument.Close
Documents("Memo to Justin.doc").Close
That looks pretty simple, but there's a lot behind the different commands. In VBA parlance, a Document is an object. In this case, the Document object is exactly what you would expect: a Word document. The Documents object is a collection of Document objects; more precisely, it's the collection of open Word documents. Documents("Invoice.doc") is the Document object called Invoice.doc.
Add is a method, an action that can be applied to the Documents collection. It adds a new Document object to the Documents collection (a nonprogrammer would say that Add creates a new document). Similarly, Open is a method that applies to the Documents collection.
Save and Close are methods, but they can be applied to either a single Document object or to the entire Documents collection. For example,
Documents.Save
Using VBA to Open, Close, and Create Excel Workbooks
Excel works similarly to Word. The following VBA/Excel statements are all analogous to their VBA/Word counterparts:
Workbooks.Add
Workbooks.Add "C: Program Files Microsoft Office Templates Company Scores.xlt"
Workbooks.Open "C: My Documents Scores20001217.xls"
Workbooks("Scores20001217.xls").Activate
ActiveWorkbook.Save
Workbooks("Scores20001217.doc").Save
Using VBA to Create a New Outlook E-mail Message or Contact
Outlook's approach to creating new e-mail messages and Contacts doesn't involve the Add method. Instead, Outlook uses something called CreateItem. There's almost no similarity at all between Outlook and Word or Excel.
The following creates a new message and displays it, so the user can fill it out:
Set message = CreateItem(olMessageItem) message.Display
And this creates a new Contact, again displaying the Contact so the user can complete it:
Set message = CreateItem(olContactItem) message.Display
The documentation claims you have to mess around with MAPI calls, create all sorts of odd objects, and the like, but that's not true. The preceding examples work perfectly well.
Using VBA to Create a New FrontPage Web File
FrontPage resembles Word and Excel in using the Add method to create new Web files. The command looks something like this:
|Add method
|methods|Add
WebFiles.Add "http://mysite.com/somepage"
Note that the URL must be included with an Add.
Using VBA to Open, Close, and Create PowerPoint Presentations
PowerPoint lives in a world of its own away from Word and Excel. All these commands work:
Presentations.Add
Presentations.Add "C: Program Files Microsoft Office Templates Company Sales.pot"
Presentations.Open "C: My Documents Sales Presentation.ppt"
ActivePresentation.Save
Presentations("Sales Presentation.ppt").Save
However, there's no reasonable way to activate any particular open presentation. (Yes, it's theoretically possible, but the method is too convoluted to be practical.)
Using VBA to Open, Close, and Create Access Files
In Access, there's no Projects collection, and no Add method to go with it. Instead, you must deal with methods with names such as NewAccessProject, CreateAccessProject, and OpenAccessProject, which work only with the Application object. Look in the Access help files for the topic "Application Object."