Printing worksheets in excel spreadsheet
 

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

Printing worksheets in excel spreadsheet
 

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Steve Fisher" wrote in message m...
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

Printing worksheets in excel spreadsheet
 

Hi Steve:

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

http://google.com/groups?threadm=8fj...ndspr ing.net

Regards,

Vasant.

"Steve Fisher" wrote in message
m...
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