L
Les Stout
Hi All,i got some help with code from this site from Dave and Bob which
works great. I have realised that the workbook i get from Germany has
all the various sheets in the book but some are hidden. I am looking for
the "Qualitaet" sheet which is on all, just it is hidden if you request
the "Tool Tracking" option as an example.
I would like to look for the unhidden sheet, is this possible ? If so
how would i change my code ??
Sub DoesSheetExist()
'
Dim myKTLih As String
myKTLih = "90ZA0810"
If
IfSheetExistsTest("\\nv09002\tpdrive\Projects\General\1700_Management_Re
port\KTL\" _
& myKTLih & ".xls", "Qualitaet") = True Then
MsgBox "The sheet exists"
Else
MsgBox "You have loaded the incorrect KTL", vbOKOnly, "ERROR"
End If
End Sub
'-----------------------------------------------------------------
Function IfSheetExistsTest(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"
IfSheetExistsTest = (Err.Number = 0)
oConn.Close
Set oConn = Nothing
End Function
Best regards,
Les Stout
*** Sent via Developersdex http://www.developersdex.com ***
works great. I have realised that the workbook i get from Germany has
all the various sheets in the book but some are hidden. I am looking for
the "Qualitaet" sheet which is on all, just it is hidden if you request
the "Tool Tracking" option as an example.
I would like to look for the unhidden sheet, is this possible ? If so
how would i change my code ??
Sub DoesSheetExist()
'
Dim myKTLih As String
myKTLih = "90ZA0810"
If
IfSheetExistsTest("\\nv09002\tpdrive\Projects\General\1700_Management_Re
port\KTL\" _
& myKTLih & ".xls", "Qualitaet") = True Then
MsgBox "The sheet exists"
Else
MsgBox "You have loaded the incorrect KTL", vbOKOnly, "ERROR"
End If
End Sub
'-----------------------------------------------------------------
Function IfSheetExistsTest(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"
IfSheetExistsTest = (Err.Number = 0)
oConn.Close
Set oConn = Nothing
End Function
Best regards,
Les Stout
*** Sent via Developersdex http://www.developersdex.com ***