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

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" wrote in message
...
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

Thanks for helping me out again Tom!

FD

"Tom Ogilvy" wrote in message
...

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" wrote in message
...
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