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

Posted on 2014-08-19
Last Modified: 2014-08-21
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
Question by:Kelvin4
    LVL 76

    Accepted Solution

    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


    Author Closing Comment

    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

    Author Comment

    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?

    LVL 76

    Expert Comment

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

    Open in new window


    Author Comment

    Thanks: that did it fine!

    Author Comment

    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...


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    In a previous video Micro Tutorial here at Experts Exchange (, I explained how to get a free, one-month trial of Office …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now