Print PDF files from folder.

[Tofik]

Hi guys, I need a VBA formula which can help me with PDF files PRINTING.

How I thing it should be ? :
1) Excel with button ( Print ) Which print all cells with numbered files.
2) Folder include reports names : 12763-12764_AZF-CS-4004 and in excel I write only AZF-CS-4004. Is it possible to Print in this way or I should write all the file name like 12763-12764_AZF-CS-4004 ?
3) Print sequence to avoid manual sorting printed papers.

 

[sijpie]

I see you are using nitroreader. Do you know how to send a print command to nitroreader?

The filename issue is no problem. Your macro can add "12763-12764_" to each of the filenames before sending the filename to Nitroreader .

 

[Tofik]

I see you are using nitroreader. Do you know how to send a print command to nitroreader?

The filename issue is no problem. Your macro can add "12763-12764_" to each of the filenames before sending the filename to Nitroreader .

To be honest, I don't know how to send a print command to nitroreader.
This Nitro Pdf have not a lot of commands.
The most useful from Nitro PDF for me is only OCR because it rotate automatically and I can avoid routine work.
If you will explain I will appreciate.

P.S I thought no one answer or write to me about this issue. Sorry for late answer.

 

[Tofik]

any solution ?

 

[John_w]

This macro should work, as long as Print is shown in the context menu when you right-click on a PDF file in File Explorer, because it uses the Shell32 object.

1) Excel with button ( Print ) Which print all cells with numbered files.
2) Folder include reports names : 12763-12764_AZF-CS-4004 and in excel I write only AZF-CS-4004. Is it possible to Print in this way or I should write all the file name like 12763-12764_AZF-CS-4004 ?
3) Print sequence to avoid manual sorting printed papers.

1) - Assign the macro to the Print button.

2) - Modify the PDFsFolder string to the full path of the folder containing the PDF files. The macro loops through cells on the active sheet in column A starting at row 2 to the last populated cell. The macro looks for the first .pdf file which matches the cell value with the wildcard "*" prepended and prints it if found.

3) - I think this would require a lot more code, probably using Windows API functions to ensure the current PDF has finished spooling to the printer before printing the next PDF. You could try manually changing the printer properties to print directly to the printer instead of spooling. That way, the PDFs should print in the order of the cells, although I haven't tried this. Regardless, I would test the code first by pausing the default printer's print queue and verify that the macro is sending the correct PDFs to the printer, to avoid wasting paper.

Public Sub Print_PDFs()

    Dim PDFsFolder As String
    Dim PDFfile As String
    Dim r As Long
   
    PDFsFolder = "C:\folder\path\PDFs folder\"      'CHANGE THIS
   
    If Right(PDFsFolder, 1) <> "\" Then PDFsFolder = PDFsFolder & "\"
   
    With ActiveSheet
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            PDFfile = Dir(PDFsFolder & "*" & .Cells(r, "A").Value & ".pdf")
            If PDFfile <> vbNullString Then
                Print_PDF PDFsFolder & PDFfile
            End If
        Next
    End With
   
End Sub


Private Sub Print_PDF(PDFfile As String)
   
    Static Sh As Object 'Shell32.Shell
    Dim folder As Variant, fileName As Variant
   
    folder = Left(PDFfile, InStrRev(PDFfile, "\"))
    fileName = Mid(PDFfile, InStrRev(PDFfile, "\") + 1)
   
    If Sh Is Nothing Then Set Sh = CreateObject("Shell.Application")
    Sh.Namespace(folder).Items.Item(fileName).InvokeVerb "Print"

End Sub

 

[Tofik]

Dear John_w,​

Your code works perfect. But I have the last question regarding paper format. Can I control A4 or A3 manually ? By cell C5 cell = A3 Print and C6 cell = A4 ? If it is possible it will be great for me. Thanks .​

 

[John_w]

But I have the last question regarding paper format. Can I control A4 or A3 manually ? By cell C5 cell = A3 Print and C6 cell = A4 ?

One way is to assign two different printer names to the same physical printer, one for A4 paper (tray?) and one for A3. Then in VBA set the default Windows printer using:

Public Sub Test_SetDefaultPrinter()
    SetDefaultPrinter "Printer name for A4"
End Sub


Public Sub SetDefaultPrinter(PrinterName As String, Optional ComputerName As String = ".")

    Dim Printer As Object, Printers As Object, WMIService As Object
    
    Set WMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & ComputerName & "\root\cimv2")
    Set Printers = WMIService.ExecQuery("Select * from Win32_Printer Where Name = '" & PrinterName & "'")

    For Each Printer In Printers
        Printer.SetDefaultPrinter
    Next

End Sub

 

[spasmex97]

hey i am trying to do almost same thing but code errors "Object variable or With block not set"

 

[John_w]

code errors "Object variable or With block not set"

Which line causes that error? Click Debug on the error message and the errant line is highlighted in yellow.

 

[spasmex97]

after this line, it gives the error and also it jumps over to the private sub after

Print_PDF PDFsfolder & PDFfile

do not iterates the loop