-
You need to get the string for the SQLStatement. Record a simple merge to get what you need. e.g. Open a new file, add one field, do the merge.
I used a txt file for this merge example as a standard routine in MSWord's VBA. The FRM file is the DOC file of course. Notice how I suppressed the initial merge in the Documents.Open. I initially did this project in WordPerfect for a consultant fee. They then asked for an MSWord solution and I gave it to them but they never paid for that. Oh well, so much for consulting.
Once you have the SQL string, that can easily be added to this routine.
[VBA]Sub MergeRun(frmFile As String, datFile As String, _
Optional bClose As Boolean = True, Optional bPrint As Boolean = True, _
Optional iNoCopies As Integer = 1)
If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub
'On Error GoTo endnow
Application.DisplayAlerts = wdAlertsNone
'Open form file and associate data file
Documents.Open frmFile, False, True, False
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource name:=datFile, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
'Merge to a new document
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
If bPrint = True Then
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=iNoCopies, Pages:="", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
End If
If bClose = True Then
ActiveDocument.Close False
ActiveDocument.Close False
End If
endnow:
Application.DisplayAlerts = wdAlertsAll
End Sub
[/VBA]
Of course this routine needs tweaked to add the MSWord references to use it as a standard Excel routine.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules