Print File - Macro

Question

I have created a macro that I can use to print PDF files. The PDF files will be saved in a folder to print. The path will be given that folder path where I save all PDF files. My questions are:

1) Once the files are saved in folder, is it possible to sort it automatically like first come first print. Now the issue is - prints did not come out in order of how the files are – we have to reconcile all files, so looking for each one in a random list order would take lots of time.

2) Is it possible to have the files automatically deleted from the folder after the printing is completed?

Public Sub Print_All_PDF_Files_in_Folder()

Dim folder As String
Dim PDFfilename As String

folder = "\\maple.fg.rbc.com\data\toronto\user_3\315606053\myWorkspace\Desktop\test"    'CHANGE AS REQUIRED
If Right(folder, 1) <> "\" Then folder = folder & "\"

PDFfilename = Dir(folder & "*.pdf", vbNormal)
While Len(PDFfilename) <> 0
    If Not PDFfilename Like "*ecg*" Then
        Print_PDF folder & PDFfilename
    End If
    PDFfilename = Dir()  ' Get next matching file
  Wend
End Sub

Sub Print_PDF(sPDFfile As String)
  Shell "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe /p /h " & Chr(34) & sPDFfile & Chr(34), vbNormalFocus
' This is path of Adobe in the desktop
End Sub

score 1 · Answer 1 · answered May 15 '18 at 08:06

There is no build in way to sort files. However, it is rather easy to read the filenames and -dates into arrays and sort them manually, but you have to use the FilesystemObject rather than using dir to get the file dates.

You can find an example to do so for example here: https://social.msdn.microsoft.com/Forums/office/en-US/5f27936e-1d98-44df-8f69-0f81624c4b92/read-files-in-a-folder-in-descending-order-with-file-name-or-date-created?forum=accessdev

The command to delete a file with VBA is kill, or you can use the .DeleteFile method of FilesystemObject. However, this will work only if the printing is already done, so you have to wait for your shell-command to finish. For this, you have to use the wscript.shell, see for example here https://stackoverflow.com/a/8906912/7599798

Print File - Macro

1 Answers1