Solved

Loop Through Folder AND Sub-Folders and Print All Files

Posted on 2010-08-13
10
697 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

724 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