PDA

View Full Version : [SOLVED] Need code to merge PDF files in a folder using adobe acrobat X



rockybalboa
08-26-2013, 11:28 PM
Hi,

I need a code to merge all the PDF files in a folder using adobe acrobat X full version. I will provide the link to the folder in Range E3. Can anybody help me with this.

Thanks:yes

ZVI
08-27-2013, 02:08 AM
Hi,
Set reference to VBE - Tools - References – Acrobat
and try this code:


Sub MergePDFs()
' ZVI:2013-08-27 http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X
' Reference required: "VBE - Tools - References - Acrobat"

' --> Settings, change to suit
Const MyPath = "C:\Temp" ' Path where PDF files are stored
Const MyFiles = "1.pdf,2.pdf,3.pdf" ' List of PDFs to ne merged
Const DestFile = "MergedFile.pdf" ' The name of the merged file
' <-- End of settings

Dim a As Variant, i As Long, n As Long, ni As Long, p As String
Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc

If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
a = Split(MyFiles, ",")
ReDim PartDocs(0 To UBound(a))

On Error GoTo exit_
If Len(Dir(p & DestFile)) Then Kill p & DestFile
For i = 0 To UBound(a)
' Check PDF file presence
If Dir(p & Trim(a(i))) = "" Then
MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
Exit For
End If
' Open PDF document
Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
PartDocs(i).Open p & Trim(a(i))
If i Then
' Merge PDF to PartDocs(0) document
ni = PartDocs(i).GetNumPages()
If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
End If
' Calc the number of pages in the merged document
n = n + ni
' Release the memory
PartDocs(i).Close
Set PartDocs(i) = Nothing
Else
' Calc the number of pages in PartDocs(0) document
n = PartDocs(0).GetNumPages()
End If
Next

If i > UBound(a) Then
' Save the merged document to DestFile
If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
MsgBox "Cannot save the resulting document" & vbLf & p & DestFile, vbExclamation, "Canceled"
End If
End If

exit_:

' Inform about error/success
If Err Then
MsgBox Err.Description, vbCritical, "Error #" & Err.Number
ElseIf i > UBound(a) Then
MsgBox "The resulting file is created:" & vbLf & p & DestFile, vbInformation, "Done"
End If

' Release the memory
If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
Set PartDocs(0) = Nothing

' Quit Acrobat application
AcroApp.Exit
Set AcroApp = Nothing

End Sub

rockybalboa
08-27-2013, 04:50 AM
Hi thanks for the code. Only one problem in Const MyFiles i cannot name all the PDF files because there are many files and will change every time. Can this be done in such a way that all the pdf files in the folder will be merged.

ZVI
08-27-2013, 06:44 AM
Well, then try this one:


Sub Main()

Const DestFile As String = "MergedFile.pdf" ' <-- change to suit

Dim MyPath As String, MyFiles As String
Dim a() As String, i As Long, f As String

' Choose the folder or just replace that part by: MyPath = Range("E3")
With Application.FileDialog(msoFileDialogFolderPicker)
'.InitialFileName = "C:\Temp\"
.AllowMultiSelect = False
If .Show = False Then Exit Sub
MyPath = .SelectedItems(1)
DoEvents
End With

' Populate the array a() by PDF file names
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
ReDim a(1 To 2 ^ 14)
f = Dir(MyPath & "*.pdf")
While Len(f)
If StrComp(f, DestFile, vbTextCompare) Then
i = i + 1
a(i) = f
End If
f = Dir()
Wend

' Merge PDFs
If i Then
ReDim Preserve a(1 To i)
MyFiles = Join(a, ",")
Application.StatusBar = "Merging, please wait ..."
Call MergePDFs(MyPath, MyFiles, DestFile)
Application.StatusBar = False
Else
MsgBox "No PDF files found in" & vbLf & MyPath, vbExclamation, "Canceled"
End If

End Sub

Sub MergePDFs(MyPath As String, MyFiles As String, Optional DestFile As String = "MergedFile.pdf")
' ZVI:2013-08-27 http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X
' Reference required: VBE - Tools - References - Acrobat

Dim a As Variant, i As Long, n As Long, ni As Long, p As String
Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc

If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
a = Split(MyFiles, ",")
ReDim PartDocs(0 To UBound(a))

