Merging pdf-files by macro dont work

[blaksnm]

Hi
There are some PDFs in folder (folder: "C:\!ConsEasy")
I want to pick the ones to be merged

So far I have got the macro below, but when running this it only opens a folder and no PDF-files is visible to pick
Will anyone be so kind to guide me to sucsess here?
Private Sub Merge_PDFs(PDFfolder As String)

Const Q As String = """"

Dim Wsh As Object 'WshShell
Dim inputPDFs As String, outputPDF As String
Dim PDFfile As String
Dim command As String
Dim page As Long

If Right(PDFfolder, 1) <> "\" Then PDFfolder = PDFfolder & "\"
outputPDF = PDFfolder & "All_PDFs_Merged.pdf"
If Dir(outputPDF) <> vbNullString Then Kill outputPDF

page = 1
inputPDFs = ""
Do
PDFfile = Dir(PDFfolder & "Page" & page & ".pdf")
If PDFfile <> vbNullString Then inputPDFs = inputPDFs & Q & PDFfile & Q & " "
page = page + 1
Loop While PDFfile <> vbNullString

'Merge PDFs with cat

command = "CD /D " & Q & PDFfolder & Q & " & PDFtk " & inputPDFs & " cat output " & Q & outputPDF & Q
Set Wsh = CreateObject("WScript.Shell") 'New WshShell
Wsh.Run "cmd /c " & command, 0, True

End Sub

 

[John_w]

Just a few pointers to help you.

There are some PDFs in folder (folder: "C:\!ConsEasy")
I want to pick the ones to be merged

So far I have got the macro below, but when running this it only opens a folder and no PDF-files is visible to pick

Call Application.FileDialog(msoFileDialogFilePicker) with AllowMultiSelect = True to browse to a folder and select multiple files.

The Private Sub Merge_PDFs(PDFfolder As String) routine has an argument so it needs to be called from a 'main' routine, but I'd delete the argument and have everything in one routine.

PDFfile = Dir(PDFfolder & "Page" & page & ".pdf")

That line is part of a Dir function loop which is looking for files matching "PageX.pdf", where X is 1, 2, 3 etc. You wouldn't use a Dir function loop if you've selected the files with Application.FileDialog(msoFileDialogFilePicker).

command = "CD /D " & Q & PDFfolder & Q & " & PDFtk " & inputPDFs & " cat output " & Q & outputPDF & Q

Note that the code uses PDFtk Server cat command to merge PDFs, so PDFtk Server must be installed.

See how you go with modifying the code and reply if you need more help.

 

[blaksnm]

Just a few pointers to help you.


Call Application.FileDialog(msoFileDialogFilePicker) with AllowMultiSelect = True to browse to a folder and select multiple files.

The Private Sub Merge_PDFs(PDFfolder As String) routine has an argument so it needs to be called from a 'main' routine, but I'd delete the argument and have everything in one routine.


That line is part of a Dir function loop which is looking for files matching "PageX.pdf", where X is 1, 2, 3 etc. You wouldn't use a Dir function loop if you've selected the files with Application.FileDialog(msoFileDialogFilePicker).



Note that the code uses PDFtk Server cat command to merge PDFs, so PDFtk Server must be installed.

See how you go with modifying the code and reply if you need more help.

Thanks for takingso kind interest in this
However I am a novise in VBA and your guiding is on a much higher level than I understand

 

[blaksnm]

I have two different tasks - the one above that merge picked PDS
the other one is like this:
In my workbook there are 5 sheets/reports to be printed as PDF - (no problem)
To each of theese 5 sheets there are 1 specified view accordingly (no problem)
I have made a macro that print/save the pages as 5 singel PDF-files and hope for a simple way to merge all 5 files into one report.

 

[John_w]

Your OP didn't say the PDFs were created by saving the 5 sheets in your workbook as a 5 PDFs.

With that, there's no need to save and merge as separate steps, because Excel can save the 5 sheets as a single PDF. Simply record a macro with you selecting all 5 sheets and saving the active sheets as a PDF, or saving the entire workbook as a PDF, if there are exactly 5 sheets in the workbook.

 

[blaksnm]

Your OP didn't say the PDFs were created by saving the 5 sheets in your workbook as a 5 PDFs.

With that, there's no need to save and merge as separate steps, because Excel can save the 5 sheets as a single PDF. Simply record a macro with you selecting all 5 sheets and saving the active sheets as a PDF, or saving the entire workbook as a PDF, if there are exactly 5 sheets in the workbook.

Thanks a lot
Ill try iot out