New and Open

As you'll see in Chapter 6, the most common thing you do in Office applications like Word, Excel, and PowerPoint is to specify an area in a document and then manipulate the content. If you want to create content in a new document or in an existing document that isn't currently loaded into the application, you first need to create a new file or open an existing one. This necessity isn't limited to Word, Excel, and PowerPoint documents. You may also need to create new e-mail messages in Outlook, for example, or open an existing database in Access to retrieve data for a report in Word.

If you click Open on the File menu in Word, Excel, PowerPoint, or Access, you see the Open dialog box. You use the Open dialog box to navigate to a file, and then you click Open to open the file in the application. When you need to open an existing document—such as a Word document, Excel workbook, PowerPoint presentation, or Access database—using Visual Basic code, the functional equivalent is the Open method. The Open method lets your programs open documents that exist in the file system on the user's machine, on a network share, and on an HTTP or FTP server. You can use the Open method to open any file type listed in the Files of Type drop-down list in the Open dialog box.

The Open method adds a document item to the existing collection of documents in an Office application. Hence, you can access the Open method in Word, Excel, and PowerPoint from the Documents, Workbooks, and Presentations collection objects, respectively. In the definition of the Open method in Word, Excel, and PowerPoint, you need the first argument, named FileName, and it's consistent in all three applications. In the FileName argument, you specify a valid file that's fully qualified with the pathname. Using the techniques described in the first section of this chapter, "File Management," you build a string to be passed into the FileName argument of the Open method. The table on the following page lists the definition of the Open method in Word, Excel, and PowerPoint. These definitions are copied from the Details pane of the Object Browser in the Visual Basic Editor.

Document Collection Object	Definition of the Open Method
Word - Documents	Function Open(FileName, [ConfirmConversions], [ReadOnly], [AddToRecentFiles], [PasswordDocument], [PasswordTemplate], [Revert], [WritePasswordDocument], [WritePasswordTemplate], [Format], [Encoding], [Visible]) As Document
Excel - Workbooks	Function Open(FileName As String, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMru]) As Workbook
PowerPoint - Presentations	Function Open(FileName As String, [ReadOnly As MsoTriState], [Untitled As MsoTriState], [WithWindow As MsoTriState = msoTrue]) As Presentation

Excel also provides an OpenText method on the Workbooks collection object. In Word and PowerPoint, you can use the Open method to open Text files (*.txt) or any other file type listed in the Files of Type drop-down list in the Open dialog box. In Access, you can use the Microsoft ActiveX Data Objects 2.1 Library to open an existing database with the Open method, as described in the integrated Office solution in Chapter 8.

You use the Add method to create a new document in Word, Excel, and PowerPoint. You'll find the Add method in many places throughout the Office objects, and you use it consistently when adding an item to a collection. When you want to add an item to the collection of documents, use the Add method to create a new document on the Documents, Workbooks, and Presentations collection objects, respectively.

The following table lists the definition of the Add method in Word, Excel, and PowerPoint for the Documents, Workbooks, and Presentations collection objects, respectively. These definitions are copied from the Details pane of the Object Browser in the Visual Basic Editor.

Document Collection Object	Definition of the Add Method
Word - Documents	Function Add([Template], [NewTemplate], [DocumentType], [Visible]) As Document
Excel - Workbooks	Function Add([Template]) As Workbook
PowerPoint - Presentations	Function Add([WithWindow As MsoTriState = msoTrue]) As Presentation

When the Add method on the Documents collection is executed, you can specify the Visible argument to False so that you create the new document in the background. You can then add content to the document, and when you're done you can make the document visible. The following procedure copies the contents of the main text of the active document in Word and creates a new document with the copied content.

The procedure adds the document invisibly so the user doesn't see the content being pasted. It also applies the styles of the template attached to the active document to the new document. You can do a lot more document and content manipulation when the document is invisible. Word provides the Visible argument in both the Add and Open methods of the Documents collection object. The Visible argument is new to Word in Office 2000 and isn't available in Word 97.

Sub AddDocument() Dim docNewCopy As Document, sTmplName As String Dim iViewType As WdViewType With ActiveDocument .Range.Copy sTmplName = .AttachedTemplate.FullName End With iViewType = ActiveWindow.View.Type Set docNewCopy = Application.Documents.Add( _ Template:=sTmplName, Visible:=False) With docNewCopy .AttachedTemplate = ActiveDocument.AttachedTemplate .Range.Paste If Application.Visible = True Then .ActiveWindow.Visible = True End If .ActiveWindow.View.Type = iViewType End With End Sub

As the Set statement in this procedure shows, the Visible argument of the Add method of the Documents collection object is set to False. The new document is added to the Documents collection without a window (it's invisible). The Document object returned by the Add method is assigned to the docNewCopy variable. This variable is used in the With…End block to paste text in its main range as well as to attach the template used in the original document.

The line .ActiveWindow.Visible = True in the With…End block creates a new document window for the invisible document. The ActiveWindow property on the Document object returns a Window object in Word. The Visible property on the Window object is then set to True. This line should always be used to make an invisible Word document visible. The last line in the With…End block changes the view of the document window to the same type as the original document window.

Unlike Word and PowerPoint, Excel doesn't provide an argument to create a document invisibly. However, you can set the Visible property of a workbook window to False, manipulate its contents, and then make the workbook window visible again. In the following procedure, the Add method on the Workbooks collection object returns a Workbook object that's assigned to the wbNew variable. The visible state of the workbook's window is immediately set to False. The procedure adds a new workbook, changes its name to "My Data," and sets the workbook's window to True.

Sub AddWorkbook() Dim wbNew As Workbook, shNew As Worksheet Set wbNew = Application.Workbooks.Add With wbNew wbNew.Windows(1).Visible = False Set shNew = .Worksheets.Add shNew.Name = "My Data" wbNew.Windows(1).Visible = True End With End Sub

Within the With…End block, you could add or manipulate a lot more content, and you may want to do that when your program needs to create a new workbook, to insert data from a database into worksheets, to create new charts, and then to make the workbook visible to the user. In Chapter 8, this technique of creating an Excel workbook invisibly is used when a workbook containing a worksheet and chart is created from data in an Access database.

The one and only argument that PowerPoint provides in the Add method on the Presentations collection object is the WithWindow argument. The functionality provided by the WithWindow argument is equivalent to Word's Visible argument in the Add method of the Documents collection object. PowerPoint provides the WithWindow argument in both the Add and Open methods of the Presentations collection object. (PowerPoint 97 also provides this argument.) The following procedure adds a new presentation and assigns the Presentation object returned by the Add method to the presNew variable. Within the With…End block, you use a For…Next loop to add five slides. You set the text of each slide's title to "Slide" plus the number of the slide.

Sub AddPresentation() Dim sld As Slide, i As Integer Dim presNew As Presentation Set presNew = Presentations.Add(WithWindow:=msoFalse) With presNew For i = 1 To 5 Set sld = .Slides.Add(i, ppLayoutText) sld.Shapes.Title.TextFrame _ .TextRange = "Slide " & i Next i If Application.Visible = msoTrue Then .NewWindow End If End With End Sub

As in the previous examples in Word and Excel, you can do more content insertion and manipulation within the With…End block before making the invisible presentation visible. In Chapter 8, this technique of creating a PowerPoint presentation invisibly is used when a presentation is created from data in an Access database.