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
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