How do you print all worksheets in a file without opening the fil.

[Guest]

I have a folder with about 30 files in it which all have more than one
worksheet attached. Is is possible to print all the worksheets in all the
files from the folder?
Thanks

 

[Ron de Bruin]

Hi timmtamm

Try this for all files in the folder C:\Data
It will print the second sheet of each file.

Copy the code in a normal module in a workbook that is
not in the folder C:\Data.

Open a new workbook
Alt-F11
Insert>Module from the menu bar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 you get a list of your macro's
Select "TestFile1" and press Run


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 <> ""
Set mybook = Workbooks.Open(FNames)
mybook.PrintOut
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub