Solved

Loop Through Folder AND Sub-Folders and Print All Files

Posted on 2010-08-13
10
662 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

707 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

21 Experts available now in Live!

Get 1:1 Help Now