?
Solved

Loop Through Folder AND Sub-Folders and Print All Files

Posted on 2010-08-13
10
Medium Priority
?
699 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 2000 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
Office 365 Training for Admins - 7 Day Trial

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.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 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