Excel VBA

css navigation by Css3Menu.com

Fill Word Template and Print to PDF

We started this project creating tables in SQL Server that are truncated and refilled weekly.

Somebody on the team exports the data to a secure site we have so that the data can be used for a Word Mail-merge.

Because the files have to be printed to PDF; mail-merge was too unwieldly. In the end, I replaced the merge fields with Bookmarks in Word.

'Cells are defined by my Enum; 2 Enums in place for parameters
Workbooks.OpenText Filename:= _
    GetPath & Cells(C,LtrV.Fname) & .txt
,Origin:= 437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= xlDoubleQuote, _
ConsecutiveDelimiter:= False,Tab:= True, Semicolon:= False, Comma:= False, Space:= False, _
Other:=True, OtherChar:= |
WB=ActiveWorkbook.Name
Workbooks(WB).Activate
R=Range(A65000).End(xlUp).Row   'Get last row
For C = 2 to R   'Second row of workbook to end
'---------------
WDapp.Documents.Open Filename:= Pather &  daFileName.DOCX   'Word file is open
WDapp.Visible = True
WDapp.ActiveDocument.Bookmarks(TheBookmarkName).Range.InsertAfter cells(C,DataSht.PersonName)
WDapp.ActiveDocument.Bookmarks(NextBookmarkName).Range.InsertAfter cells(C,DataSht.PersonAddress)
WDapp.ActiveDocument.ExportAsFixedFormat OutputFileName:= DaPath & GeorgresFile.PDF,
ExportFormat:= wdExportFormatPDF
WDapp.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Word file is closed before starting next
Next C

Because we had four kinds of documents that may or may not contain data; I would go thru each one. If it did not contain data, it was closed immediately and the Word routine was not started.

© 2020-2024

Updated:  06/21/2024 07:42
This page added:  02 June 2020