Hello all, is it possible to get a worksheet name in a closed workbook ?

If it is possible could you help me with some script please ?
--
Les

Here's a simple function to test fr it

Function IfSheetExists(FileName As String, sh As String) As Boolean
Dim oConn As Object

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";" & _
"Extended Properties=Excel 8.0;"

On Error Resume Next
oConn.Execute "SELECT 1 FROM [" & sh & "$] WHERE 0=1"
IfSheetExists = (Err.Number = 0)

oConn.Close
Set oConn = Nothing

End Function

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Les" <(E-Mail Removed)> wrote in message
news:52A6C1B7-1885-4DD8-AD7A-(E-Mail Removed)...
> Hello all, is it possible to get a worksheet name in a closed workbook ?
>
> If it is possible could you help me with some script please ?
> --
> Les

Hi Bob, thanks for the reply, i have done script like this, but get an
error msg. ?

Could you help ?

Sub DoesSheetExist()
'

If IfSheetExists("TOOL TRACKING") = True Then

Workbooks.Open(FileName:="\\nv09002\tpdrive\Projects\General\50_Comparis
ons\KTL's\" & myKTLIH & ".xls"). _
RunAutoMacros Which:=xlAutoOpen
Else
MsgBox "You have loaded the incorrect KTL", vbOKOnly, "ERROR"
End If

End Sub
'-----------------------------------------------------------------
Function IfSheetExists(FileName As String, sh As String) As Boolean
Dim oConn As Object, myKTL As String

myKTL = "90ZA0810"

Set sh = "TOOL TRACKING"
Set FileName =
"\\nv09002\tpdrive\Projects\General\50_Comparisons\KTL's\" & myKTLIH &
".xls"

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";" & _
"Extended Properties=Excel 8.0;"

On Error Resume Next
oConn.Execute "SELECT 1 FROM [" & sh & "$] WHERE 0=1"
IfSheetExists = (Err.Number = 0)

oConn.Close
Set oConn = Nothing

End Function


Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

First, don't change Bob's function. It should just be pasted into the module.

Second, Bob's function want's a filename passed to the function, then the
worksheet name, so maybe something like:

If IfSheetExists("\\nv09002\tpdrive\Projects\General\50_Comparisons\KTL's\" _
& myKTLIH & ".xls", "TOOL TRACKING") = True Then

Les Stout wrote:
>
> Hi Bob, thanks for the reply, i have done script like this, but get an
> error msg. ?
>
> Could you help ?
>
> Sub DoesSheetExist()
> '
>
> If IfSheetExists("TOOL TRACKING") = True Then
>
> Workbooks.Open(FileName:="\\nv09002\tpdrive\Projects\General\50_Comparis
> ons\KTL's\" & myKTLIH & ".xls"). _
> RunAutoMacros Which:=xlAutoOpen
> Else
> MsgBox "You have loaded the incorrect KTL", vbOKOnly, "ERROR"
> End If
>
> End Sub
> '-----------------------------------------------------------------
> Function IfSheetExists(FileName As String, sh As String) As Boolean
> Dim oConn As Object, myKTL As String
>
> myKTL = "90ZA0810"
>
> Set sh = "TOOL TRACKING"
> Set FileName =
> "\\nv09002\tpdrive\Projects\General\50_Comparisons\KTL's\" & myKTLIH &
> ".xls"
>
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & FileName & ";" & _
> "Extended Properties=Excel 8.0;"
>
> On Error Resume Next
> oConn.Execute "SELECT 1 FROM [" & sh & "$] WHERE 0=1"
> IfSheetExists = (Err.Number = 0)
>
> oConn.Close
> Set oConn = Nothing
>
> End Function
>
> Best regards,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

Thanks so much Dave, as i am not a programmer i am a little unsure of
these things but have managed to get it working in the mean time, with
the following code.

Sub DoesSheetExist()
'
Dim myKTLih As String

myKTLih = "90IH0810"


If
IfSheetExists("\\nv09002\tpdrive\Projects\General\50_Comparisons\KTL's\"
& myKTLih & ".xls", "TOOL TRACKING") = True Then

Workbooks.Open(FileName:="\\nv09002\tpdrive\Projects\General\50_Comparis
ons\KTL's\" & myKTLih & ".xls"). _
RunAutoMacros Which:=xlAutoOpen

Else
MsgBox "You have loaded the incorrect KTL", vbOKOnly, "ERROR"
End If

End Sub
'-----------------------------------------------------------------
Function IfSheetExists(FileName As String, sh As String) As Boolean
Dim oConn As Object



'FileName = "\\nv09002\tpdrive\Projects\General\50_Comparisons\KTL's\" &
myKTLih & ".xls"
'sh = Worksheets("TOOL TRACKING")

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";" & _
"Extended Properties=Excel 8.0;"

On Error Resume Next
oConn.Execute "SELECT 1 FROM [" & sh & "$] WHERE 0=1"
IfSheetExists = (Err.Number = 0)

oConn.Close
Set oConn = Nothing

End Function



Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***