[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1939
  • Last Modified:

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
0
Kelvin4
Asked:
Kelvin4
  • 4
  • 2
1 Solution
 
GrahamSkanCommented:
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
        .Apply
    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
    Loop
    wdDoc.SaveAs "C:\MyFolder\MyPDF.PDF", wdFormatPDF
    wdDoc.Close wdDoNotSaveChanges
    If bNewApp Then
        wdApp.Quit
    End If
End Sub

Open in new window

0
 
Kelvin4Author Commented:
Graham:
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
Kelvin
0
 
Kelvin4Author Commented:
Graham:
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?

Cheers/
Kelvin
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Open in new window

0
 
Kelvin4Author Commented:
Thanks: that did it fine!
0
 
Kelvin4Author Commented:
Graham:
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...

regards,
Kelvin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now