Test for worksheet in a closed workbook.

Posted on 2007-10-09
Last Modified: 2011-09-20
I need to test to see if a worksheet exists in a closed workbook. I am currently using the code I found posted on this site that allows you to access data from a closed workbook;

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String

'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If

'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

however, I have not been able to modify it to only test for the existance of a specified worksheet in the closed workbook.  Any help would be greatly appreciated?
Question by:pwken
    LVL 119

    Accepted Solution

    try this

    Function chkSheet(sht As String) As Boolean
    Dim xlObj As Object, j As Integer
    Set xlObj = CreateObject("excel.application")
        xlObj.workbooks.Open "C:\MyXx.xls", , True
        With xlObj
            For j = 1 To .worksheets.Count
                If .worksheets(j).Name = sht Then
                    chkSheet = True
                    Exit For
                End If
        End With
        Set xlObj = Nothing
    End Function
    LVL 10

    Expert Comment

    LVL 33

    Expert Comment

    Without open the workbook and getting the sheetnames you might take a look at:
    Search for: ReadSheetNames

    To use this example you need to set the following references in the VBA editor:
    ActiveX Data Objects 2.1 Library" and "Microsoft ADO Ext. 2.1 for DDL and

    Here an example of the code:

    Sub ReadSheetNames(TheCompleteFilePath As String)
        Dim cnn As New ADODB.Connection
        Dim cat As New ADOX.Catalog
        Dim tbl As ADOX.Table

        cnn.Open "Provider=MSDASQL.1;Data Source=" _
         & "Excel Files;Initial Catalog=" & TheCompleteFilePath
        cat.ActiveConnection = cnn
        For Each tbl In cat.Tables
            MsgBox Left$(tbl.Name, Len(tbl.Name) - 1)
        Next tbl

        Set cat = Nothing
        Set cnn = Nothing
    End Sub

    Sub test()
    Call ReadSheetNames("D:\My documents\Test.xls")
    End Sub


    Author Comment

    I've tried capricorn1 code - returns the following error "function call on left hand side of assignment must return a variant or object" This appears to occure where you set chkSheet = true.

    Will look into the other options today.
    LVL 119

    Expert Comment

    by:Rey Obrero
    how did you use the function?
    did you copy the function as it is?

    Author Comment

    Found the problem. I changed the function a little (send the path and sheet name). The problem was a typo on my part. Works great.

    Thanks for all of you folks support.

    Featured Post

    Wish Marketing would stop bothering you?

    Is your marketing department constantly asking for new email signature updates? Are they requesting a different design for every department? Do they need yet another banner added? Don’t let it get you down! There is an easy way to manage all of these requests...

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    A Bare Metal Image backup allows for the restore of an entire system to a similar or dissimilar hardware. They are highly useful for migrations and disaster recovery. Bare Metal Image backups support Full and Incremental backups. Differential backup…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now