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

NiharDave

New Member
Joined
Feb 21, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

1677898257618.png
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi NihaDave

Maybe with this code ;)

VBA 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
 
Upvote 0
Hi NihaDave

Maybe with this code ;)

VBA 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?
 
Upvote 0
Hi

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

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top