Combine 2 sheets into 1 PDF file

[seansn]

Hi everyone here on VBA express.

I am currently encountering some difficulties in coming up with a vba code to combine 2 excel sheets within a workbook into 1 single PDF print job.

Ideally, I want the PDF 'Save As' dialog to appear so that i can name the file before saving and opening the PDF.

However, so far my code results in 2 separate PDF print jobs. Merging them together is a hassle.

Here is my current code:

Worksheets(Array("PrintCustomer", "Items")).PrintOut _
Preview:=False, _
ActivePrinter:="PDF-XChange Standard V6"

Please help me!

[Kenneth Hobs]

Welcome to the forum!

If you have a PDF printer line ADOBE PDF, then PrintOut method will work. The Publish method will work as well. You could use a dialog to get the drive:/path\filename.ext and send that to the Publish. Using an array of sheetnames prints by sheet index and not array index order for what its worth.

Here is a publish example with a dialog.

Sub Test_PublishToPDF()
Dim s As String, ss As String
s = Range("F5").Value2 & Range("F4").Value2 & ".pdf"
'ss= PublishToPDF(s, ActiveSheet) 'Use set print range

Dim r As Range
Set r = Columns("A:A").Find("TOTAL LIABILITIES & EQUITY")
If r Is Nothing Then Exit Sub
ss = PublishToPDF(s, Range("A1:B" & r.Row)) 'Use a dynamic range
'ss = PublishToPDF(s, Range("A1:B" & r.Row), True) 'Use a dynamic range, prompt for filename
Shell "cmd /c " & ss, vbNormalFocus
End Sub


Function PublishToPDF(fName As String, o As Object, _
Optional tfGetFilename As Boolean = False) As String
Dim rc As Variant
rc = fName
If tfGetFilename Then
rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
If rc = "" Then Exit Function
End If

o.ExportAsFixedFormat Type:=xlTypePDF, fileName:=rc _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

PublishToPDF = rc
End Function

[seansn]

Welcome to the forum!

If you have a PDF printer line ADOBE PDF, then PrintOut method will work. The Publish method will work as well. You could use a dialog to get the drive:/path\filename.ext and send that to the Publish. Using an array of sheetnames prints by sheet index and not array index order for what its worth.

Here is a publish example with a dialog.

Sub Test_PublishToPDF()
Dim s As String, ss As String
s = Range("F5").Value2 & Range("F4").Value2 & ".pdf"
'ss= PublishToPDF(s, ActiveSheet) 'Use set print range

Dim r As Range
Set r = Columns("A:A").Find("TOTAL LIABILITIES & EQUITY")
If r Is Nothing Then Exit Sub
ss = PublishToPDF(s, Range("A1:B" & r.Row)) 'Use a dynamic range
'ss = PublishToPDF(s, Range("A1:B" & r.Row), True) 'Use a dynamic range, prompt for filename
Shell "cmd /c " & ss, vbNormalFocus
End Sub


Function PublishToPDF(fName As String, o As Object, _
Optional tfGetFilename As Boolean = False) As String
Dim rc As Variant
rc = fName
If tfGetFilename Then
rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
If rc = "" Then Exit Function
End If

o.ExportAsFixedFormat Type:=xlTypePDF, fileName:=rc _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

PublishToPDF = rc
End Function




Hi Kenneth,

What do you mean by a PDF Printer Line?

[Kenneth Hobs]

The line was your line of code in post #1 used like:
Worksheets(Array("PrintCustomer", "Items")).PrintOut _
Preview:=False, _
ActivePrinter:="ADOBE PDF"

To use my function in post #2:

Sub Main()
Dim s$
Worksheets(Array("PrintCustomer", "Items")).Select
s = PublishToPDF( _
ThisWorkbook.Path, _
ActiveSheet, _
True)
'Open file
Shell "cmd /c " & """" & s & """", vbNormalFocus
Worksheets(1).Select
End Sub