Further to my last thread - Check for worksheet in a closed file

[Les Stout]

Hi all,
I got the function below from Bob Phillips yesterday and would like to
know if it is possible for it to give me the name of the sheet that it
finds ?

Any help would be much appreciated...

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

 

[paul.robinson]

Hi all,
I got the function below from Bob Phillips yesterday and would like to
know if it is possible for it to give me the name of the sheet that it
finds ?

Any help would be much appreciated...

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 Developersdexhttp://www.developersdex.com***

Hi
The input sh to the function is the name of the sheet, or have I
missed something? Why would you need the function to return it if you
already know it??
Maybe if you are inside a loop???

For each Sh in Activeworkbook.worksheets
If IsSheetExists(FileName, Sh.Name) then
FoundSheetName = Sh.Name
End If
Next Sh

regards
Paul

 

[Les Stout]

Hi Paul, no the (sh) is the sheet that i am looking for. It is possible
to create a "KTL" with different sheet names, which has different data
on it. So just want to advise the user that the file is there but
incorrect as the sheet has a different name.

Best regards,

Les Stout

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