Hi,
Found a very nice code to allow Word mail merge using an Access query starting the process with a form command button. The following version works like charm
Sub startMergeLB()
Dim oWord As Object
Dim oWdoc As Object
Dim wdInputName As String
Dim wdOutputName As String
Dim outFileName As String
' Set Template Path
'------------------------------------------------
'wdInputName = CurrentProject.path & "\AQHAPaymentOrderSigned.docx"
wdInputName = "D:\DOCS\OwnCloud\DBs\HQHA_Templates\AQHAPaymentOrderSigned.docx"
' Create unique save filename with minutes
' and seconds to prevent overwrite
'------------------------------------------------
outFileName = "AQHAPaymentOrder_" & Format(Now(), "yyyymmddmms")
' Output File Path w/outFileName
'------------------------------------------------
'wdOutputName = CurrentProject.path & "" & outFileName
wdOutputName = "P:\HQHA\DB_Directory\PaymentOrders" & outFileName
Set oWord = CreateObject("Word.Application")
Set oWdoc = oWord.Documents.Open(wdInputName)
' Start mail merge
'------------------------------------------------
With oWdoc.MailMerge
.MainDocumentType = 0 'wdFormLetters
.OpenDataSource _
Name:=CurrentProject.FullName, _
AddToRecentFiles:=False, _
LinkToSource:=True, _
Connection:="QUERY qrService", _
SQLStatement:="SELECT * FROM [qrService]"
.Destination = 0 'wdSendToNewDocument
.Execute Pause:=False
End With
' Hide Word During Merge
'------------------------------------------------
oWord.Visible = False
' Save file as PDF
' Uncomment the line below and comment out
' the line below "Save file as Word Document"
'------------------------------------------------
oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17
' Save file as Word Document
'------------------------------------------------
'oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16
' Quit Word to Save Memory
'------------------------------------------------
oWord.Quit savechanges:=False
' Clean up memory
'------------------------------------------------
Set oWord = Nothing
Set oWdoc = Nothing
End Sub
However i would like to able to apply a filter - just like in normal Word use - and tried to change to code to include a WHERE clause but it simple does not work. Searched pretty much but still unable to get it working. Here is my code
Sub startMergeLB()
Dim oWord As Object
Dim oWdoc As Object
Dim wdInputName As String
Dim wdOutputName As String
Dim outFileName As String
Dim strX As String
strX = "SELECT [tbServices.ServiceID] FROM [tbServices] WHERE (((tbServices.ServiceID)=1371));"
' Set Template Path
'------------------------------------------------
'wdInputName = CurrentProject.path & "\AQHAPaymentOrderSigned.docx"
wdInputName = "D:\DOCS\OwnCloud\DBs\HQHA_Templates\AQHAPaymentOrderSigned.docx"
' Create unique save filename with minutes
' and seconds to prevent overwrite
'------------------------------------------------
outFileName = "AQHAPaymentOrder_" & Format(Now(), "yyyymmddmms")
' Output File Path w/outFileName
'------------------------------------------------
'wdOutputName = CurrentProject.path & "" & outFileName
wdOutputName = "P:\HQHA\DB_Directory\PaymentOrders" & outFileName
Set oWord = CreateObject("Word.Application")
Set oWdoc = oWord.Documents.Open(wdInputName)
' Start mail merge
'------------------------------------------------
With oWdoc.MailMerge
.MainDocumentType = 0 'wdFormLetters
.OpenDataSource _
Name:=CurrentProject.FullName, _
AddToRecentFiles:=False, _
LinkToSource:=True, _
Connection:="QUERY qrService1", _
SQLStatement:=strX
.Destination = 0 'wdSendToNewDocument
.Execute Pause:=False
End With
' Hide Word During Merge
'------------------------------------------------
oWord.Visible = False
' Save file as PDF
' Uncomment the line below and comment out
' the line below "Save file as Word Document"
'------------------------------------------------
oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17
' Save file as Word Document
'------------------------------------------------
'oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16
' Quit Word to Save Memory
'------------------------------------------------
oWord.Quit savechanges:=False
' Clean up memory
'------------------------------------------------
Set oWord = Nothing
Set oWdoc = Nothing
End Sub
Tested all around and i can not figure out if the WHERE clause is simple the source of the problem or a syntax.
thanks
Wysy
Found a very nice code to allow Word mail merge using an Access query starting the process with a form command button. The following version works like charm
Sub startMergeLB()
Dim oWord As Object
Dim oWdoc As Object
Dim wdInputName As String
Dim wdOutputName As String
Dim outFileName As String
' Set Template Path
'------------------------------------------------
'wdInputName = CurrentProject.path & "\AQHAPaymentOrderSigned.docx"
wdInputName = "D:\DOCS\OwnCloud\DBs\HQHA_Templates\AQHAPaymentOrderSigned.docx"
' Create unique save filename with minutes
' and seconds to prevent overwrite
'------------------------------------------------
outFileName = "AQHAPaymentOrder_" & Format(Now(), "yyyymmddmms")
' Output File Path w/outFileName
'------------------------------------------------
'wdOutputName = CurrentProject.path & "" & outFileName
wdOutputName = "P:\HQHA\DB_Directory\PaymentOrders" & outFileName
Set oWord = CreateObject("Word.Application")
Set oWdoc = oWord.Documents.Open(wdInputName)
' Start mail merge
'------------------------------------------------
With oWdoc.MailMerge
.MainDocumentType = 0 'wdFormLetters
.OpenDataSource _
Name:=CurrentProject.FullName, _
AddToRecentFiles:=False, _
LinkToSource:=True, _
Connection:="QUERY qrService", _
SQLStatement:="SELECT * FROM [qrService]"
.Destination = 0 'wdSendToNewDocument
.Execute Pause:=False
End With
' Hide Word During Merge
'------------------------------------------------
oWord.Visible = False
' Save file as PDF
' Uncomment the line below and comment out
' the line below "Save file as Word Document"
'------------------------------------------------
oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17
' Save file as Word Document
'------------------------------------------------
'oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16
' Quit Word to Save Memory
'------------------------------------------------
oWord.Quit savechanges:=False
' Clean up memory
'------------------------------------------------
Set oWord = Nothing
Set oWdoc = Nothing
End Sub
However i would like to able to apply a filter - just like in normal Word use - and tried to change to code to include a WHERE clause but it simple does not work. Searched pretty much but still unable to get it working. Here is my code
Sub startMergeLB()
Dim oWord As Object
Dim oWdoc As Object
Dim wdInputName As String
Dim wdOutputName As String
Dim outFileName As String
Dim strX As String
strX = "SELECT [tbServices.ServiceID] FROM [tbServices] WHERE (((tbServices.ServiceID)=1371));"
' Set Template Path
'------------------------------------------------
'wdInputName = CurrentProject.path & "\AQHAPaymentOrderSigned.docx"
wdInputName = "D:\DOCS\OwnCloud\DBs\HQHA_Templates\AQHAPaymentOrderSigned.docx"
' Create unique save filename with minutes
' and seconds to prevent overwrite
'------------------------------------------------
outFileName = "AQHAPaymentOrder_" & Format(Now(), "yyyymmddmms")
' Output File Path w/outFileName
'------------------------------------------------
'wdOutputName = CurrentProject.path & "" & outFileName
wdOutputName = "P:\HQHA\DB_Directory\PaymentOrders" & outFileName
Set oWord = CreateObject("Word.Application")
Set oWdoc = oWord.Documents.Open(wdInputName)
' Start mail merge
'------------------------------------------------
With oWdoc.MailMerge
.MainDocumentType = 0 'wdFormLetters
.OpenDataSource _
Name:=CurrentProject.FullName, _
AddToRecentFiles:=False, _
LinkToSource:=True, _
Connection:="QUERY qrService1", _
SQLStatement:=strX
.Destination = 0 'wdSendToNewDocument
.Execute Pause:=False
End With
' Hide Word During Merge
'------------------------------------------------
oWord.Visible = False
' Save file as PDF
' Uncomment the line below and comment out
' the line below "Save file as Word Document"
'------------------------------------------------
oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17
' Save file as Word Document
'------------------------------------------------
'oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16
' Quit Word to Save Memory
'------------------------------------------------
oWord.Quit savechanges:=False
' Clean up memory
'------------------------------------------------
Set oWord = Nothing
Set oWdoc = Nothing
End Sub
Tested all around and i can not figure out if the WHERE clause is simple the source of the problem or a syntax.
thanks
Wysy