Print Files in Date Order

[JimmyC]

I have a folder, with the following sub-folders on a CD: APR 2012, APR 2013, AUG 2012, AUG 2013, DEC 2012, DEC 2013, JUL 2012, JUL 2013, JUN 2012, JUN 2013, MAY 2012, MAY 2013, NOV 2012, NOV 2013, OCT 2012, OCT 2013, etc--I actually have 10 years of sub-folders or 120 sub-folders in total). There are PDF's and Word doc files in each sub-folder. My assignment is to print all the files in the folders but the hardcopy prints must be in calendar month / year order. I can do this manually as I can select the month /year folder in the correct sequential order to print it (i.e. my actual data actually begins in 2003).

I had to a similar task two months ago and thought that it would be the last time in my life I would spend 8 hours doing such an assignment---so I didn't worry about trying to make the process more efficient. This assignment will take at least that long as there are many more files in each sub-folder to print for the 10 year period. I thought about manually re-naming the folders so that Windows would display them in calendar / year sequential order--but I was told not to copy the CD to a hard drive (i.e., legal reasons) so that eliminated my ability to manually re-name the folders.

Even if I could gain persmission to copy the CD to my hard drive to re-name the sub-folders, I have been googling, but there does not appear to be a 3rd party solution free or paid, or even a VBA solution (i.e. I have Office 2010) that will then print a folder with sub-folders in order--plus the files in the sub-folders are a mixture of PDF and doc files. It is too bad the creator of the CD, did not realize that her folder name selection would be difficult to print in calendar month / year sequential order. Hopefully, I am just missing the obvious and there is an "easier" solution other than me "manually" selecting folders to print in calendar month / year order.

[HansV]

You could run the following macro from any Office application (Word, Excel, PowerPoint, Outlook, Access):

Code: Select all

Private Declare Function ShellExecute Lib "shell32.dll" 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

    Private Const SW_SHOWNORMAL As Long = 1

Sub PrintDocs()
    ' Path of CD drive - modify as needed
    Const strDrive = "F:"
    Dim lngYear As Long
    Dim lngMonth As Long
    Dim strFolder As String
    Dim strFile As String
    ' Loop through the years
    For lngYear = 2003 To 2013
        ' Loop through the months
        For lngMonth = 1 To 12
            ' Assemble folder path
            strFolder = strDrive & "\" & Format(DateSerial(lngYear, lngMonth, 1), "mmm yyyy") & "\"
            ' Loop through the files in the folder
            strFile = Dir(strFolder & "*.*")
            Do While strFile <> ""
                ' Print the file
                ShellExecute 0&, "Print", strFolder & strFile, 0&, 0&, SW_SHOWNORMAL
                ' Next file
                strFile = Dir
            Loop
            ' Optional - wait after printing all docs for a month
            MsgBox "Finished processing " & strFolder, vbInformation
        Next lngMonth
    Next lngYear
End Sub

[JimmyC]

Hans--thank you. I am off to make this work. I may be back for a question or two as I want to be able to understand and tweak the code for future use. Again, thank you. JimC

[JimmyC]

Hans,
I am still trying to study the code you provided. Is there a website, etc. that can help me learn and understand the "ShellExecute" you reference?

What you provided is so very helpful to me and I know I could adopt it for other uses but just can't seem to get a handle on exactly how the "ShellExecute" works (i.e. for example, I would like to list folders in a directory in EXCEL). My googling, and this is, at best, in laymen terms is that the ShellExecute lets VBA communicate with the Windows operating software--which in my case as accessing Windows Explorer type functions commands. Is that even close to accurate?

The other thing I am trying to figure out is how the code you provided seems to "know" that Jan is month 1, Feb is month 2, etc. I just don't "see" this translation happening in the code so I wondered if this something that is known to the "ShellExecute" functions?

Thanks for your patience. I really do try to learn and just not take your solutions and use them without a learning opportunity. I will never improve if I don't at least try to understand code. I am just so blown away by what you did for me..... JimC

[HansV]

ShellExecute is a so-called Windows API function; it can be used to make Windows perform standard actions on a file such as Open and Print; the advantage is that you don't have to specify which application should be used to open or print the file - the application associated with the file will be used.

ShellExecute doesn't know anything about month names. The 'translation' is done in the code:

DateSerial(lngYear, lngMonth, 1) is the date of the first day in the month specified by lngMonth and the year specified by lngYear.
Format(DateSerial(lngYear, lngMonth, 1), "mmm yyyy") formats this date using the format string "mmm yyyy". "mmm" stands for the abbreviated month name ("Jan", "Feb" etc.) and "yyyy" for the four-digit year ("2014").
Look up the Format function in the VBA help for more info.

[JimmyC]

Hans---thanks for the additional clarification and your patience to answer my question. I wil indeed look up the Format function. Thanks again. JimC