vbCity/DevCity.NET Forums :: .NET :: VB.NET :: FAQ :: Ultimate Guide To Office Automation. Part 1

Version 2.0

I have seen way too many posts and answered way too many questions to have me believe this is not yet a frequently asked question. So Starting now I am going to begin compiling a guide to using, manipulating, and creating documents in MS Office using VB .NET.

============
MS Word
============

1) Creating a new Document

Hey you gotta start somewhere.
First make sure you import the Word Object Library to your project.
Now that you have access to the Word Object Library you need to have create an instance of the application

Code:

'office xp
   Dim wordApp As Word.Application
   wordApp = CreateObject("Word.Application")
'office 2000
   Dim wordApp As Word.Application
   wordApp = New Word.Application
'office 97
   Dim wordApp As Word.ApplicationClass
   wordApp = New Word.Application

Although with most objects you create a new instance of it using the New keyword, with OfficeXP it is best to Explicitly define it with the CreateObject method due to a bug that exists in Office 97, which is why we create the Object with the ApplicationClass and not the Application Object.
Now that we have a running Application, we are going to want to create a document, so ...

Code:

'XP and 2000
Dim wordDoc As Word.Document
'97, once again the 97 bug
Dim wordDoc As Word.DocumentClass

Now that we have an identifier for our document we are going to want to create a new document and play with it.

Code:

Dim wordRng As Word.Range
   Dim wordPara As Word.Paragraph

   With WordApp
      .WindowState = Word.WdWindowState.wdWindowStateMaximize
      .Documents.Add
      wordDoc = wordApp.ActiveDocument
      wordRng = wordDoc.Range

      With wordRng

         .Font.Bold = True
         .Font.Italic = True
         .Font.Size = 24
         'since this is going to be the first bit of text, you could just use the .Text field, but it is better practice
         'to prevent data lose to use .InsertAfter, anytime you enter data into the .Text field, it erases the pervious
         'info and replaces it with the new, .InsertAfter does the same as .Text += "anything"
         .InsertAfter "Word Automation"
         .InsertParagraphAfter

         ' Insert a blank paragraph between the two paragraphs.
         .InsertParagraphAfter

      End With

      'note, for non XP users, use wordPara = wordRng.Paragraphs.Item(3)
      wordPara = wordRng.Paragraphs(3)

      With wordPara.Range

         .Bold = False
         .Italic = True
         .Font.Size = 14
         .InsertAfter "Document Created On: "
         .Collapse(Direction:=Word.WdCollapseDirection.wdCollapseEnd)
         .InsertDateTime DateTimeFormat:="MM-DD-YY HH:MM:SS"

      End With

      .ActiveDocument.SaveAs "c:\My Documents\createDoc.Doc"
      .ActiveDocument.Saved = True

      .Quit

   End With

That should give you some idea of using the object library. Now this final step is optional but recommended. To help the garbage collector and to increase speed and effectively use memory, set all your large objects to nothing when finished with them. This will allow them to be taken out of memory a lot easier.

Code:

wordPara = Nothing
   wordRng = Nothing
   wordDoc = Nothing
   wordApp = Nothing

2) Opening a Word Document

OK, first create the application and document as before

Code:

'for xp users it would be
   Dim wordApp As Word.Application
   Dim wordDoc As Word.Document

   ' Create new hidden instance of Word.
   wordApp = CreateObject("Word.Application")

Now invoke the Application.Documents.Open(...) method

Code:

wordDoc = wordApp.Documents.Open(FileName:=docName)

The open method has a lot of different possible parameters. For lack of a better way to put it, here they are
  FileName - If you dont understand this one, you shouldnt be here
  ConfirmConversions - Set to true if the you want the convert file dialog to appear if the file is not an MS Word doc
  ReadOnly - Set to True if you dont want the ability to modify the text
  AddToRecentFiles - Set to true if you want the Word doc in the Recent Files listing
  Revert - If the file is already open, True will discard all unsaved changes and open file from disk, False will activate current
  Format - Specifies file converter; wdOpenFormatAuto, wdOpenFormatDocument, wdOpenFormatTemplate, wdOpenFormatRTF, wdOpenFormatText, wdOpenFormatUnicodeText
  Password - If the doc needs a password to open, this is where you put it
  WritePassWordDocument - if to modify the doc you need a password, you put it here
  PasswordTemplate - If the doc is based on a template, you put that password here
  WritePasswordTemplate - To change template, password Here
Now that that is over with, do anything you want and quit

Code:

' Display document name and count of words, and then close
   ' document without saving changes.
   With wordDoc
       accessWordDoc = "'" & .Name & "' contains " & .Words.Count & " words."
       .Close wdDoNotSaveChanges
   End With

   wordApp.Quit(False)
   Set wordApp = Nothing

For anyone, like myself, who still has to use Word 97, you will recieve the error

Quote:

'close' is ambiguous across the inherited interfaces 'Word._Document' and 'Word.DocumentEvents_Event'.

to get around this, force the type of object with the current close/quit method

Code:

CType(.ActiveDocument, Word._Document).Close()

I've seen that questions asked about 10 times in 3 months so I am going to keep repeating it.

3) Inserting Data into Word from an MS Access Form

Here is the code to do this in Word XP:-

Code:

Private Sub MergeButton_Click()
   On Error GoTo MergeButton_Err
   Dim objWord As Word.Application
   ' Get path of the photo.
   Dim sImagePath As String
   sImagePath = ""
   If InStr(2, ImagePath.Value, ":") Or InStr(1, ImagePath.Value, "\") Then
      sImagePath = ImagePath.Value 'path was complete
   Else ' ImagePath is to file in folder of this database.
      sImagePath = Me.Application.CurrentProject.path & "\" & ImagePath.Value
   End If

   'Start Microsoft Word.
   Set objWord = CreateObject("Word.Application")
   With objWord ' Make the application visible.
      .Visible = True
      ' Open the document.
      .Documents.Open ("c:\MyDocs\mymerge.doc")
      ' Move to each bookmark and insert text from the form.
      .ActiveDocument.Bookmarks("First").Select
      .Selection.Text = (CStr(Forms!Employees!FirstName))
      .ActiveDocument.Bookmarks("Last").Select
      .Selection.Text = (CStr(Forms!Employees!LastName))
      .ActiveDocument.Bookmarks("Address").Select
      .Selection.Text = (CStr(Forms!Employees!Address))
      .ActiveDocument.Bookmarks("City").Select
      .Selection.Text = (CStr(Forms!Employees!City))
      .ActiveDocument.Bookmarks("Region").Select
      .Selection.Text = (CStr(Forms!Employees!Region))
      .ActiveDocument.Bookmarks("PostalCode").Select
      .Selection.Text = (CStr(Forms!Employees!PostalCode))
      .ActiveDocument.Bookmarks("Greeting").Select
      .Selection.Text = (CStr(Forms!Employees!FirstName))

      ' Insert the photo.
      .ActiveDocument.Bookmarks("Photo").Select
      .Selection.InlineShapes.addpicture fileName:=sImagePath, _
      LinkToFile:=False, SaveWithDocument:=True
   End With

   ' Print the document in the foreground so that Word
   ' does not close until the document is finished printing.
   objWord.ActiveDocument.PrintOut Background:=False
   ' Close the document without saving changes.
   objWord.ActiveDocument.Close

   SaveChanges:=wdDoNotSaveChanges
   ' Quit Microsoft Word and release the object variable.
   objWord.Quit
   Set objWord = Nothing

   Exit Sub

MergeButton_Err:
   ' If a field on the form is empty,
   ' remove the bookmark text and continue.
   If Err.Number = 94 Then
      objWord.Selection.Text = ""
      Resume Next ' If the Photo field is empty...
   ElseIf Err.Number = 2046 Then
      MsgBox "Please add a photo to this record and try again."
   Else
      MsgBox Err.Number & vbCr & Err.Description
   End If
   Exit Sub
End Sub