Print specific worksheet from multiple excel files as batch

Hi,

i have a directory which includes many excel files.
I want to take every file and print the worksheet named S1 (1 of 3 Worksheets)
of every file.

DO i need a dos(cmd) batchfile or can i use an extra excelfile to do this?

Any hints ?

Best Regards,

Rainer

Here's a macro you can run to do what you need (change code in BOLD below to your needs):

Sub print_S1_sheets()
' Declare variables
Dim a(999999), i
Dim msg, style, title
Dim myDrive, myLocation, LoadDir As String

' Initialize variables...
' *** IMPORTANT *** Replace the drive name "C" below w/ your reference
myDrive = "C"
' *** IMPORTANT *** Replace the file path below w/ your reference
myLocation = "Temp\test"

' Turn off screen updating (program runs faster)...
Application.ScreenUpdating = False

' Notify user of progress...
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Searching for files; please wait..."
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

'Set the filepath to the correct directory...
ChDrive myDrive
ChDir myDrive & ":\" & myLocation

' Initialize the array counter...
i = 0
' Load array with names of Excel files in correct directory...
a(i) = Dir("*.xls")
' If no Excel files are present, alert user & exit program...
If a(i) = "" Then
GoTo FilesMissing ' the error handler...
Exit Sub
Else
' Loop through the Excel files to count files as loop limit...
Do
i = i + 1
a(i) = Dir()
Loop Until a(i) = ""
' Count the number of names in the array...
fileCount = CStr(i)
' Notify user of number of files to be printed...
Application.StatusBar = "Number of files to print: " & fileCount & " - please wait..."
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
' Start looping through the Excel file(s) to open & print sheet S1 from each one...
For MyFilCount = 0 To (fileCount - 1)
LoadDir = CurDir & "\"
Workbooks.Open LoadDir & (a(MyFilCount)), UpdateLinks:=0, _
ReadOnly:=False, IgnoreReadOnlyRecommended:=True
' Provide file processing status to user ...
Application.StatusBar = _
"Printing sheet S1 from file " & MyFilCount + 1 & " of " & fileCount & ": " & a(MyFilCount) & "; please wait..."
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Sheets("S1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWorkbook.Close SaveChanges:=False
Next MyFilCount
' Reset screen updating and status bar...
Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
End If
Exit Sub
'Error handler if no file(s) exist in directory...
FilesMissing:
' Define user dialog parameters
msg = "There are no files located in the directory." & Chr(13) & _
"The program stopped and no printouts were made."
style = vbOKOnly + vbCritical + vbDefaultButton1
title = "Missing File(s)"
' Display user dialog
Response = MsgBox(msg, style, title)
' Reset screen updating and status bar...
Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
End Sub

Hope this helps,
theDude