Print all Sheet2 of all Workbooks in Folder

[u473]

This little module crashes on "With Application.FileSearch".
That may not be the real culprit, but I have not found what is wrong.
Thank you for your help.
..
Sub PrintAllWS2()
Dim i As Long
Dim WB As Workbook
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "F:\VBA845\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
For i = 1 To .FoundFiles.Count
Set WB = Workbooks.Open(.FoundFiles(i))
Sheets("Sheet2").PrintOut
WB.Close False
Next i
End With
Application.ScreenUpdating = True
End Sub

 

[Joel]

Filesearch will not work with excel 2007. You should also make sure there
are at leastt 2 sheets in the workbook before you try to print. also you
left off WB from the Printout statement.


Sub PrintAllWS2()
Dim i As Long
Dim WB As Workbook
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "F:\VBA845\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
For i = 1 To .FoundFiles.Count
Set WB = Workbooks.Open(.FoundFiles(i))
if WB.Sheets.Count >= 2 then
WB.Sheets("Sheet2").PrintOut
end if
WB.Close False
Next i
End With
Application.ScreenUpdating = True
End Sub

 

[dbKemp]

This little module crashes on "With Application.FileSearch".
That may not be the real culprit, but I have not found what is wrong.
Thank you for your help.
.
Sub PrintAllWS2()
Dim i As Long
Dim WB As Workbook
Application.ScreenUpdating = False
With Application.FileSearch
    .NewSearch
    .LookIn = "F:\VBA845\"
    .SearchSubFolders = False
    .FileType = msoFileTypeExcelWorkbooks
    For i = 1 To .FoundFiles.Count
        Set WB = Workbooks.Open(.FoundFiles(i))
        Sheets("Sheet2").PrintOut
        WB.Close False
     Next i
End With
Application.ScreenUpdating = True
End Sub

I tried your code with Excel 2000.... Got through without error. ps.
Of course it didn't find any files

 

[u473]

Thank you for the tip. I am indeed using Excel 2007.
What I supposed to replace FileSearch ?
Thanks again

 

[Ron de Bruin]

Use Dir if you use 2007

Sub Example()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, Fnum As Long
Dim mybook As Workbook
Dim CalcMode As Long

'Fill in the path\folder where the files are
MyPath = "C:\Users\Ron\test"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
On Error GoTo 0

If Not mybook Is Nothing Then
On Error Resume Next
mybook.Worksheets("Sheet2").PrintOut
On Error GoTo 0
mybook.Close savechanges:=False
End If
Next Fnum
End If

'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub