Check if sheet exists in a closed workbook

F

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
 
T

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 said:
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
Click to expand...
 
F

FrigidDigit

Thanks for helping me out again Tom!

FD

Tom Ogilvy said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Kill all files in Folder, If folder exists 2
Splash screen not working in hidden workbook 2
Copy data from Closed Workbook 3
Test if sheet exists create if not 6
Rename closed workbook 2
Error retrieving data from closed workbook 5
copy selected cells to new sheet. 12
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0

Top