Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further to my last thread - Check for worksheet in a closed file
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further to my last thread - Check for worksheet in a closed file
On Dec 4, 2:50 pm, Les Stout wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further to my last thread - Check for worksheet in a closed file
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for worksheet in closed workbook | Excel Programming | |||
distributed vba project - multiple thread / concurrent file access | Excel Programming | |||
CAN I IMPORT DATA FROM A CLOSED FILE TO A NEW WORKSHEET | Excel Discussion (Misc queries) | |||
How can I check the file size of each worksheet in an Excel Doc | Excel Discussion (Misc queries) | |||
Check to see if Excel has closed | Excel Programming |