On Error GoTo exit_
If Len(Dir(p & DestFile)) Then Kill p & DestFile
For i = 0 To UBound(a)
' Check PDF file presence
If Dir(p & Trim(a(i))) = "" Then
MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
Exit For
End If
' Open PDF document
Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
PartDocs(i).Open p & Trim(a(i))
If i Then
' Merge PDF to PartDocs(0) document
ni = PartDocs(i).GetNumPages()
If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
End If
' Calc the number of pages in the merged document
n = n + ni
' Release the memory
PartDocs(i).Close
Set PartDocs(i) = Nothing
Else
' Calc the number of pages in PartDocs(0) document
n = PartDocs(0).GetNumPages()
End If
Next

If i > UBound(a) Then
' Save the merged document to DestFile
If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
MsgBox "Cannot save the resulting document" & vbLf & p & DestFile, vbExclamation, "Canceled"
End If
End If

exit_:

' Inform about error/success
If Err Then
MsgBox Err.Description, vbCritical, "Error #" & Err.Number
ElseIf i > UBound(a) Then
MsgBox "The resulting file is created:" & vbLf & p & DestFile, vbInformation, "Done"
End If

' Release the memory
If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
Set PartDocs(0) = Nothing

' Quit Acrobat application
AcroApp.Exit
Set AcroApp = Nothing

End Sub

rockybalboa
08-27-2013, 09:20 AM
Thanks for the code but i am getting this error under Sub MergePDFs query (Dim a As Variant, i As Long, n As Long, ni As Long, p As String
Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc)
.
Compile error: User-defined type not defined

