Solved

Loop Through Folder AND Sub-Folders and Print All Files

Posted on 2010-08-13
10
679 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:SpartanValor
  • 7
  • 2
10 Comments
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 33434212
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
 

Author Comment

by:SpartanValor
ID: 33434569
Unknown sub folder names. That'd be great if you could.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 33436364
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:SpartanValor
ID: 33437208
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
 

Author Comment

by:SpartanValor
ID: 33437237
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 33437382
Sorry, there was a typo that left the sentence a bit garbled.
0
 

Author Comment

by:SpartanValor
ID: 33437660
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
 

Author Comment

by:SpartanValor
ID: 33437791
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
 

Author Comment

by:SpartanValor
ID: 33437863
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
 

Author Comment

by:SpartanValor
ID: 33437875
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now