Open Workbook Only If Worksheet Exists.

Re: Open Only If WS present...

Here's the code...

Sub recTestOpenAll()
    Dim x As Integer
    Dim WB As String
    Dim wbk As Workbook
    Dim ws As Worksheet
    Dim lookforsheet As String
    Dim toclose As Boolean
    Dim ThisWkbName As String
    
    lookforsheet = InputBox("Which sheet do you want to look for?", "Sheet lookup")
    toclose = True
    ThisWkbName = ThisWorkbook.Name
    MsgBox ThisWkbName
    
    For x = 1 To 100
        WB = "G:\Rule Test Files\REC " & x & ".xls"
        On Error Resume Next
        Set wbk = Workbooks.Open(Filename:=WB)
        On Error GoTo 0
    Next
    
    For Each wbk In Application.Workbooks
        If wbk.Name <> ThisWkbName Then
            wbk.Activate
            For Each ws In wbk.Worksheets
                If ws.Name = lookforsheet Then toclose = False
            Next
            If toclose Then wbk.Close False
        End If
    Next
End Sub

Wigi

Re: Open Only If WS present...

With 100 or so workbooks you may want to check if the sheet exists before opening the file or it could take a long time. (It would be prudent to include functions to validate the path and the existence of the workbook as well).

I use a small function for this:[VBA]Function CheckSheetExist(Pth As String, WB As String, Sh As String) As Boolean
'(assumes the path and workbook is valid) attempt to extract a value from A1
'of the specified sheet, if we get an error the sheet does not exist
On Error GoTo NBG
If Application.ExecuteExcel4Macro("'" & Pth & "[" & WB & "]" & Sh & "'!R1C1") > "" Then
CheckSheetExist = True
Else
NBG: CheckSheetExist = False
Err.Clear
End If
On Error GoTo 0
End Function[/VBA]
to test it [VBA]Sub testit()
Dim check As Boolean
check = CheckSheetExist("C:\", "Book1.xls", "Sheet1")
MsgBox check
End Sub[/VBA]
Modifying the original code [untested] maybe like: [VBA]Sub recTestOpenAll()
'
Dim x As Integer
Dim WB As String
Dim wbk As Workbook
Dim WS As String

WS = "Sheet1" 'the sheet name to check for

For x = 1 To 100

WB = "G:\Rule Test Files\REC " & x & ".xls"

If CheckSheetExist("G:\Rule Test Files\", "REC " & x & ".xls", WS) Then

On Error Resume Next
Set wbk = Workbooks.Open(FileName:=WB)
On Error GoTo 0
If Not wbk Is Nothing Then
End If

End If
Next
End Sub[/VBA]

Re: Open Only If WS present...

have_a_cup, please don't use abbreviations in Thread titles. I will change WS to Worksheet.