I have been searching everywhere to try to figure out how to use Access to open an existing Word document that is merged to an Excel spreadsheet and merge the data when it opens. I'm working with two different version, Word 2000 and 2003. I got it to work perfect in 2000, but when I use it in 2003, the Word document opens and it is not linked to a data source.
The closest thing I have found to help me out is a function written on this site called MergeIt(), but when I compile, this line, "Dim objDoc As Word.Document," gives me this error: Compile error: User-defined type not defined.
What am I doing wrong? Any help is GREATLY appreciated. I have already spent way too much time on trying to figure this one out.
Here is the code I'm trying to use:
Function MergeIt()
On Error GoTo ErrHandling
Dim objDoc As Word.Document
Dim objWord As Word.Application
Dim blnCreated As Boolean
Dim strFilename As String
Dim strQueryName As String
Dim strDBpath As String
strFilename = "e:\SUMMONSFORM.DOC"
strQueryName = "qryOWNERCODEFENDANTsummonsmergedata"
strDBpath = "E:\CityTaxSaleV63.mdb" 'Or probably just CurrentDb.Name
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err Then
Set objWord = CreateObject("Word.Application")
blnCreated = True
End If
On Error GoTo ErrHandling
Set objDoc = objWord.Documents.Open(strFilename)
'Make Word Visible
objWord.Visible = True
'Execute the MailMerge
With objDoc.MailMerge
'Set Merge Data Source
objDoc.MailMerge.OpenDataSource Name:=strDBpath, _
LinktoSource:=True, _
Connection:="QUERY " & strQueryName, _
SQLStatement:="SELECT * FROM " & strQueryName
.Destination = wdSendToNewDocument
.Execute
objWord.ActiveDocument.PrintOut False
objWord.ActiveDocument.Close wdDoNotSaveChanges
End With
'Close The form files and the merged document
objDoc.Close wdDoNotSaveChanges
If blnCreated Then
objWord.Quit
End If
Set objDoc = Nothing
Set objWord = Nothing
Exit Function
ErrHandling:
msgbox "Whoops" 'Better error handling of course
End Function
Thanks!
Jody