macro help

[maxflia10]

I recorded a macro to open a folder and to open, print and close the files within in folder. Is there a way to loop thru each file in the folder and have it print the file?



Sub hhh()
'
' hhh Macro
' Macro recorded 3/9/2008 by Brian
'

'
ChDir _
"C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions\Dept Of Public Works.xls"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.Close
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions\Dept Of Water.xls"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.Close
End Sub
I have a few dozen files in the folder...

TIA

[lucas]

Try this Max.....be sure to change the path to suit your need.
Sub PrintAllWorksheetsOnWorkbooksInFolder()
Dim vFolder As String, vFile As String, WB As Workbook, WS As Worksheet

vFolder = "C:\temp\"
'vFolder = ThisWorkbook.Path & "\"

vFile = Dir(vFolder)
Application.ScreenUpdating = False
Do Until vFile = ""
If LCase(Right(vFile, 3)) = "xls" And vFile <> ThisWorkbook.Name Then
Set WB = Workbooks.Open(vFolder & vFile)
For Each WS In WB.Sheets
WS.PrintOut
Next WS
WB.Close False
End If
vFile = Dir()
Loop
Application.ScreenUpdating = True
End Sub

[maxflia10]

Lucas,

Thanks for your time. I substituted

C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions"

for

C:\temp\

but the worksheets did not print. I know I'm doing something wrong as I'm a complete dummy when it comes to code. What am I going wrong? My code looks like



Sub PrintAllWorksheetsOnWorkbooksInFolder()
Dim vFolder As String, vFile As String, WB As Workbook, WS As Worksheet

vFolder = "C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions"
'vFolder = ThisWorkbook.Path & "\"

vFile = Dir(vFolder)
Application.ScreenUpdating = False
Do Until vFile = ""
If LCase(Right(vFile, 3)) = "xls" And vFile <> ThisWorkbook.Name Then
Set WB = Workbooks.Open(vFolder & vFile)
For Each WS In WB.Sheets
WS.PrintOut
Next WS
WB.Close False
End If
vFile = Dir()
Loop
Application.ScreenUpdating = True
End Sub

[lucas]

make sure the path is exact and it looks like you need a backslash after the last word in your path.....ie

instead of this:
vFolder = "C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions"


try this:

vFolder = "C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions\"

[lucas]

You may wish to comment out the print line and use printPreview for testing like this:

Sub PrintAllWorksheetsOnWorkbooksInFolder()
Dim vFolder As String, vFile As String, WB As Workbook, WS As Worksheet

vFolder = "C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions\"
'vFolder = ThisWorkbook.Path & "\"

vFile = Dir(vFolder)
Application.ScreenUpdating = False
Do Until vFile = ""
If LCase(Right(vFile, 3)) = "xls" And vFile <> ThisWorkbook.Name Then
Set WB = Workbooks.Open(vFolder & vFile)
For Each WS In WB.Sheets
' WS.PrintOut
WS.PrintPreview
Next WS
WB.Close False
End If
vFile = Dir()
Loop
Application.ScreenUpdating = True
End Sub

Then you can reverse it when you get everything working the way you want.

[maxflia10]

Thanks Lucas, it was the \. Appreciate your time!!!!!

[lucas]

Glad I could help Max. Be sure to mark your thread solved using the thread tools at the top of the page.....