• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

Test for worksheet in a closed workbook.

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?
1 Solution
Rey Obrero (Capricorn1)Commented:
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
Jeroen RosinkCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

pwkenAuthor Commented:
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.
Rey Obrero (Capricorn1)Commented:
how did you use the function?
did you copy the function as it is?
pwkenAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now