View Full Version : macro help
maxflia10
03-09-2008, 08:31 PM
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
03-09-2008, 09:06 PM
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
03-09-2008, 09:30 PM
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
03-09-2008, 09:39 PM
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
03-09-2008, 09:42 PM
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
03-09-2008, 10:01 PM
Thanks Lucas, it was the \. Appreciate your time!!!!!
lucas
03-09-2008, 10:06 PM
Glad I could help Max. Be sure to mark your thread solved using the thread tools at the top of the page.....
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.