printing sheets in different files

[Jean-Paul De Winter]

Hi,
I have about 20 files containing te word "November"
Each file has an avarage of 15 sheets.
I want to print this all out... Is there a command to make the computer pick
all files and print all sheets automatically without having to open the all
individually???
Thanks

 

[Ron de Bruin]

Hi Jean-Paul

Try this for the folder C:\Data

Sub TestFile1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
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 <> ""
If FNames Like "*November*" Then
Set mybook = Workbooks.Open(FNames)
mybook.PrintOut
mybook.Close False
End If
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[Jean-Paul De Winter]

this is perfect...
Some remarks though
Files are opened only when entering a password (DBI in my case) can this be
implermented too??
In every workbook there is a kind of template (always sheet1). I don't need
to print this sheet... can I ommit this.
Last but not least. Can I print workbooks in reverse order so no manual
sorting is needed after the printing process?
Thanks
JP

 

[Ron de Bruin]

Hi Jean-Paul

Files are opened only when entering a password (DBI in my case)

See the Tips on this page
http://www.rondebruin.nl/copy3.htm

In every workbook there is a kind of template (always sheet1). I don't need
to print this sheet... can I ommit this.

How many sheets have each workbook?
Is this the same in every workbook?

Last but not least. Can I print workbooks in reverse order so no manual

Sort the files in the folder first

 

[Jean-Paul De Winter]

wokbooks can contain any number of sheets

With sorting I mean: in a workbook sheets are sorted alphabetically (tabs
contain names)... so when printed it would be nice getting the sheets in
reverse order.

CU
and thanks
JP

 

[Ron de Bruin]

Try this

Sub TestFile1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim a As Integer

SaveDriveDir = CurDir
MyPath = "C:\Data"
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 <> ""
If FNames Like "*November*" Then
Set mybook = Workbooks.Open(FNames)
For a = mybook.Sheets.Count To 2 Step -1
mybook.Sheets(a).PrintPreview
Next
mybook.Close False
End If
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Change PrintPreview to PrintOut

 

[Jean-Paul De Winter]

Perfect..
Just the password-thing remains to be completely perfect
JP

 

[Ron de Bruin]

Look in the link I posted Jean-Paul
Set mybook = Workbooks.Open(FNames, Password:="ron", WriteResPassword:="ron", UpdateLinks:=0)