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