Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-23-2021, 08:21 AM
handclips handclips is offline Pls help - copying data from multiple word files Windows 10 Pls help - copying data from multiple word files Office 2019
Novice
Pls help - copying data from multiple word files
 
Join Date: Jun 2020
Posts: 4
handclips is on a distinguished road
Default 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!!
Reply With Quote
  #2  
Old 01-24-2021, 12:20 AM
gmayor's Avatar
gmayor gmayor is offline Pls help - copying data from multiple word files Windows 10 Pls help - copying data from multiple word files Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #3  
Old 01-24-2021, 07:57 AM
handclips handclips is offline Pls help - copying data from multiple word files Windows 10 Pls help - copying data from multiple word files Office 2019
Novice
Pls help - copying data from multiple word files
 
Join Date: Jun 2020
Posts: 4
handclips is on a distinguished road
Default

thanks for your help! it works great
Reply With Quote
  #4  
Old 01-24-2021, 01:57 PM
macropod's Avatar
macropod macropod is offline Pls help - copying data from multiple word files Windows 10 Pls help - copying data from multiple word files Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

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
Pls help - copying data from multiple word files Copying multiple files as text without extensions Metamag Office 3 05-09-2011 06:25 PM
Pls help - copying data from multiple word files macro to pull data from multiple files psrs0810 Excel 2 10-25-2010 01:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:08 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft