Macro to automate printing
Posted by Dwight on September 20, 2001 5:27 AM
I work with 50+ spreadsheets which periodically must be printed; a tedious task. The spreadheets are identically structured and grouped within 7 directories. I would like a macro which, when one of the spreadsheets is open, will select all of the worksheets in that spreadsheet (each has 6 sheets), print them to the default printer, and close the file without saving. I would appreciate any thoughts on this or if there would be ways to automate the process even more ( a macro which would print all of the sheets for all spreadsheets within a given directory?).
Thanks.
Posted by Rob Jackson on September 20, 2001 6:33 AM
The following code should work if you open 1 file from each directory then run the code (1 directory at a time). Unfortunately I don't have a sutable setup to test it on.
Good Luck.
Rob
Sub Printer()
Dim PathNm As String
PathNm = ActiveWorkbook.Path
Dim FSO, Fldr, Fle, Fls
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fldr = FSO.GetFolder(PathNm)
Set Fls = Fldr.Files
For Each Fle In Fls
If Right(Fle, 4) = ".xls" Then
Workbooks.Open Filename:=Fle
Workbooks(Fle).Activate
For Each Sht In Sheets
Sht.PrintOut
Next Sht
Workbooks(Fle).Close savechanges:=False
End If
Next
End Sub
Posted by Dwight on September 20, 2001 7:32 AM
Rob: Thanks, might need some fine tuning
Tried your code; it seems to be heading in the right direction but paused twice:
1) Asks if I want to update links to other worksheets. The answer will always be "yes". Can you suggest the appropriate code?
2) After opening one of the other xls files in the directory (I tried the macro out on a directory which only has 3 files), it flashed this error message: "run time error '13': type mismatch", and clicking "de-bug" highlighted the line : "Workbooks(Fle).Activate"
Rob, I really apreciate your help. In case we can't get this to work, perhaps you could go ahead and suggest something which could be used one file at a time; this would still be easier than what I've been doing.
Thanks again,
Dwight
Posted by Rob Jackson on September 21, 2001 12:57 AM
Re: Rob: Thanks, might need some fine tuning
This bit will do the printing of all sheets.
For Each Sht In Sheets
Sht.PrintOut
Next Sht
Sorry it didn't work completely.
R.
Posted by Dwight on September 21, 2001 4:50 AM
No apology necessary. This is very useful, Thanks! NT
