Macro - print all files in a folder & subfolders

C

cp

I would like a macro that would print all excel files (some with
multiple tabs) in a folder, and it's subfolders, based on a few
criteria.

Criteria:
1. There will be files in the folders that don't need to be printed,
but all files follow a specific naming convention that would allow me
to specify which files should be printed. For example, for Ocotber I
would want to print all files whoes name ends in OCT06. Maybe a message
box with a spot to specify what I'm looking for in the file names?
2. I don't want to print any files in any subfolders called VOID.

This print job could easily be printing off several hundred files each
month, and I realize that the macro/job could take quite some time. My
intention is to run this macro, and then go to lunch or let it run over
night and pick up my print outs when I come back to work the next
morning.

cp
 
J

Jim Cone

It has already been written. <g>
Try the free Excel add-in "List Files" by downloading from...
http://www.realezsites.com/bus/primitivesoftware
No registration required.
You won't have to go to lunch while it runs.
--
Jim Cone
San Francisco, USA


"cp" <[email protected]>
wrote in message
I would like a macro that would print all excel files (some with
multiple tabs) in a folder, and it's subfolders, based on a few
criteria.

Criteria:
1. There will be files in the folders that don't need to be printed,
but all files follow a specific naming convention that would allow me
to specify which files should be printed. For example, for Ocotber I
would want to print all files whoes name ends in OCT06. Maybe a message
box with a spot to specify what I'm looking for in the file names?
2. I don't want to print any files in any subfolders called VOID.

This print job could easily be printing off several hundred files each
month, and I realize that the macro/job could take quite some time. My
intention is to run this macro, and then go to lunch or let it run over
night and pick up my print outs when I come back to work the next
morning.

cp
 
J

Jim Cone

On the other hand, if you want to print the actual file content
and not list the file names then this should work...

Sub PrintSpecificWorkbooks()
'Jim Cone - San Francisco - November 2006
On Error GoTo ThatHurt
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim blnTask As Boolean

If Val(Application.Version) >= 10 Then
blnTask = Application.ShowWindowsInTaskbar
Application.ShowWindowsInTaskbar = False
End If
Application.ScreenUpdating = False
' Specify the folder...
strPath = "C:\Documents and Settings\user\My Documents\" 'CHANGE

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check type of file in the folder and open file.
For Each objFile In objFolder.Files
If objFile.Name Like "*OCT06.xls" Then 'CHANGE
strName = objFile.Name
Application.StatusBar = strName
Workbooks.Open objFile
Workbooks(strName).PrintOut
Workbooks(strName).Close savechanges:=False
End If
Next 'objFile
CloseOut:
On Error Resume Next
Application.ShowWindowsInTaskbar = blnTask
Application.StatusBar = False
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
Exit Sub

ThatHurt:
Beep
MsgBox "Error " & Err.Number & " " & Err.Description, , "PrintWorkbooks"
GoTo CloseOut
End Sub
'--------------


"Jim Cone"
wrote in message
It has already been written. <g>
Try the free Excel add-in "List Files" by downloading from...
http://www.realezsites.com/bus/primitivesoftware
No registration required.
You won't have to go to lunch while it runs.
 
C

cp

I definitely want to print the actual files, not a list of files.
Will this code print files in the subfolders of the strPath? And how
do I make it avoid files in subfolders called Void? If it matters,
some subfolders may be 3-4 folders deep.

Thanks!
cp
 
J

Jim Cone

Option Compare Text 'REQUIRED
Sub PrintSpecificWorkbooks()
'Jim Cone - San Francisco - November 2006
On Error GoTo ThatHurt
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim blnTask As Boolean

If Val(Application.Version) >= 10 Then
blnTask = Application.ShowWindowsInTaskbar
Application.ShowWindowsInTaskbar = False
End If
Application.ScreenUpdating = False
' Specify the folder...
strPath = "C:\Documents and Settings\user\My Documents\" 'CHANGE

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check type of file in the folder and open file.
For Each objFile In objFolder.Files
If objFile.Name Like "*OCT06.xls" Then 'CHANGE
strName = objFile.Name
Application.StatusBar = strName
Workbooks.Open objFile
Workbooks(strName).PrintOut
Workbooks(strName).Close savechanges:=False
End If
Next 'objFile
Call PrintSubFolderFiles(objFolder)
CloseOut:
On Error Resume Next
Application.ShowWindowsInTaskbar = blnTask
Application.StatusBar = False
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
Exit Sub
ThatHurt:
Beep
MsgBox "Error " & Err.Number & " " & Err.Description, , _
"PrintWorkbooks"
GoTo CloseOut
End Sub

Function PrintSubFolderFiles(ByRef oParentFolder As Object)
Dim oSubFolder As Object
Dim oFile As Object
Dim strName As String
For Each oSubFolder In oParentFolder.SubFolders
If InStr(1, oSubFolder.Name, "Void") = 0 Then 'CHANGE
For Each oFile In oSubFolder.Files
If oFile.Name Like "*OCT06.xls" Then 'CHANGE
strName = oFile.Name
Application.StatusBar = strName
Workbooks.Open oFile
Workbooks(strName).PrintOut
Workbooks(strName).Close savechanges:=False
End If
Next
End If
Call PrintSubFolderFiles(oSubFolder)
Next 'oSubFolder
End Function
'------------------------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html





I definitely want to print the actual files, not a list of files.
Will this code print files in the subfolders of the strPath? And how
do I make it avoid files in subfolders called Void? If it matters,
some subfolders may be 3-4 folders deep.
Thanks!
cp
 
Top