![]() |
|
#1
|
|||
|
|||
|
Hi all
I want to make a VBA which can help to copy data from ALL word files in a folder to a template file that has preset header and footer. For example: 1) Folder has 12 word files (ie. File1 to File12) 2) VBA should open File1, copy select all contents and paste into template word file. 3) Afterwhich, save template word file as PDF in another folder 4) Pt 1 to 3 should repeat for all files in folder. In this case, since there are 12 word files, there should be 12 PDFs. Appreciate if someone can help with this pls? Thanks!!
|
|
#2
|
||||
|
||||
|
Without seeing either the template or the document content, the following should work to save the documents to PDF in the same folder and with the same names as the original documents.
Code:
Sub BatchToPDF()
'Graham Mayor - https://www.gmayor.com - Last updated - 24 Jan 2021
Dim strFile As String
Dim strPath As String
Dim strName As String
Dim oDoc As Document, oNewDoc As Document
Dim fDialog As FileDialog
Const strTemplate As String = "C:\Path\Letter.dotx" 'The name and path of the template
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
With fDialog
.Title = "Select folder and click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show <> -1 Then
MsgBox "Cancelled By User", , "List Folder Contents"
Exit Sub
End If
strPath = fDialog.SelectedItems.Item(1)
If Right(strPath, 1) <> "\" Then strPath = strPath + "\"
End With
strFile = Dir$(strPath & "*.docx")
While strFile <> ""
WordBasic.DisableAutoMacros 1
Set oDoc = Documents.Open(strPath & strFile)
Set oNewDoc = Documents.Add(strTemplate)
oNewDoc.Range.FormattedText = oDoc.Range.FormattedText
strName = Replace(oDoc.FullName, ".docx", ".pdf")
oDoc.Close 0
oNewDoc.ExportAsFixedFormat OutputFileName:=strName, _
ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, _
OptimizeFor:=wdExportOptimizeForPrint, _
Range:=wdExportAllDocument, from:=1, To:=1, _
Item:=wdExportDocumentContent, _
IncludeDocProps:=True, _
KeepIRM:=True, _
CreateBookmarks:=wdExportCreateHeadingBookmarks, _
DocStructureTags:=True, _
BitmapMissingFonts:=True, _
UseISO19005_1:=False
oNewDoc.Close 0
WordBasic.DisableAutoMacros 0
strFile = Dir$()
Wend
lbl_Exit:
Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
#3
|
|||
|
|||
|
thanks for your help! it works great
|
|
#4
|
||||
|
||||
|
Cross-posted at: Copying data from multiple word files
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Copying linked excel with word files | Firooz | Excel | 4 | 01-05-2016 12:17 AM |
| Extracting multiple words from one cell into individual rows while copying all other data | randyaserve | Excel Programming | 4 | 10-05-2015 09:52 AM |
| Data Import from multiple word files into an Excel tale | asmanokhchi | Word | 1 | 04-21-2015 06:24 AM |
Copying multiple files as text without extensions
|
Metamag | Office | 3 | 05-09-2011 06:25 PM |
macro to pull data from multiple files
|
psrs0810 | Excel | 2 | 10-25-2010 01:49 PM |