How to print pdf files in order of cell list using Excel-VBA?

Question

I cannot figure out how I can print PDF files in order (order of cell list).

Sub PrintPDFFiles() 
    zProg = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe" 
    zLastRow = [a65536].End(xlUp).Row 
    temp = "a1:a" & zLastRow 
    zPrinter = "HP LaserJet Professional M1213nf MFP " 
    For Each cell In Range(temp) 
    zFile = cell.Value 
    If zFile Like "*.pdf" Then 
    Shell """" & zProg & """/n /h /t""" & zFile & """" 
    End If 
    Next 
    End Sub

Visual Aid:

Research I have done so far:

No command line switch to print files in order.

Update-1 MyCode After Suggestions:

I used object.run method in my code and I get error:

Sub PrintPDFFiles() 
    zProg = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe" 
    zLastRow = [a65536].End(xlUp).Row 
    temp = "a1:a" & zLastRow 
    zPrinter = "HP LaserJet Professional M1213nf MFP " 
    For Each cell In Range(temp) 
    zFile = cell.Value 
    If zFile Like "*.pdf" Then 
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    zCommand = zProg & " /n /h /t " & Chr(34) & zFile & Chr(34) & " " & zPrinter
    wsh.Run zCommand, windowStyle, waitOnReturn
    End If 
    Next 
    End Sub

Error:

Update-2 My code After Suggestions:

wsh.Run """Acrobat.exe"" /n /h /t" & Chr(34) & zfile & Chr(34) & " " & zPrinter, , waitOnReturn

Problem: I managed to print using run method , however I have to close Adobe Acrobat Reader after each file. I have to print 500+ files.

You are **Shell**ing in a loop; are you sure one **Shell** completes before the next one is launched ?? — Gary's Student, Oct 16 '17 at 23:47
Does Adobe stay open until the print is completed? What if you try to Declare Adobe as an object, Print, `Adobe.Quit`, then `Do While Not Adobe Is Nothing`. I do not have too much experience with manipulating Adobe, but it's a possible method. — K.Dᴀᴠɪs, Oct 16 '17 at 23:50
See: https://stackoverflow.com/questions/15951837/wait-for-shell-command-to-complete — Gary's Student, Oct 16 '17 at 23:50
@Gary'sStudent yes I am shelling in the loop.One shell completes before the next one the reason I know because I put break point.I don't understand why waiting would correct order of printing.I tried with just three files and debugged to see if loop is correct and one Shell completes before the next one - but still order of printing files is random. — shaadi, Oct 17 '17 at 00:01
@k.Davis as I am using /n /h /t switches adobe stay open just for the last file. — shaadi, Oct 17 '17 at 01:26
@shaadi When you say that one shell completes before the next one starts, do you mean the task invoked within the shell has completed, or just that control has returned to the next line of your VBA code? (`Shell` will return control to VBA as soon as the task is **started** - i.e. it runs synchronously - so the thread which is printing one file may still be running when the VBA code submits the next `Shell` command to start printing the next file.) — YowE3K, Oct 17 '17 at 01:33
@YowE3K When I say that one shell completes before the next one starts I mean control has returned to the next line of VBA code.As far As I think from C# experience controls return to next line if previously line is executed (might be this is different in VBA if yes then correct me). — shaadi, Oct 17 '17 at 03:17
The `Shell` command doesn't wait for whatever you have asked it to execute to finish before returning control to VBA. Therefore you may have been executing multiple print jobs simultaneously. Refer to [the question Gary's Student mentioned](https://stackoverflow.com/questions/15951837/wait-for-shell-command-to-complete) and check out the answer using `WScript.Shell` with its `waitOnReturn` option. That will probably work for you. — YowE3K, Oct 17 '17 at 03:48
@YowE3K Thanks for suggestion I am beginner with VBA require your help with code I don't have any idea how I can integrate object in my code. — shaadi, Oct 17 '17 at 04:25
Can someone please review my updated code in question section? — shaadi, Oct 17 '17 at 07:56

score 0 · Answer 1 · edited Jun 18 '20 at 17:33

0

I modify the code with GhostScript

Sub PrintPDFFiles()

    zProg = "gsprint -printer printerName -dPDFFitPage    " 
    zLastRow = [a65536].End(xlUp).Row 

    Dim Counter As Integer 

    For Counter = 1 To zLastRow 

        zFile = Worksheets("Sheet1").Cells(Counter, 1).Value ' Where 1 is the first column
        If zFile Like "*.pdf" Then 
            Shell """" & zProg & zFile & """" 
        End If 

    Next Counter 


End Sub

edited Jun 18 '20 at 17:33

Davesexcel

6,896
2
27
42

answered Oct 17 '17 at 04:33

Vinh Can Code

407
3
14

I don't see wait for shell command to complete in your answer. – shaadi Oct 17 '17 at 04:37
There is a sample of shell at http://dmcritchie.mvps.org/excel/shell.htm – Vinh Can Code Oct 17 '17 at 06:03
link is not relevant in my context - no waitOnReturn. The problem is that I want to finish one line before starting another line in loop. I am already using shell see my sample code in question. – shaadi Oct 17 '17 at 06:19
Oh, I know the issue you got. Can you use GhostScript instead of Acrobat? Here is sample: gsprint -printer "abc" -dPDFFitPage "pdf.pdf" – Vinh Can Code Oct 17 '17 at 13:19
Or bat call solution may help: https://stackoverflow.com/questions/5085491/closing-an-instance-of-acrobat-reader-from-command-line – Vinh Can Code Oct 17 '17 at 13:29

score 0 · Answer 2 · answered Oct 24 '17 at 07:30

0

Use this to print all sort of files from cell list given that files exist in file directory.Basically that software print all kind of files in order.

answered Oct 24 '17 at 07:30

shaadi

161
2
4
21

Vivek GOyal · Answer 3 · 2023-05-29T12:25:17.770

we can use following code to print PDFs through VBA but problem is the files are not getting printing in sequence.

Sub PrintPDF()

Dim folder As String
Dim PDFfilename As String

folder = Range("F9")
If Right(folder, 1) <> "\" Then folder = folder & "\"
   
PDFfilename = Dir(folder & "*.pdf", vbNormal)
While Len(PDFfilename) <> 0
    Print_PDF folder & PDFfilename
    PDFfilename = Dir()  ' Get next file
Wend

End Sub

Sub Print_PDF(sPDFfile As String)

Shell "C:\Program Files (x86)\Adobe\Acrobat Reader 2020\Reader\AcroRd32.exe /p /h " & Chr(34) & sPDFfile & Chr(34), vbNormalFocus

End Sub

How to print pdf files in order of cell list using Excel-VBA?

3 Answers3