VBA - Command Button to print current worksheet + attached PDF

[dmanso]

Hi there,

I have been searching for a solution to this for quite some time. I have a workbook with multiple worksheets. On one of the worksheets, I have attached PDFs as objects. Within the same worksheet, I am hoping to create a command button which will print when clicked on, the current worksheet and the attached PDFs. My intention is to use Microsoft print to PDF function, and "merge" the worksheet together with the PDFs.

My apologies for my vague explanation. I am also extremely new to VBA, and will really appreciate any assistance.

Thank you again

 

[John_w]

See Need to Print Embedded PDFs, but note that the code needs Acrobat Pro to merge the two PDFs.

 

[dmanso]

Very much appreciated! I will definitely have a good read. I think I may have come across this earlier on, but due to Adobe Pro, which I do not have, it was not feasible. I wonder if I can get it to work using a opensource PDF merger. Thank you again

 

[John_w]

Alternatively, there are some command line tools which can merge PDFs from VBA, for example PDFtk Server and PDFill. Example code here:

Append to pdf VBA

Morning all. I am hoping to find advice for a Shell( ) or other method to add sheets to a pdf file. I have tried several pdf generators / merging programs that support command line, but cannot get any of them to work. I have a standard sheet which is filled by VBA and saved to PDF then...

www.mrexcel.com

 

[dmanso]

Hi there John_w, thank you again for the help. I have played around with the codes, and unfortunately as I do not have Adobe Pro, I cannot get it to work.

I am wondering if it is possible to use a button (form or a shape) to print the active worksheet and the embedded PDFs, and merging them using PDFSam (opensource)? I cannot for the life of me figure out how to update the Macro from Adobe to PDFSam... Any help will be much appreciated!

 

[John_w]

Does PDFsam have a command line interface? I couldn't find any documentation or examples.

 

[dmanso]

Thanks John_w. I managed to find something another MrExcel member had posted (Kenneth Hobson) and he wrote a macro as below (although this is for splitting rather than merging?) I cannot for the life of me understand it or convert it to merge. My PDFSam version is 4.3.1:

Macro from Kenneth

'How to split a PDF into pages using VBA

Sub Test2()
Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
Dim pdfOut As String, q As String
q = """"
pdfsam = "cmd /k java -jar " & q & _
"C:\MyFiles\pdfsam\2.0.0\lib\" & _
"pdfsam-console-2.0.2e.jar" & q
pdfFiles = "-f " & q & "C:\MyFiles\pdfsam\2.0.0\doc\pdfsam-1.0.0-tutorial.pdf" & q
pdfOut = "-o " & q & Environ("temp") & "\" & q
pdfsamStr = pdfsam & " -p SamDoc -s BURST " & pdfFiles & " " & pdfOut & " SPLIT"
Debug.Print pdfsamStr
Shell pdfsamStr, vbNormalFocus
End Sub

Sub Test1()
Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
Dim pdfOut As String, q As String
q = """"
pdfsam = "cmd /k java -jar " & q & _
"C:\MyFiles\pdfsam\1.0.3\lib\" & _
"pdfsam-console-1.1.5e.jar" & q
pdfFiles = "-f " & q & "C:\MyFiles\pdfsam\1.0.3\doc\pdfsam-1.0.0-tutorial.pdf" & q
pdfOut = "-o " & q & "c:\temp" & "\" & q
pdfsamStr = pdfsam & " " & pdfFiles & " -p ken.pdf -s BURST " & pdfOut & " split"
Debug.Print pdfsamStr
Shell pdfsamStr, vbNormalFocus
End Sub

Sub TestBat()
Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
Dim pdfOut As String, q As String
q = ""
pdfsam = "cmd /k java -jar " & q & _
"C:\MyFiles\pdfsam\1.0.3\bin\" & _
"run-console.bat" & q
pdfFiles = "-f " & q & "C:\MyFiles\pdfsam\1.0.3\doc\pdfsam-1.0.0-tutorial.pdf" & q
pdfOut = "-o " & q & "c:\temp" & "\" & q
pdfsamStr = pdfsam & " " & pdfFiles & " " & pdfOut & " -p ken.pdf -s BURST split"
Debug.Print pdfsamStr
Shell pdfsamStr, vbNormalFocus
End Sub

 

[John_w]

Do those macros work?

 

[dmanso]

I must admit I do not know for sure as I have not tested them.

 

[dmanso]

Wondering if anyone can help? If it is not possible without Adobe Pro. Is it possible to simply print the active sheet, and the embedded PDF, using the "Microsoft print to PDF" as separate files (into the same folder)? Sorry, I am quite useless with VBA and will need all the help I can get. Much appreciated.