#1
|
|||
|
|||
Pls help - copying data from multiple word files
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] |
Thread Tools | |
Display Modes | |
|
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 |