I have been trying to populate a merge document from access. I have the dotx files organised and the recipient file (Query) within my main access project.
The following code opens word, opens the selected file (dotx) but can't find the db file.
I am using access 2010 and .accdb database format. I have temporarily put in MsgBox lines to display the running content of fields and they are producing the right info.
Am I using the right code to do what I want?
------------------------------------------------------------------------------------
--------------------------------------------------------------------------
Any help would be greatly appreciated
The following code opens word, opens the selected file (dotx) but can't find the db file.
I am using access 2010 and .accdb database format. I have temporarily put in MsgBox lines to display the running content of fields and they are producing the right info.
Am I using the right code to do what I want?
------------------------------------------------------------------------------------
Private Sub DocumentsCbo_Click()
' load precedent template as a dotx, merge fields into precedent document, allow user to modify if needed, save it as a pdf
On Error GoTo ErrTrap
Dim MyPath As String ' Path of the source template
Dim DestPath As String ' Path of destination document
'MyPath = Me.FolderPath & Me.TemplateFileName
MyPath = "Z:\INVESTREND DATA\COMPANIES\Options Group\Collection & Recovery Options Pty Ltd\CRO Precedents 2015\CRO3-01.dotx"
dbPath = "Z:\INVESTREND DATA\COMPANIES\Options Group\TOGAS\TOGAS Copy\Development\TOGAS-Development-20141113am.accdb"
DestPath = "Z:\INVESTREND DATA\COMPANIES\Options Group\TOGAS\ClientMergeFiles"
DocName = "\ClientReturnsRetainer.docx"
dbSourcePath = dbPath
MyStr = MyPath
MsgBox "MyPath = " & MyPath
MsgBox "MyStr = " & MyStr
MsgBox "DestPath = " & DestPath
MsgBox "dbPath = " & dbPath
MsgBox "DocName = " & DocName
MsgBox "dbSourcePath = " & dbSourcePath
Dim objWord As Object, strFile As String
strFile = MyStr
Set objWord = CreateObject("Word.Application")
'open word file
objWord.Documents.Open strFile
objWord.ActiveDocument.SaveAs DestPath & DocName ' save the document
'objWord.Documents.Add strFile
'Make word visible.
objWord.Application.Visible = True
'Set mail merge data source as the TOGAS Project
objWord.ActiveDocument.MailMerge.OpenDataSource Name:="Z:\INVESTREND DATA\COMPANIES\Options Group\TOGAS\TOGAS Copy\Development\TOGAS-Development-20141113am.accdb", LinkToSource:=True, Connection:="Query PrecedentsQry", SQLStatement:="SELECT * FROM [PrecedentsQry]"
'Execute the Mail Merge
objWord.ActiveDocument.MailMerge.Execute
objWord.ActiveDocument.SaveAs DestPath & "filename"
objWord.Quit 0
'Reopen document
Set objWord = CreateObject("Word.Application")
objWord.Documents.Open DestPath & "filename"
obWord.Applicatiom.Visible = True
Set objWord = Nothing
Exit Sub
ErrTrap:
MsgBox Err.Description, vbCritical
End Sub
--------------------------------------------------------------------------
Any help would be greatly appreciated