Printing PDF file as per the counts specified in Excel sheet

[nitkot]

We are non-profit organization, which is sending the question papers of religious exam to various schools based on their enrollment of levels.

We need one code, which can print/export to combined PDF based on the parameters given in the excel sheet.

Please check the attached excel sheet for parameters. At break of every school change, system can print one banner page with school name.

 

[John_w]

This macro uses the default application for printing PDFs and the data is read from an Excel table (convert your data range to a table if necessary) on the active sheet. Also, your 'Name of PDF File' column specifies only the partial folder path, so you must edit the code to change the baseFolder string to give the full file path to the "MyFiles" folder before running the macro.

Public Sub Print_PDFs()

    Dim table As ListObject
    Dim i As Long, n As Long
    Dim school As String
    Dim baseFolder As String
    
    baseFolder = "C:\path\to\"  'CHANGE THIS
    
    Set table = ActiveSheet.ListObjects(1)
    
    With table
        school = ""
        For i = 1 To .DataBodyRange.Rows.Count
            If .DataBodyRange(i, 1).Value <> school Then
                school = .DataBodyRange(i, 1).Value
                MsgBox "Print banner page for " & school
                'ShellExecute_Print "C:\path\to\" & school & " Banner Page.pdf"
            End If
            
            For n = 1 To .DataBodyRange(i, 3).Value
                ShellExecute_Print baseFolder & .DataBodyRange(i, 2).Value
            Next
        Next
    End With
    
End Sub


Public Sub ShellExecute_Print(file As String, Optional printerName As String)
    If printerName = "" Then
        ShellExecute Application.hwnd, "PrintTo", file, vbNullString, 0&, SW_HIDE
    Else
        ShellExecute Application.hwnd, "PrintTo", file, Chr(34) & printerName & Chr(34), 0&, SW_HIDE
    End If
End Sub

At break of every school change, system can print one banner page with school name.

I'm not sure what you mean; what is the banner page? Is it a PDF specific to the school? Is it printed automatically by the printer? Or should the macro print it? There is a line in the code which prints "<school> Banner Page.pdf", however this is commented out because I don't know what you want to do and instead the macro simply displays a message when the school changes.

 

[nitkot]

Thanks a lot for your response. Highly appreciate your effort for the same.

Banner page will print the Just a school name in big font in the middle of paper, It will act as a separator between two school, so we can clip the set and send in cover to respective school. I would appreciate if we can have macro for the same.

Meanwhile, I will review and try to code sent by you.

Thanks again.

 

[nitkot]

This macro uses the default application for printing PDFs and the data is read from an Excel table (convert your data range to a table if necessary) on the active sheet. Also, your 'Name of PDF File' column specifies only the partial folder path, so you must edit the code to change the baseFolder string to give the full file path to the "MyFiles" folder before running the macro.

Public Sub Print_PDFs()

    Dim table As ListObject
    Dim i As Long, n As Long
    Dim school As String
    Dim baseFolder As String
   
    baseFolder = "C:\path\to\"  'CHANGE THIS
   
    Set table = ActiveSheet.ListObjects(1)
   
    With table
        school = ""
        For i = 1 To .DataBodyRange.Rows.Count
            If .DataBodyRange(i, 1).Value <> school Then
                school = .DataBodyRange(i, 1).Value
                MsgBox "Print banner page for " & school
                'ShellExecute_Print "C:\path\to\" & school & " Banner Page.pdf"
            End If
           
            For n = 1 To .DataBodyRange(i, 3).Value
                ShellExecute_Print baseFolder & .DataBodyRange(i, 2).Value
            Next
        Next
    End With
   
End Sub


Public Sub ShellExecute_Print(file As String, Optional printerName As String)
    If printerName = "" Then
        ShellExecute Application.hwnd, "PrintTo", file, vbNullString, 0&, SW_HIDE
    Else
        ShellExecute Application.hwnd, "PrintTo", file, Chr(34) & printerName & Chr(34), 0&, SW_HIDE
    End If
End Sub

I'm not sure what you mean; what is the banner page? Is it a PDF specific to the school? Is it printed automatically by the printer? Or should the macro print it? There is a line in the code which prints "<school> Banner Page.pdf", however this is commented out because I don't know what you want to do and instead the macro simply displays a message when the school changes.

