CODEWITHSUNDEEP
vbaexcelpdfmergepdf-generation
Corey
Corey

Reputation: 41

Open and Merge multiple PDF files using VBA

I am trying to open three pdf files in order to merge them. We create a series of files on a monthly basis that are all identified by "ticker".

I have three source files:

RootDoc = Root file, produced monthly, variable filename: "[variable ticker] + [static RootDoc append].pdf"

DefDoc = Static file, variable filename, "[variable ticker] + [static DefDoc append].pdf"

NotesDoc = One file, does not change, must be included in all docs.

I'm stuck on how to merge appropriately under the following conditions:

1) The number of ticker-labeled files in the folder each month will vary.

2) The RootDoc and DefDoc of the same ticker must be paired together as they contain ticker-specific information. The tickers will match on both files, but will vary in character length from one set of files to the next.

3) I have a footer format saved in Adobe that must be applied to all docs. (this is not necessary but would be nice to have, I can do this outside of VBA if necessary)

Here's what I have so far, with notations commented out for clarity:

With Application
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

Dim Filename, Pathname, PRODname, Defname As String
Dim RootDoc, NotesDoc, DefDoc As AcroPDDoc
Dim stp1, stp2, stp3, mergefile As Variant

Pathname = "H:\apps\xp\Desktop\PROD CHECK\Expanded LT"
'PRODname = "*[static RootDoc append].pdf"
Filename = Dir(Pathname & "\" & PRODname)
'Defname = "*[static DefDoc append].pdf"

Set RootDoc = CreateObject("AcroExch.PDDoc")
Set NotesDoc = CreateObject("AcroExch.PDDoc")
Set DefDoc = CreateObject("AcroExch.PDDoc")

'stp1 = NotesDoc.Open("[unchanging file name and location]")

Do While Filename <> ""
    'stp2 = RootDoc.Open(Pathname & "\" & Filename)
    'stp3 = DefDoc.Open("[location]" & "\" & Defname)
        'mergefile = RootDoc.InsertPages([I want to insert all of DefDoc into RootDoc after page 0])
        'mergefile = RootDoc.InsertPages([I want to insert all of NotesDoc after the last page of RootDoc])
        DefDoc.Close SaveChanges:=False
    'RootDoc = [Apply Saved Footer]
    RootDoc.Close SaveChanges:=True
    Filename = Dir()
Loop

With Application
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

Upvotes: 1

Views: 14608

Answers (1)

Eugene
Eugene

Reputation: 2878

Is this question about finding pdf files to merge using their filenames or about merging PDF files using Acrobat API through VBA?

In case you need to find the best workflow then I suppose you should find all files in the folder and find the find the "ticker" filename using regular expressions (see this awesome answer about regular expressions in VBA and this tutorial on matching filenames using regular expressions in VBA).

In case you are looking for a proper way to use Acrobat API to merge pages from one pdf documents into another one then you should check the online documentation for Acrobat API (see Javascript / Javascript for Acrobat... / Javascript API/ Doc / InsertPages section) and this sample VB code.

Upvotes: 1

Related Questions