1

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
Corey
  • 41
  • 2
  • 2
  • 4

1 Answers1

1

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.

Community
  • 1
  • 1
Eugene
  • 2,820
  • 19
  • 24
  • I am hoping to find files within a set, static directory and then merge using their filenames. I would have a variable string and a static string for each of two files, and a static file and directory for the third. I suspect it would be something like: merge "*[static RootDoc append].pdf" with "*[static DefDoc append].pdf" by adding *[static RootDoc append].pdf" after page 0 (a cover page) – Corey Apr 14 '15 at 18:05
  • The reason I'm thinking in VBA is that there are other set scripts I have that run on the source excel docs to convert to pdf. – Corey Apr 14 '15 at 18:11
  • It is not quite clear to me but if seems like you should first 1) find all PDF files in the given folder (like this http://www.mrexcel.com/forum/excel-questions/396659-visual-basic-applications-macro-list-all-tif-pdf-files-folder-including-subfolders.html ) and then 2) process each file in the folder and merge with 2 others? – Eugene Apr 14 '15 at 18:58
  • I am able to generate a list of all filenames and directories to be used in a set of 3 columns. I am thinking of using a For Each loop for the range of rows and then column offsets to open each file. I am still unclear on how to word the merge operation. – Corey Apr 15 '15 at 19:10