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.
[vba]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 [/vba]

[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:
[vba] vFolder = "C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions"
[/vba]

try this:
[VBA]
vFolder = "C:\Documents and Settings\Administrator\My Documents\Excel\Purchase Requisitions\"
[/VBA]

[lucas]

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

[VBA]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[/VBA]

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.....