Open Workbook Only If Worksheet Exists.

  • Hello all....I have a macro that 'opens all' workbooks in the specified folder. I've copied it over, but need to only open all the wbk's if a specified worksheet exists w/in the wbk..I know there is a dim ws statement that can be used, but how do i use it w/ the current macro? I'll post the 'open all below'...


    Some days, it's not even worth chewing through the Restraints....

  • Re: Open Only If WS present...


    Here's the code...



    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • 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...


    Great...thanks a bunch...I'll run thru it and see if I can step thru it...thanks again for the help....

    Some days, it's not even worth chewing through the Restraints....

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!