A
Antoine
Hi!
I have a question regarding Mail Merging Word 2003 documents with Access
2003 data.
My requirement is:
to have a form in Access 2003 with a button -done.
to have a Word 2003 doc with Merged Fields from the Wizard -done.
to have the Access form's button create one Word 2003 document for
each row in the corresponding table.
to have each newly created document saved to a specified folder.
to have this process done without the user seeing anything other then a
progress bar.
My issues are how to:
have the documents created where I want
have the original Mail Merge document opened from Access without some
weird db connection issues
make the process invisible
make an appropriate progress bar.
The way I worked it out up til now, is to use the Word 2003 Mail Merge
Wizard, and then have a form in access with a button. My understanding is
pretty basic.
This is the code I came up with up to now. Please help me resolve these
issues...
thank you!
Option Compare Database
'Private Declare Function MoveFile& Lib "kernel32" Alias "MoveFileA" _
' (ByVal lpExistingFileName As String, _
' ByVal lpNewFileName As String) as long
Private Sub cmd_Click()
MergeTheSuckers
End Sub
Public Function MergeTheSuckers()
Dim x As Integer
Dim nRec As Long
Dim SQL As String
Dim filename As String
Dim newfilename As String
Dim newpath As String
Dim oldpath As String
Dim newdirpath As String
Dim rs As ADODB.Recordset
Dim objWord As Word.Document
Dim obj As Word.Document
oldpath = Application.CurrentProject.Path & "\" & "My Documents"
newdirpath = Application.CurrentProject.Path & "\" & "The Temp Docs from
hell"
MkDir newdirpath
Set rs = New ADODB.Recordset
SQL = "SELECT * FROM 1MailMerge"
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockPessimistic
nRec = rs.RecordCount
rs.MoveFirst
x = 0
Set objWord = CreateObject(Application.CurrentProject.Path & "\TEST.doc")
objWord.Application.Visible = True
While x < nRec
With objWord.MailMerge
.DataSource.FirstRecord = x
.DataSource.LastRecord = x
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
rs.MoveFirst
rs.Move x
filename = rs!LastName
newfilename = filename
filename = filename & "1" & ".doc"
ActiveDocument.SaveAs filename
ActiveDocument.Close
x = x + 1
' oldpath = oldpath & "\" & filename
' newdirpath = newdirpath & "\" & newfilename
'
' c = MoveFile(oldpath, newdirpath)
' If c = 0 Then ' failure
' MsgBox "Copy failed -- " & newdirpath & " already exists."
' End If
'
End With
Wend
End Function
I have a question regarding Mail Merging Word 2003 documents with Access
2003 data.
My requirement is:
to have a form in Access 2003 with a button -done.
to have a Word 2003 doc with Merged Fields from the Wizard -done.
to have the Access form's button create one Word 2003 document for
each row in the corresponding table.
to have each newly created document saved to a specified folder.
to have this process done without the user seeing anything other then a
progress bar.
My issues are how to:
have the documents created where I want
have the original Mail Merge document opened from Access without some
weird db connection issues
make the process invisible
make an appropriate progress bar.
The way I worked it out up til now, is to use the Word 2003 Mail Merge
Wizard, and then have a form in access with a button. My understanding is
pretty basic.
This is the code I came up with up to now. Please help me resolve these
issues...
thank you!Option Compare Database
'Private Declare Function MoveFile& Lib "kernel32" Alias "MoveFileA" _
' (ByVal lpExistingFileName As String, _
' ByVal lpNewFileName As String) as long
Private Sub cmd_Click()
MergeTheSuckers
End Sub
Public Function MergeTheSuckers()
Dim x As Integer
Dim nRec As Long
Dim SQL As String
Dim filename As String
Dim newfilename As String
Dim newpath As String
Dim oldpath As String
Dim newdirpath As String
Dim rs As ADODB.Recordset
Dim objWord As Word.Document
Dim obj As Word.Document
oldpath = Application.CurrentProject.Path & "\" & "My Documents"
newdirpath = Application.CurrentProject.Path & "\" & "The Temp Docs from
hell"
MkDir newdirpath
Set rs = New ADODB.Recordset
SQL = "SELECT * FROM 1MailMerge"
rs.Open SQL, CurrentProject.Connection, adOpenStatic, adLockPessimistic
nRec = rs.RecordCount
rs.MoveFirst
x = 0
Set objWord = CreateObject(Application.CurrentProject.Path & "\TEST.doc")
objWord.Application.Visible = True
While x < nRec
With objWord.MailMerge
.DataSource.FirstRecord = x
.DataSource.LastRecord = x
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
rs.MoveFirst
rs.Move x
filename = rs!LastName
newfilename = filename
filename = filename & "1" & ".doc"
ActiveDocument.SaveAs filename
ActiveDocument.Close
x = x + 1
' oldpath = oldpath & "\" & filename
' newdirpath = newdirpath & "\" & newfilename
'
' c = MoveFile(oldpath, newdirpath)
' If c = 0 Then ' failure
' MsgBox "Copy failed -- " & newdirpath & " already exists."
' End If
'
End With
Wend
End Function