Check if sheet exists in a closed workbook

[FrigidDigit]

Hi all,

Is it possible to determine whether a sheet exists in a workbook without
opening it?

I am creating links to a closed workbook in the active workbook (Thanks to
Tom, Rowan, Ron and Dave for all the help) but have found that the "same"
sheet has different names in different workbooks i.e. the name could be
either Inv Summ or Invoice Summary. I have tried to add a check for this
but the builtin Excel "select sheet to update values from" dialog pops up
before my check is completed.

Any ideas?

Below is the code:

Sub NewGetData(fName As String, SheetName As String, _
Rnge As String, Location As Range, bBool As Boolean)
Dim fName1 As String, fName2 As String
Dim sStr As String
On Error GoTo SomethingWrong
Restart:
fName1 = Left(fName, InStrRev(fName, "\"))
fName2 = "[" & Right(fName, Len(fName) - Len(fName1)) & "]"
sStr = "='" & fName1 & fName2 & SheetName & "'!" & Rnge
rng.Formula = sStr
Exit Sub
SomethingWrong:
If SheetName = "Invoice Summary" Then
SheetName = "Inv Summ"
ElseIf SheetName = "Inv Summ" Then
SheetName = "Invoice Summary"
End If
GoTo Restart

End Sub

Thanks!!

FD

[Tom Ogilvy]

The test routine shows how to get you sheet name and then use it in your
call to the revised NewGetData routine. Include the new GetName function
in your module. You will need to create references (in the VBE
Tools=>References) to

Microsoft ActiveX Data Objects 2.x Library
Microsoft ADO Ext. 2.x for DDL and Security

In Office 2003, Windows XP, the version was 2.7 in each case.

Sub TestRoutine()
Dim bkName As String
Dim SheetName As String
bkName = "C:\Data6\ABCD.xls"
SheetName = GetName(bkName)
If SheetName <> "" Then
NewGetData bkName, SheetName, "A1", _
ActiveSheet.Range("A1"), False
NewGetData bkName, SheetName, "A2", _
ActiveSheet.Range("A2"), False
End If
End Sub


Sub NewGetData(fName As String, SheetName As String, _
Rnge As String, Location As Range, bBool As Boolean)
Dim fName1 As String, fName2 As String
Dim sStr As String
fName1 = Left(fName, InStrRev(fName, "\"))
fName2 = "[" & Right(fName, Len(fName) - Len(fName1)) & "]"
sStr = "='" & fName1 & fName2 & SheetName & "'!" & Rnge
Location.Formula = sStr
End Sub

Function GetName(bkName As String)
Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim t As ADOX.Table
On Error GoTo ErrHandler

Set cn = New ADODB.Connection
cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _
& "Initial Catalog=" & bkName
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn

GetName = ""
For Each t In cat.Tables
If InStr(1, t.Name, "inv", vbTextCompare) > 0 Then
GetName = Replace(Replace( _
t.Name, "'", ""), "$", "")
Exit For
End If
Next t
Set cat = Nothing
cn.Close
Set cn = Nothing
Exit Function
ErrHandler:
GetName = ""
End Function

--
Regards,
Tom Ogilvy



"FrigidDigit" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> Is it possible to determine whether a sheet exists in a workbook without
> opening it?
>
> I am creating links to a closed workbook in the active workbook (Thanks to
> Tom, Rowan, Ron and Dave for all the help) but have found that the "same"
> sheet has different names in different workbooks i.e. the name could be
> either Inv Summ or Invoice Summary. I have tried to add a check for this
> but the builtin Excel "select sheet to update values from" dialog pops up
> before my check is completed.
>
> Any ideas?
>
> Below is the code:
>
> Sub NewGetData(fName As String, SheetName As String, _
> Rnge As String, Location As Range, bBool As Boolean)
> Dim fName1 As String, fName2 As String
> Dim sStr As String
> On Error GoTo SomethingWrong
> Restart:
> fName1 = Left(fName, InStrRev(fName, "\"))
> fName2 = "[" & Right(fName, Len(fName) - Len(fName1)) & "]"
> sStr = "='" & fName1 & fName2 & SheetName & "'!" & Rnge
> rng.Formula = sStr
> Exit Sub
> SomethingWrong:
> If SheetName = "Invoice Summary" Then
> SheetName = "Inv Summ"
> ElseIf SheetName = "Inv Summ" Then
> SheetName = "Invoice Summary"
> End If
> GoTo Restart
>
> End Sub
>
> Thanks!!
>
> FD
>
>

[FrigidDigit]

Thanks for helping me out again Tom!

FD

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>
> The test routine shows how to get you sheet name and then use it in your
> call to the revised NewGetData routine. Include the new GetName
> function
> in your module. You will need to create references (in the VBE
> Tools=>References) to
>
> Microsoft ActiveX Data Objects 2.x Library
> Microsoft ADO Ext. 2.x for DDL and Security
>
> In Office 2003, Windows XP, the version was 2.7 in each case.
>
> Sub TestRoutine()
> Dim bkName As String
> Dim SheetName As String
> bkName = "C:\Data6\ABCD.xls"
> SheetName = GetName(bkName)
> If SheetName <> "" Then
> NewGetData bkName, SheetName, "A1", _
> ActiveSheet.Range("A1"), False
> NewGetData bkName, SheetName, "A2", _
> ActiveSheet.Range("A2"), False
> End If
> End Sub
>
>
> Sub NewGetData(fName As String, SheetName As String, _
> Rnge As String, Location As Range, bBool As Boolean)
> Dim fName1 As String, fName2 As String
> Dim sStr As String
> fName1 = Left(fName, InStrRev(fName, "\"))
> fName2 = "[" & Right(fName, Len(fName) - Len(fName1)) & "]"
> sStr = "='" & fName1 & fName2 & SheetName & "'!" & Rnge
> Location.Formula = sStr
> End Sub
>
> Function GetName(bkName As String)
> Dim cn As ADODB.Connection
> Dim cat As ADOX.Catalog
> Dim t As ADOX.Table
> On Error GoTo ErrHandler
>
> Set cn = New ADODB.Connection
> cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _
> & "Initial Catalog=" & bkName
> Set cat = New ADOX.Catalog
> Set cat.ActiveConnection = cn
>
> GetName = ""
> For Each t In cat.Tables
> If InStr(1, t.Name, "inv", vbTextCompare) > 0 Then
> GetName = Replace(Replace( _
> t.Name, "'", ""), "$", "")
> Exit For
> End If
> Next t
> Set cat = Nothing
> cn.Close
> Set cn = Nothing
> Exit Function
> ErrHandler:
> GetName = ""
> End Function
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "FrigidDigit" <[email protected]> wrote in message
> news:[email protected]...
>> Hi all,
>>
>> Is it possible to determine whether a sheet exists in a workbook without
>> opening it?
>>
>> I am creating links to a closed workbook in the active workbook (Thanks
>> to
>> Tom, Rowan, Ron and Dave for all the help) but have found that the "same"
>> sheet has different names in different workbooks i.e. the name could be
>> either Inv Summ or Invoice Summary. I have tried to add a check for this
>> but the builtin Excel "select sheet to update values from" dialog pops up
>> before my check is completed.
>>
>> Any ideas?
>>
>> Below is the code:
>>
>> Sub NewGetData(fName As String, SheetName As String, _
>> Rnge As String, Location As Range, bBool As Boolean)
>> Dim fName1 As String, fName2 As String
>> Dim sStr As String
>> On Error GoTo SomethingWrong
>> Restart:
>> fName1 = Left(fName, InStrRev(fName, "\"))
>> fName2 = "[" & Right(fName, Len(fName) - Len(fName1)) & "]"
>> sStr = "='" & fName1 & fName2 & SheetName & "'!" & Rnge
>> rng.Formula = sStr
>> Exit Sub
>> SomethingWrong:
>> If SheetName = "Invoice Summary" Then
>> SheetName = "Inv Summ"
>> ElseIf SheetName = "Inv Summ" Then
>> SheetName = "Invoice Summary"
>> End If
>> GoTo Restart
>>
>> End Sub
>>
>> Thanks!!
>>
>> FD
>>
>>
>
>