C
Cooz
Hi everyone,
This VBA function accomplishes a mail merge in Word (Office 2003):
Function MMWord(DotName As String, QueryName As String)
Dim blnWordAlreadyRunning As Boolean
Dim strSQL As String
On Error GoTo ErrorTrap
blnWordAlreadyRunning = True
Set objWord = GetObject(, "Word.Application") ' Error 429 if Word was
not open
With objWord
.Documents.Open path & DotName
strSQL = "SELECT * FROM [" & QueryName & "]"
.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
.ActiveDocument.MailMerge.OpenDataSource Name:=path &
"Sollicitaties.mdb", SQLStatement:=strSQL
....
ErrorTrap:
Select Case Err.Number
Case 429 ' ActiveX cannot make object
Set objWord = CreateObject("Word.Application") ' start Word
blnWordAlreadyRunning = False
Resume Next ' continue
Case Else
Error Err.Number
End Select
End Function
"..." stands for code that is working fine and that is not particularly
relevant here. "path" holds the correct path to Sollicitaties.mdb.
DotName is the name of a Word-document that contains mergefields and no VBA
Code. QueryName is name of the data source, and yes, that's a query.
All lines of code function flawlessly up until the one directly above "..."
– the one with ".OpenDataSource". This line coughs up a dialog called
"Confirm data source" (or rather "Gegevensbestand bevestigen" since I use the
Dutch version of Access) where I am forced to select a mail merge method:
"OLE-DB Database files", "MS-Access databases via DDE (*.mdb, *.mde)" and
others.
How can I prevent this dialog from appearing? In addition, error 4198
"Command failed"/"Opdracht mislukt" occurs when I click OK. Until this
morning it seemed that the macro worked fine if the query did not contain a
WHERE-clause – however today I came across a situation in which the dialog
appeared even though the query was WHERE-clauseless. Bummer.
The following provides no solution:
- Use DDE (.OpenDataSource SubType:=wdMergeSubTypeWord2000)
- strSQL = CurrentDB.QueryDefs(strQueryIntern).SQL (and tweak strSQL in such
a way – remove the ";" at the end and such – that .OpenDataSource can work
with it)
- letting Word perform the mail merge – this yields the same result.
- In Word: (Un)check Tools | Options... | tab General | Confirm conversions
on open.
Any help in this matter will be greatly appreciated.
Thank you,
Cooz
This VBA function accomplishes a mail merge in Word (Office 2003):
Function MMWord(DotName As String, QueryName As String)
Dim blnWordAlreadyRunning As Boolean
Dim strSQL As String
On Error GoTo ErrorTrap
blnWordAlreadyRunning = True
Set objWord = GetObject(, "Word.Application") ' Error 429 if Word was
not open
With objWord
.Documents.Open path & DotName
strSQL = "SELECT * FROM [" & QueryName & "]"
.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
.ActiveDocument.MailMerge.OpenDataSource Name:=path &
"Sollicitaties.mdb", SQLStatement:=strSQL
....
ErrorTrap:
Select Case Err.Number
Case 429 ' ActiveX cannot make object
Set objWord = CreateObject("Word.Application") ' start Word
blnWordAlreadyRunning = False
Resume Next ' continue
Case Else
Error Err.Number
End Select
End Function
"..." stands for code that is working fine and that is not particularly
relevant here. "path" holds the correct path to Sollicitaties.mdb.
DotName is the name of a Word-document that contains mergefields and no VBA
Code. QueryName is name of the data source, and yes, that's a query.
All lines of code function flawlessly up until the one directly above "..."
– the one with ".OpenDataSource". This line coughs up a dialog called
"Confirm data source" (or rather "Gegevensbestand bevestigen" since I use the
Dutch version of Access) where I am forced to select a mail merge method:
"OLE-DB Database files", "MS-Access databases via DDE (*.mdb, *.mde)" and
others.
How can I prevent this dialog from appearing? In addition, error 4198
"Command failed"/"Opdracht mislukt" occurs when I click OK. Until this
morning it seemed that the macro worked fine if the query did not contain a
WHERE-clause – however today I came across a situation in which the dialog
appeared even though the query was WHERE-clauseless. Bummer.
The following provides no solution:
- Use DDE (.OpenDataSource SubType:=wdMergeSubTypeWord2000)
- strSQL = CurrentDB.QueryDefs(strQueryIntern).SQL (and tweak strSQL in such
a way – remove the ";" at the end and such – that .OpenDataSource can work
with it)
- letting Word perform the mail merge – this yields the same result.
- In Word: (Un)check Tools | Options... | tab General | Confirm conversions
on open.
Any help in this matter will be greatly appreciated.
Thank you,
Cooz