Loop Through Folder AND Sub-Folders and Print All Files

How to a create a Macro that will search through a folder, and all subfolders, and print all files?  The files I am opening are linked and must be updated when opened/printed.  I don't want to have to click "Yes" to the update each time (to a few hundred files).  I tried a few programs from google search but they don't search subfolders and they didn't resolve the requirement to update the file.  Thank you.
SpartanValorAsked:
Who is Participating?
 
GrahamSkanRetiredCommented:
You will need to set up the QueryRefresh registry entry in the link hitsdoshi1. Then you should be able to run this VBA code without interruption.
Option Explicit

Sub PrintAll()
    FindFiles "M:\Allwork", "*.xl*" '<--- edit for your root folder.
End Sub

Sub FindFiles(strFolder As String, strFilePattern As String)
    Dim strFileName As String
    Dim strFolders() As String
    Dim iFolderCount As Integer
    Dim i As Integer
    Dim xlWbk As Workbook
    
    'collect child folders
    strFileName = Dir$(strFolder & "\", vbDirectory)
    Do Until strFileName = ""
        If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
            If Left$(strFileName, 1) <> "." Then
                ReDim Preserve strFolders(iFolderCount)
                strFolders(iFolderCount) = strFolder & "\" & strFileName
                iFolderCount = iFolderCount + 1
            End If
        End If
        strFileName = Dir$()
    Loop
   
    'process files in current folder
    strFileName = Dir$(strFolder & "\" & strFilePattern)
    Do Until strFileName = ""
        DoEvents
        Set xlWbk = Workbooks.Open(strFolder & "\" & strFileName)
        xlWbk.PrintOut
        xlWbk.Close
        strFileName = Dir$()
    Loop
   
    'look through child folders
    For i = 0 To iFolderCount - 1
        FindFiles strFolders(i), strFilePattern
    Next i
End Sub

Open in new window

0
 
hitsdoshi1Commented:
Here is the answer to the second part of your question - to disable "update" prompt while opening excel file..

http://support.microsoft.com/kb/248204

So there are known sub-directory in folders ? or you have to get the name of sub-folder as well ?

I think we can come up with some kind of macro to pick up file and print it

Please provide more details.....if possible attach sample...
0
 
SpartanValorAuthor Commented:
Unknown sub folder names. That'd be great if you could.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SpartanValorAuthor Commented:
Thank you Graham.  I don't know what this means,

"You will need to set up the QueryRefresh registry entry in the link hitsdoshi1. Then you should be able to run this VBA code without interruption."

Can you please explain?  Thanks.
0
 
SpartanValorAuthor Commented:
Ok, that was stupid.  Nevermind.  I just woke up, a little groggy.  Ok, this looks great.  One problem, its going to be next to impossible to access/change regedit at work due to the security settings.  I will file a request with IT and hopefully it can be done.  I will try this on Monday.  Thank you very much Graham!
0
 
GrahamSkanRetiredCommented:
Sorry, there was a typo that left the sentence a bit garbled.
0
 
SpartanValorAuthor Commented:
Hmmm, weird.  I was able to add the DWORD.  I'm going to try it.  Anyway to adjust the above so that it only prints from page 1 to page 1?
0
 
SpartanValorAuthor Commented:
Ok, so after adding to regedit I restarted my pc and then ran the Sub().  Unfortunately, its still asking me to "update" or "don't update".  Any suggestions?
0
 
SpartanValorAuthor Commented:
Sorry for the real time posts.  I finally got the update prompt to go away by selecting Tools -> Options -> Edit -> (Uncheck) Ask to Update Automatic Links.  But now after it prints the file it ask me if I want to save it before closing it.  How can I elect "Yes" automatically?  Thanks.
0
 
SpartanValorAuthor Commented:
Ok so that was stupid.  I added "xlWbk.Save" after "xlWbk.PrintOut".  So I think that settles it.  This is now resolved.  Thank you very much Graham.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.