ZVI
08-27-2013, 09:50 AM
Compile error: User-defined type not defined
As it was said in post #2 and has been mentioned in the comments, you need to set reference to VBE - Tools - References – Acrobat.
Press Alt-F11 and choose menu Tools - References, enable checkbox with Acrobat option (may be also Adobe Acrobat ##.# Type Library, where XX.X is version number), and press OK.

rockybalboa
08-27-2013, 10:01 AM
ohh yes, so srry. Thanks. Will try now

ZVI
08-27-2013, 10:18 AM
It's ok :)
By the way, code works with free Adobe Reader (http://get.adobe.com/uk/reader/?promoid=JZEFU) because the required library is included in it.
But if your Excel version is 32 bit then install Adobe Reader 32bit. And the same is for 64bit pair versions.

rockybalboa
08-27-2013, 08:06 PM
Hey, thanks for the code and it is working fine. But can we do this using adobe acrobat X pro because in acrobat when we do combine files, the merged pdf also shows bookmarks so that we can directly go to a particular pdf.

ZVI
08-27-2013, 09:19 PM
For referencing to Acrobat X Pro library:
1. Use VBE - Tools - References
2. Uncheck the previously set reference to Acrobat (or Adobe Acrobat ##.# Type Library)
3. Use button Browse to choose the file ACROBAT.TLB in the Acrobat X Pro installation folder (subfolder Acrobat)
4. Press OK

rockybalboa
08-29-2013, 11:14 PM
Hi,
I selected ACROBAT.TLB and ran the macro. It worked fine. It merged all the PDFs in to one but i am still not getting Bookmarks in the merged file.
I do get it when i manually merge the files in Acrobat 9 pro (Combine - Merge files in to single PDF).
Is there any way to get the bookmarks as well?

Thanks

ZVI
08-30-2013, 12:10 AM
May be functionality of Acrobat's Application Program Interface (API) is a bit different to the Graphical User Interface (GUI), which is common practice.
You can ask Adobe support about it.
I just have shown how to use Acrobat's merging API as is.

yuechu
07-10-2014, 12:43 PM
I browsed for Acrobat.tlb, but when the macro ran, it kept displaying that it cannot insert page from this line: MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"

ZVI
07-10-2014, 03:34 PM
I browsed for Acrobat.tlb, but when the macro ran, it kept displaying that it cannot insert page from this line: MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
Hi and welcome to VBAX!

You can't insert new page after the PDF document with password protection has been inserted.
Have a look on protection property of the last document inserted into the resulting file MergedFile.pdf

Regards

yuechu
07-11-2014, 05:11 AM
Thanks ZVI. I see that the file does have password protection in it even though it does not ask for any when I open it. I tried it with some PDFs I created and the macro worked perfectly.

My next steps will be to only merge similar PDF files (files beginning with same filenames) out of the bunch of PDF file in the folder and then renaming the merged file to a corresponding name on a list. Any tips of the best way to do that?




Hi and welcome to VBAX!

You can't insert new page after the PDF document with password protection has been inserted.
Have a look on protection property of the last document inserted into the resulting file MergedFile.pdf

Regards

ZVI
07-11-2014, 01:57 PM
...My next steps will be to only merge similar PDF files (files beginning with same filenames) out of the bunch of PDF file in the folder and then renaming the merged file to a corresponding name on a list. Any tips of the best way to do that?
Name of the destination file is defined in the constant DestFile of the subroutine Main(), change it as required.
Or pass file name you like into the sub MergePDFs() using the 3-rd parameter.

To merge some files in a folder by filename mask replace this f = Dir(MyPath & "*.pdf")
for example by that one f = Dir(MyPath & "MyFileMask*.pdf")

kishlaya
08-21-2014, 05:24 AM
Hi,
Set reference to VBE - Tools - References – Acrobat
and try this code:


Sub MergePDFs()
' ZVI:2013-08-27 http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X
' Reference required: "VBE - Tools - References - Acrobat"

' --> Settings, change to suit
Const MyPath = "C:\Temp" ' Path where PDF files are stored
Const MyFiles = "1.pdf,2.pdf,3.pdf" ' List of PDFs to ne merged
Const DestFile = "MergedFile.pdf" ' The name of the merged file
' <-- End of settings

Dim a As Variant, i As Long, n As Long, ni As Long, p As String
Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc

If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
a = Split(MyFiles, ",")
ReDim PartDocs(0 To UBound(a))

On Error GoTo exit_
If Len(Dir(p & DestFile)) Then Kill p & DestFile
For i = 0 To UBound(a)
' Check PDF file presence
If Dir(p & Trim(a(i))) = "" Then
MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
Exit For
End If
' Open PDF document
Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
PartDocs(i).Open p & Trim(a(i))
If i Then
' Merge PDF to PartDocs(0) document
ni = PartDocs(i).GetNumPages()
If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
End If
' Calc the number of pages in the merged document
n = n + ni
' Release the memory
PartDocs(i).Close
Set PartDocs(i) = Nothing
Else
' Calc the number of pages in PartDocs(0) document
n = PartDocs(0).GetNumPages()
End If
Next

If i > UBound(a) Then
' Save the merged document to DestFile
If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
MsgBox "Cannot save the resulting document" & vbLf & p & DestFile, vbExclamation, "Canceled"
End If
End If

exit_:

' Inform about error/success
If Err Then
MsgBox Err.Description, vbCritical, "Error #" & Err.Number
ElseIf i > UBound(a) Then
MsgBox "The resulting file is created:" & vbLf & p & DestFile, vbInformation, "Done"
End If

' Release the memory
If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
Set PartDocs(0) = Nothing

' Quit Acrobat application
AcroApp.Exit
Set AcroApp = Nothing

End Sub


Hi there!
Despite setting the correct references, I cannot get this working.
The problem lies in this line

Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
12170It says "Activex component can't create object"

Any thoughts?

/Kish

ZVI
08-21-2014, 07:15 AM
Hi there!
Despite setting the correct references, I cannot get this working.
The problem lies in this line

Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
It says "Activex component can't create object"

Any thoughts?

/Kish
Hi,

Try using early binding: Set PartDocs(i) = New Acrobat.AcroPDDoc
Also follow the recommendations of the post #10 (http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X&p=296017&viewfull=1#post296017)

Regards

kishlaya
08-21-2014, 07:26 AM
Hi,

Sorry no luck. Still the same result.:crying:

Regards,
Kish

ZVI
08-21-2014, 07:32 AM
Sorry no luck. Still the same result.
Hope that Excel version is not Starter Edition which has some major limitations.
Try manually setting the reference to Acrobat X Pro library as it was recommended in post #10 (http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X&p=296017&viewfull=1#post296017).
Try reinstalling the Acrobat X Pro.

kishlaya
08-21-2014, 07:37 AM
I am using Excel professional (Office professional). I am using the free Adobe Reader edition. I am sorry but I do not have Acrobat X Pro.

ZVI
08-21-2014, 07:40 AM
To validate the referenced objects (early binding) in VBE choose Debug - Compile VBAProject.
Debugger will highlight incorrect objects in the code if exist.

kishlaya
08-21-2014, 07:45 AM
Debugger compiles just fine. But again while executing the same error "Cannot create ActiveX object."

ZVI
08-21-2014, 07:46 AM
I am using Excel professional (Office professional). I am using the free Adobe Reader edition. I am sorry but I do not have Acrobat X Pro.
Adobe Reader Version XI (http://get.adobe.com/uk/reader/?promoid=JZEFU) should work, hope that its reinstalling will help.
I'd also temporary check the code in the new created workbook without reference to Microsoft Access Object Library shown in your attachment of post #17

kishlaya
08-21-2014, 07:48 AM
Ah! I am on a computer from work. As far as I know, I cannot install Adobe Reader XI :(. Thanks for your help!

yuechu
08-21-2014, 07:59 AM
I also learned that if you have Acrobat pro, you can highlight the files and right click to combine supported files in Acrobat and it'll merge them for you. Found out about this after working with getting the macro to work.

ZVI
08-21-2014, 08:04 AM
Not sure the code works well with previous versions of Acrobat Reader.
Such error usually indicates the Acrobat's DLL needs to be re-registered, and yea - it's the job for IT guys.
Good luck and thanks for testing the code!

Kenneth Hobs
08-21-2014, 09:53 AM
There are several PDF programs one can use to merge and splice/split PDF files. PDFSam is one. Here is a split example. http://www.vbaexpress.com/forum/showthread.php?46903-Solved-Call-CMD-EXE-and-send-instructions

PDFSam also has a GUI method to do it as well. Before I got Adobe Pro, it used one of those methods.

I also wrote a vb.net file to accept command line parameters to do it using the iTextSharp.dll routines.

There are several other 3rd party PDF programs to can accept command line parameters if you are seeking that kind of solution.

blitzd13
10-31-2014, 06:10 AM
hey I have the abobe pro XI, Im getting a Compile error at Call MergePDFS(MyPath, Myfiles, DestFile), is there a different way to call it? I updated my references and using the follwing code.

'
' adobeme Macro
'
'

Const DestFile As String = "MergedFile.pdf" ' <-- change to suit

Dim MyPath As String, MyFiles As String
Dim a() As String, i As Long, f As String

' Choose the folder or just replace that part by: MyPath = Range("E3")
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "H:\TEST PDF\"
.AllowMultiSelect = False
If .Show = False Then Exit Sub
MyPath = "H:\TEST PDF"
DoEvents
End With

' Populate the array a() by PDF file names
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
ReDim a(1 To 2 ^ 14)
f = Dir(MyPath & "*.pdf")
While Len(f)
If StrComp(f, DestFile, vbTextCompare) Then
i = i + 1
a(i) = f
End If
f = Dir()
Wend

' Merge PDFs
If i Then
ReDim Preserve a(1 To i)
MyFiles = Join(a, ",")
Application.StatusBar = "Merging, please wait ..."
Call MergePDFs(MyPath, MyFiles, DestFile)
Application.StatusBar = False
Else
MsgBox "No PDF files found in" & vbLf & MyPath, vbExclamation, "Canceled"
End If


End Sub

Kenneth Hobs
10-31-2014, 07:29 AM
Welcome to the forum!

Please start a new thread if you are not contributing to an active thread and need your own help.

When posting a new thread and parts like the MergePDFs() routine is used, feel free to add a link to a post like this one that might be relevant. For new users, just say what thread number, e.g. 47310.

When posting code, click the # icon to insert the code tags and paste between them.

You can always attach a file so we can check things like references and such. Click the "Go Advanced" button to get the paperclip icon to do that. Post a simple file that isolates the problem if possible. The more you help us, the better and faster help you will get.

For your issue, I am guessing maybe you have a conflict with your Constant variables. The MergePDFs() routine can easily be modified to include input parameters rather than Constant variables. Use F8 in the Visual Basic Editor (VBE) to step through your code one line at a time to debug. And/or, use Debug.Print to print intermediate results to the Immediate window during a run. Of course that does not help for a Compile issue. Compiling before a run is important.

Without seeing all of your code, I am guessing maybe you did not include the MergePDFs() sub as another possible cause.

Another cause can be the reference to the Adobe Acrobat object and registration of the object. Again, seeing an attachment can tell us that tell, if we have that object as well of course.

Tip: Other parts of your code could use some tweaking. I noticed that you used Preserve. That can be used in building of the file names array rather than defining a huge array. There are other neat ways to do that sort of thing too. That is fodder for another thread of course.

ZVI
10-31-2014, 12:42 PM
Im getting a Compile error at Call MergePDFS(MyPath, Myfiles, DestFile)
Hi and welcome to VBAX!

Seems the code of Sub MergePDFs has not been copied from post #4 (http://www.vbaexpress.com/forum/showthread.php?47310-Need-code-to-merge-PDF-files-in-a-folder-using-adobe-acrobat-X&p=295941&viewfull=1#post295941). It is required.
Your attached example would avoid our guessing - please pay attention on Kenneth's recommendations.

Regards

nreynolds
12-15-2014, 09:56 AM
The original coding supplied at the beginning of this thread worked perfectly for me. However I'd like to link this code with an Excel spreadsheet I'm developing. Basically, I have a checklist in Excel that allows the user to check which pdfs they want to have merged. Their selections either return a True/False or I can have it return a 0/1. These values are stored in a column in excel. I've made the checklist in the same order as the pdf files. I'm fairly new to VBA so any help is appreciated. Let me know if you need any additional information.

Jlearman
07-16-2015, 06:14 AM
Hey ZVI,

This code would be tremendously helpful for me, but I'm getting the "Run-time error 429: ActiveX component can't create object" Set PartDocs(i) = CreateObject("AcroExch.PDDoc") at as well. I also tried the early binding method.

I manually set the reference to the Acrobat 10.0 Type Library, and can confirm that I have Acrobat Reader XI installed. Any other ideas why this wouldn't work?

13933

Many thanks,
Joe

Kenneth Hobs
07-16-2015, 07:20 AM
Welcome to the forum! You should start your own thread and reference ones like this one if needed.

Please read the posts in this thread. You have referenced the reader, not Adobe "Acrobat". As such, the class AcroExch.PDDoc is not in your registry and can not be referenced. The reference should just be shown as "Acrobat" as previously explained. The acrobat DLL and TLB files will be in your "c:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\" folder.

Since you only have the reader, you will have to use a third party program to merge PDF files as explained. Of course one can include more than one worksheet to create a PDF using Excel's method.

emreimer
03-29-2016, 06:01 AM
The code works after adding the reference (in my case the acrobat.tdl), however after saving and closing the reference disappears. With the correct references the process works start to finish. When I reopen Excel and the reference has been replace to Acrobat 10.0 Type Library (and the .tdl is no longer checked) this happens:

At this line: Set PartDocs(i) = CreateObject("AcroExch.PDDoc"), a dialog box opens that says:

15769


When I cancel and continue, the process works, but the idea is to have this run automatically. Neither suppressing ScreenUpdating nor EnableEvents works.

I need to figure out how to persist the correct reference even after saving/closing or suppress this messaging.

Thanks.

Kenneth Hobs
03-29-2016, 07:00 AM
Welcome to the forum! You should start your own thread and reference ones like this one if needed.

You should update your print driver to avoid that problem dialog. http://lfpp.csa.canon.com/main/product_group.jsp?FOLDER%3C%3Efolder_id=2534374302162637

Do you mean acrobat.tlb? I reference Acrobat.dll. Once added, your reference will then simple show as "Acrobat".

Shiva117
08-18-2016, 11:02 AM
Hi i am getting the error of user defined type not defined. I am using Excel 2013 and the libraries available for me are
1) Adobe Acrobat browser control type library 1.0
2) Adobe reader file preview
3)Acrobat access 3.0


16904
Version is 15.010
please help its very urgent

Kenneth Hobs
08-18-2016, 12:10 PM
See post 34.

You must have Acrobat installed and not just Adobe Reader.

ZVI
08-18-2016, 06:30 PM
The code works with Adobe Reader XI Pro installed

Shiva117
08-19-2016, 02:03 AM
Hi,

Can we do it using Acrobat reader. if yes please share the code. even if I need to use third part controls please help me on this

Aussiebear
08-19-2016, 03:37 PM
Hi,

Can we do it using Acrobat reader. if yes please share the code. even if I need to use third part controls please help me on this

Shiva117, did you not READ post #38 by Kenneth. You must have Acrobat installed and not just Adobe Reader for this code to work.

swarmo
12-09-2016, 04:15 PM
Hi guys,

Can anyone please help me? I need to have this macro reference a cell which determines the location of the PDF's. The locations and the names of the pdf's change for every job, based off the business name.

For example, cell A1 says C:\ACME Motors\Form\ACME Motors - Form.pdf

And A2 says C:\ACME Motors\Office Documents\ACME Motors - Office Form.pdf

And I need the merged PDF to be saved at A3 which says C:\ACME Motors\Final Documents\ACME Motors - Signed.pdf


Sub Main()
Const DestFile As String = "MergedFile.pdf" ' <-- change to suit

Dim MyPath As String, MyFiles As String
Dim a() As String, i As Long, f As String

' Choose the folder or just replace that part by: MyPath = Range("E3")
With Application.FileDialog(msoFileDialogFolderPicker)
'.InitialFileName = "C:\Temp\"
.AllowMultiSelect = False
If .Show = False Then Exit Sub
MyPath = .SelectedItems(1)
DoEvents
End With

ZVI
12-10-2016, 01:52 AM
Hi guys,
Can anyone please help me? I need to have this macro reference a cell which determines the location of the PDF's. The locations and the names of the pdf's change for every job, based off the business name.
For example, cell A1 says C:\ACME Motors\Form\ACME Motors - Form.pdf
And A2 says C:\ACME Motors\Office Documents\ACME Motors - Office Form.pdf
And I need the merged PDF to be saved at A3 which says C:\ACME Motors\Final Documents\ACME Motors - Signed.pdf

Hi and welcome to VBAExpress!
Try this version of the code:


Sub Main()
Dim MyFiles As String, DestFile As String
With ActiveSheet
MyFiles = .Range("A1").Value & "," & .Range("A2").Value
DestFile = .Range("A3").Value
End With
Call MergePDFs01(MyFiles, DestFile)
End Sub

Sub MergePDFs01(MyFiles As String, DestFile As String)
' ZVI:2016-12-10 http://www.vbaexpress.com/forum/showthread.php?47310&p=353568&viewfull=1#post353568
' Reference required: VBE - Tools - References - Acrobat

Dim a As Variant, i As Long, n As Long, ni As Long
Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.AcroPDDoc

a = Split(MyFiles, ",")
ReDim PartDocs(0 To UBound(a))

On Error GoTo exit_
If Len(Dir(DestFile)) Then Kill DestFile
For i = 0 To UBound(a)
' Check PDF file presence
If Dir(Trim(a(i))) = "" Then
MsgBox "File not found" & vbLf & a(i), vbExclamation, "Canceled"
Exit For
End If
' Open PDF document
Set PartDocs(i) = New Acrobat.AcroPDDoc ' CreateObject("AcroExch.PDDoc")
PartDocs(i).Open Trim(a(i))
If i Then
' Merge PDF to PartDocs(0) document
ni = PartDocs(i).GetNumPages()
If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
MsgBox "Cannot insert pages of" & vbLf & a(i), vbExclamation, "Canceled"
End If
' Calc the amount of pages in the merged document
n = n + ni
' Release the memory
PartDocs(i).Close
Set PartDocs(i) = Nothing
Else
' Calc the amount of pages in PartDocs(0) document
n = PartDocs(0).GetNumPages()
End If
Next

If i > UBound(a) Then
' Save the merged document to DestFile
If Not PartDocs(0).Save(PDSaveFull, DestFile) Then
MsgBox "Cannot save the resulting document" & vbLf & DestFile, vbExclamation, "Canceled"
End If
End If

exit_:

' Inform about error/success
If Err Then
MsgBox Err.Description, vbCritical, "Error #" & Err.Number
ElseIf i > UBound(a) Then
MsgBox "The resulting file is created:" & vbLf & DestFile, vbInformation, "Done"
End If

' Release the memory
If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
Set PartDocs(0) = Nothing

' Quit Acrobat application
AcroApp.Exit
'DoEvents: DoEvents
Set AcroApp = Nothing

End Sub
Best Regards!

johnybravok
03-30-2017, 04:43 AM
Hi ZVI, all,

the first code worked well posted by ZVI (on page 1), however, I am having issues in adjusting the Destfile to include a variable date "lg_cob" as per below. Any way to make it work as currently the code just ingores it and saves it as constant.

thanks

Const Destfile = "MergedFile" & lg_cob & ".pdf" ' The name of the merged file



Sub MergePDFs()

' --> Settings, change to suit
Const MyPath = "C:\Temp" ' Path where PDF files are stored
Const MyFiles = "1.pdf,2.pdf,3.pdf" ' List of PDFs to ne merged
Const DestFile = "MergedFile.pdf" ' The name of the merged file
' <-- End of settings

Dim a As Variant, i As Long, n As Long, ni As Long, p As String
Dim AcroApp As New Acrobat.AcroApp, PartDocs() As Acrobat.CAcroPDDoc

If Right(MyPath, 1) = "\" Then p = MyPath Else p = MyPath & "\"
a = Split(MyFiles, ",")
ReDim PartDocs(0 To UBound(a))

On Error GoTo exit_
If Len(Dir(p & DestFile)) Then Kill p & DestFile
For i = 0 To UBound(a)
' Check PDF file presence
If Dir(p & Trim(a(i))) = "" Then
MsgBox "File not found" & vbLf & p & a(i), vbExclamation, "Canceled"
Exit For
End If
' Open PDF document
Set PartDocs(i) = CreateObject("AcroExch.PDDoc")
PartDocs(i).Open p & Trim(a(i))
If i Then
' Merge PDF to PartDocs(0) document
ni = PartDocs(i).GetNumPages()
If Not PartDocs(0).InsertPages(n - 1, PartDocs(i), 0, ni, True) Then
MsgBox "Cannot insert pages of" & vbLf & p & a(i), vbExclamation, "Canceled"
End If
' Calc the number of pages in the merged document
n = n + ni
' Release the memory
PartDocs(i).Close
Set PartDocs(i) = Nothing
Else
' Calc the number of pages in PartDocs(0) document
n = PartDocs(0).GetNumPages()
End If
Next

If i > UBound(a) Then
' Save the merged document to DestFile
If Not PartDocs(0).Save(PDSaveFull, p & DestFile) Then
MsgBox "Cannot save the resulting document" & vbLf & p & DestFile, vbExclamation, "Canceled"
End If
End If

exit_:

' Inform about error/success
If Err Then
MsgBox Err.Description, vbCritical, "Error #" & Err.Number
ElseIf i > UBound(a) Then
MsgBox "The resulting file is created:" & vbLf & p & DestFile, vbInformation, "Done"
End If

' Release the memory
If Not PartDocs(0) Is Nothing Then PartDocs(0).Close
Set PartDocs(0) = Nothing

' Quit Acrobat application
AcroApp.Exit
Set AcroApp = Nothing

End Sub

ZVI
03-30-2017, 05:54 AM
Hi Johny,
Try this adjusted part of the code:


If i > UBound(a) Then

Dim lg_cob As String, f As String
lg_cob = Format(Now, "_yyyy-mm-dd")
f = Replace(DestFile, ".pdf", lg_cob & ".pdf")

' Save the merged document to DestFile
If Not PartDocs(0).Save(PDSaveFull, p & f) Then
MsgBox "Cannot save the resulting document" & vbLf & p & f, vbExclamation, "Canceled"
End If

End If

exit_:

johnybravok
03-31-2017, 03:08 AM
Hi ZVI,

spacibo, but it, sadly, did not work... i have a pretty hefty code i've inherited and there are a lot of reference.

As a quick fix, maybe you can suggest a code where I can open the saved PDF and save it with an updated name?

Something like, open "MergedFile.pdf", save as "MergedFile2.pdf". Rest I should figure out myself.

thanks again!

ANKY4304
08-29-2017, 04:15 AM
Hi,

Can you please help me as well, I need to save all the files in a folder(pdf files)(whatever be the name) by converting them to a single file using Adobe to another folder. some of the files in the folder(which are to be converted to a single) are password protected also. please HELP, Thanks!!

Kenneth Hobs
08-29-2017, 10:33 AM
Welcome to the forum! Please start your own thread. http://www.vbaexpress.com/forum/newthread.php?do=newthread&f=17

You can reference this thread if needed. If links can not be posted yet, list thread 47310 as the reference.

I guess you want all the files merged into one file? I guess all the files have the same password? I guess that you mean Adobe Acrobat and NOT Adobe Reader?

ANKY4304
08-30-2017, 12:21 AM
Thanks!! I have posted a new thread. And only 3 files are password protected.. I need total 5 files to be converted into one and saved to another location