VBA automation: to append many word files into one PDF file

I have an increasing number (~60) of 1-2 page word files, and seek VBA automation to convert/append each word file, in a chosen order, into a single PDF file. Each appended word file should begin a new page in the pdf.

The word fileNames are listed in an xl sheet, together with their ORDER of occurrence in the PDF file.

As the word files proliferate in number, the ORDER value will enable me to shift word files into the most helpful order in the single PDF file.

The speed of assembly of the PDF is not very important, as the final PDF file will be distributed as such.

Thanks, Kelvin
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You could run some VBA code like this in the Excel Workbook with the document list,

It creates a fresh PDF file from all documents. It expects the ORDER to be in column 1 and the filenames to be in column 2 (no header row).

Note that it uses early binding, so needs a reference to the Microsoft Word Object Library.
Sub MakePDF()
    Dim wdDoc As Word.Document
    Dim wdApp As Word.Application
    Dim bNewApp As Boolean
    Dim sh As Worksheet
    Dim r As Integer
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    If wdApp Is Nothing Then
        Set wdApp = CreateObject("Word.Application")
        bNewApp = True
    End If
    Set wdDoc = wdApp.Documents.Add
    wdApp.Visible = True

    Set sh = ActiveWorkbook.Worksheets("Sheet1")
    With sh.Sort
        .SortFields.Add Key:=Range("A1"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange ActiveWorkbook.Worksheets("Sheet1").UsedRange
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    r = 1
    Do Until sh.Cells(r, 1).Value = ""
        If Len(wdDoc.Range) > 0 Then
            wdDoc.Bookmarks("\EndOfDoc").Range.InsertBreak wdPageBreak
        End If
        wdDoc.Bookmarks("\EndOfDoc").Range.InsertFile Filename:=sh.Cells(r, 2).Value
        r = r + 1
    wdDoc.SaveAs "C:\MyFolder\MyPDF.PDF", wdFormatPDF
    wdDoc.Close wdDoNotSaveChanges
    If bNewApp Then
    End If
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin4Author Commented:
Thanks for this speedy and response which should push me on quicker.
It will be two days before I can test it, so I'll pay up the points now and let you know how I get on.

Much appreciated
Kelvin4Author Commented:
It worked exactly as you said, thanks!
If I may ask one brief question?? - can you give a pointer to using vba to count the number of pages in a word or pdf document?

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Can't help with PDF, but with Word documents you can use:
iPageCount = ActiveDocument.Range.Information(wdNumberOfPagesInDocument)

Open in new window

Kelvin4Author Commented:
Thanks: that did it fine!
Kelvin4Author Commented:
A strange bug has appeared in the code you sent me, and the same occurred even when I re-pasted your original code into a new xl file. The problem follows the successful sorting of word file name data in "sheet1", its outcome is the the piece reports that the word file named in Row 1 of "Sheet1" cannot be found. However all the five word files to 'read' into the PDF file are present in the requisit folder, and their content is unchanged.

Word vba syntax is quite new to me, though I have made some progress toward reading the page number value for each word file.

 I'd like to ask you to comment on how I went about this.

SO! As this is new ground, i thought I'd issue these issues as a NEW question, and give you prior warning. Trust that is acceptable?  I will load the new question in 2 hours from now, when I've assembled the files, and done a final check...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.