VBA to send print output from a loop to single PDF file

[NiharDave]

i have 2 sheets, Sheet 1 has the actual data and Sheet 2 has predefined label where i fetch values from sheet 1 and print using a loop. Currently it works fine as i send output directly to printer but i want to send output to a single PDF file. Below is the snapshot of how the label looks and also code i am using to print.

Sub PrintLabels()

Dim ws1 As Worksheet 'declare worksheet variables
Dim ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Sheet1") 'set worksheet variables to the relevant sheets
Set ws2 = ThisWorkbook.Sheets("Sheet2")

Dim row As Long 'declare row variables

row = 2 'start at row 2 on Sheet 1

Do While ws1.Cells(row, 1).Value <> "" 'loop until an empty cell is encountered

ws2.Cells(4, "D").Value = ws1.Cells(row, "A").Value
ws2.Cells(7, "D").Value = ws1.Cells(row, "D").Value
ws2.Cells(11, "D").Value = ws1.Cells(row, "H").Value
ws2.Cells(14, "D").Value = ws1.Cells(row, "I").Value
ws2.Cells(21, "D").Value = ws1.Cells(row, "L").Value

ActiveSheet.PrintOut 'Print active sheet

row = row + 1 'increment row on Sheet 1
Loop

End Sub

 

[BrianM45]

Hi NihaDave

Maybe with this code

Sub PrintLabels()
  ' declare variables
  Dim WsLabel As Worksheet
  Dim sPath As String, sFName As String
  Dim dRow As Long, lRow As Long, Ind As Long
  ' Table of files
  Dim TabFiles() As String
  ' For application
  Dim PDFsamPath As String
  Dim pdfName As String
  
  ' Define Path of PDf file
  sPath = "C:\Temp\"
  ' Define worksheet
  Set WsLabel = ThisWorkbook.Sheets("Sheet2")
  ' Define Pdf Merged files
  pdfName = "LabelMerged.pdf"
  ' Initalise variable
  Ind = 0
  ' With the database
  With ThisWorkbook.Sheets("Sheet1")
    dRow = .Range("A" & Rows.Count).End(xlUp).row
    For lRow = 2 To dRow
      WsLabel.Range("D4").Value = .Range("A" & lRow).Value
      WsLabel.Range("D7").Value = .Range("D" & lRow).Value
      WsLabel.Range("D11").Value = .Range("H" & lRow).Value
      WsLabel.Range("D14").Value = .Range("I" & lRow).Value
      WsLabel.Range("D21").Value = .Range("L" & lRow).Value
      ' Build file name
      sFName = "Label_" & Format(lRow - 1, "000") & ".pdf"
      ' Print to PDF
      WsLabel.ExportAsFixedFormat Type:=xlTypePDF, _
          Filename:=sPath & sFName, Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
      ' Remember file name
      Ind = Ind + 1
      ReDim Preserve TabFiles(Ind)
      TabFiles(Ind) = sFName
    Next lRow
  End With
  ' Set path to PDFsam program
  PDFsamPath = "C:\Program Files\PDFsam\pdfsam-console.exe"
  ' Merge created PDF files into a single file using PDFsam
  Shell PDFsamPath & " -f " & Join(TabFiles, ",") & " -o " & pdfName, vbHide
  ' Delete individual PDF files created
  For Ind = 1 To UBound(TabFiles)
      Kill TabFiles(Ind)
  Next Ind
End Sub

 

[NiharDave]

Hi NihaDave

Maybe with this code

Sub PrintLabels()
  ' declare variables
  Dim WsLabel As Worksheet
  Dim sPath As String, sFName As String
  Dim dRow As Long, lRow As Long, Ind As Long
  ' Table of files
  Dim TabFiles() As String
  ' For application
  Dim PDFsamPath As String
  Dim pdfName As String
 
  ' Define Path of PDf file
  sPath = "C:\Temp\"
  ' Define worksheet
  Set WsLabel = ThisWorkbook.Sheets("Sheet2")
  ' Define Pdf Merged files
  pdfName = "LabelMerged.pdf"
  ' Initalise variable
  Ind = 0
  ' With the database
  With ThisWorkbook.Sheets("Sheet1")
    dRow = .Range("A" & Rows.Count).End(xlUp).row
    For lRow = 2 To dRow
      WsLabel.Range("D4").Value = .Range("A" & lRow).Value
      WsLabel.Range("D7").Value = .Range("D" & lRow).Value
      WsLabel.Range("D11").Value = .Range("H" & lRow).Value
      WsLabel.Range("D14").Value = .Range("I" & lRow).Value
      WsLabel.Range("D21").Value = .Range("L" & lRow).Value
      ' Build file name
      sFName = "Label_" & Format(lRow - 1, "000") & ".pdf"
      ' Print to PDF
      WsLabel.ExportAsFixedFormat Type:=xlTypePDF, _
          Filename:=sPath & sFName, Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
      ' Remember file name
      Ind = Ind + 1
      ReDim Preserve TabFiles(Ind)
      TabFiles(Ind) = sFName
    Next lRow
  End With
  ' Set path to PDFsam program
  PDFsamPath = "C:\Program Files\PDFsam\pdfsam-console.exe"
  ' Merge created PDF files into a single file using PDFsam
  Shell PDFsamPath & " -f " & Join(TabFiles, ",") & " -o " & pdfName, vbHide
  ' Delete individual PDF files created
  For Ind = 1 To UBound(TabFiles)
      Kill TabFiles(Ind)
  Next Ind
End Sub

Hi Brian,
thank you for the code it works till it creates pdf files but for me my organization will not allow to install PDFsam or any other program is there a way to do it without external programs?

 

[BrianM45]

Hi

Sorry, but not to my knowledge, if you have Acrobat DC, you can merge all your files afterwards

 

[NiharDave]

Hi

Sorry, but not to my knowledge, if you have Acrobat DC, you can merge all your files afterwards

Thank you so much for everything.