Hi,

I have modified the macro as suggested with Path name, When I try to run its giving error as per screenshot attached. Also attached the layout of the sheet.

Can you help? I can send the excel sheet if needed.

Thanks
Nitin

 

[John_w]

Click Debug on the error message; which line is highlighted? Is the data in a table?

 

[nitkot]

Hi John,

While I was waiting for response, I have converted my range into table and macro ran further, but it stopped at one line, I am sending you screenshots herewith.

Screenshot_8 is having complete macro as of now.

Kindly check, if you can help.

Thanks
Nitin

 

[John_w]

Now that the PDF column contains the full path and PDF file name, you don't need baseFolder. Change the print line to:

ShellExecute_Print .DataBodyRange(i, 2).Value

 

[John_w]

Sorry, the 'Sub or Function not defined' error occurs because I omitted the ShellExecute API function. Here's the complete code with all the corrections and it also prints a banner page before each school.

Option Explicit

#If VBA7 Then
  Private Declare PtrSafe Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
  Private Declare Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

Private Const SW_HIDE As Long = 0&


Public Sub Print_PDFs2)

    Dim table As ListObject
    Dim i As Long, n As Long
    Dim school As String
    Dim bannerSheet As Worksheet
   
    Set table = ActiveSheet.ListObjects(1)
   
    Set bannerSheet = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
   
    With table
        school = ""
        For i = 1 To .DataBodyRange.Rows.Count
            If .DataBodyRange(i, 1).Value <> school Then
                school = .DataBodyRange(i, 1).Value
                With bannerSheet.Range("C25")
                    .Clear
                    .Value = school
                    .Font.Name = "Calibri"
                    .Font.Size = 72
                    .Font.Bold = True
                End With
                bannerSheet.PrintOut
            End If
            For n = 1 To .DataBodyRange(i, 3).Value
                ShellExecute_Print .DataBodyRange(i, 2).Value
            Next
        Next
    End With
   
    Application.DisplayAlerts = False
    bannerSheet.Delete
    Application.DisplayAlerts = True
   
End Sub


Public Sub ShellExecute_Print(file As String, Optional printerName As String)
    If printerName = "" Then
        ShellExecute Application.hwnd, "PrintTo", file, vbNullString, 0&, SW_HIDE
    Else
        ShellExecute Application.hwnd, "PrintTo", file, Chr(34) & printerName & Chr(34), 0&, SW_HIDE
    End If
End Sub

Note that it is quite difficult in VBA to print files in a specific order because the printer queue may reorder the print jobs, according to their file size - usually in ascending order of file size. One solution which may work is to change the Windows printer properties to print directly to the printer, instead of spooling via the print queue, and the files should print in the same order as VBA 'prints' them.

 

[nitkot]

Hi John,
Thanks for your response and revised code. There was minor correction which I have to do as below. Hope this is correct syntax.

Public Sub Print_PDFs2) --> Public Sub Print_PDF()

While running, Unfortunately, code is sending three PDF(s) of the school name heading only, it does not extend the header PDF file with the mentioned files that many times as defined in the excel sheet.

Also, when you do that, I need output as single PDF file only with Total number of pages = No. of copies for each file + No. of header page printed. In our sample case, it will be 59 pages + 3 school header page (which is getting printed correctly as of now but in separate files) = Total 62 pages.

Hope I am clear to define the problems. Highly appreciate your effort. If you can share me your email id, I can send the sample PDF and Excel sheet also.

Thanks in advance.
Nitin

 

[nitkot]

Just to add on my previous response. I am fine with this condition.

Note that it is quite difficult in VBA to print files in a specific order because the printer queue may reorder the print jobs, according to their file size - usually in ascending order of file size. One solution which may work is to change the Windows printer properties to print directly to the printer, instead of spooling via the print queue, and the files should print in the same order as VBA 'prints' them.

Example Order of the Pages will be as below in single PDF.

School1 Header (1 Page)
Paper1 (3 Pages)......12 times
Paper2 (4 Pages)......6 times
Paper3 (2 Pages).....4 times
School2 Header (1 Page)
Paper1 (3 Pages)......8 times
Paper2 (4 Pages)......4 times
Paper3 (2 Pages).....3 times
....and so on....