Printing worksheets in excel spreadsheet

[Steve Fisher]

i have approx 45 spreadsheets in a directory (directory is
"c:\SCRIPTS".

Each spreadsheet has 3 worksheets.

Is there a simple macro (VBA) which can open up each workbook in turn
and then print the entire workbook and then close spreasheet and go on
to next one.


I presume gthe VBA is something along the lines of

1) Read the names of *.xls files in directory "c:\SCRIPTS" and store
this info

2) Open the file

3) Then
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
This will print all worksheets

4) Then
ActiveWindow.Close
to close workbook

5) Go back to (2) and open next file

Can anyone help me with the code for (1), (2) and (5)

Regards

Steve Fisher

 

[Ron de Bruin]

Hi Steve

Try this to print all the workboooks in C:\SCRIPTS

Sub Test()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\SCRIPTS"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
mybook.PrintOut
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[Vasant Nanavati]

Hi Steve:

You should be able to work it out from this Tom Ogilvy post:

http://google.com/[email protected]

Regards,

Vasant.