none
Strange Excel Worksheet names RRS feed

  • Question

  • Hello, 
    I want to receive the worksheet names for random Excel sheets, the below 
    mentioned solution works for most excel sheet but with the most important one 
    I get too many strange excel sheets in this format
    
    'WS1$' ''this one works
    'WS1$'print_tables
    'WS2$'Z_XX_XXX
    'WS2$'Print_area
    
    Where does the stuff after the $' come frome and how can I avoid this (already found some hints -hidden sheets and temp sheets here 
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=845735&SiteID=1 )but is there any way to just receive the actual worksheet names ('WS1$') and at best in the order the 
    occur in the document and not alphabetically.
    
    Thanks very much for your support and I hope i phrased the question in an 
    understandable fashion
    Jörg
    
    
    Code to get the Excel schema ( No Excel installed on the server itself)
    string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data 
    Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
    connection.Open();
    worksheets = connection.GetSchema("Tables");
    } 
    
    

     

    Tuesday, November 20, 2007 8:26 AM

Answers

  • What you will probably need to do is discard any name returned that does not contain a '$' as the last charater in the string. There is no other way to filter out these "table names" when returning the schema.

     

    With respect to returning the Worksheet names as they correspond to their ordinal position in the Workbook the bad news is that you will have to use either DAO or Excel automation. I have some old VB DAO code that will do this:

     

    Code Block

    Dim xlWB As DAO.Database
    Dim tbl As DAO.TableDef

     

    Set xlWB = OpenDatabase("C:\Test Files\Book20.xls", False, True, "Excel 8.0;")

    For Each tbl In xlWB.TableDefs
        If Right$(tbl.Name, 1) = "$" Then
            Debug.Print tbl.Name
        End If
    Next tbl


    xlWB.Close

     

     

    Tuesday, November 20, 2007 3:11 PM

All replies

  • What you will probably need to do is discard any name returned that does not contain a '$' as the last charater in the string. There is no other way to filter out these "table names" when returning the schema.

     

    With respect to returning the Worksheet names as they correspond to their ordinal position in the Workbook the bad news is that you will have to use either DAO or Excel automation. I have some old VB DAO code that will do this:

     

    Code Block

    Dim xlWB As DAO.Database
    Dim tbl As DAO.TableDef

     

    Set xlWB = OpenDatabase("C:\Test Files\Book20.xls", False, True, "Excel 8.0;")

    For Each tbl In xlWB.TableDefs
        If Right$(tbl.Name, 1) = "$" Then
            Debug.Print tbl.Name
        End If
    Next tbl


    xlWB.Close

     

     

    Tuesday, November 20, 2007 3:11 PM
  • Thanks very much!

     

    Wednesday, November 21, 2007 7:39 AM