locked
Using ODBC to find out the Sheet Name in an Excel file (non Excel version specific) RRS feed

  • Question

  • I'm using ODBC to read data from an Excel file.  I chose ODBC because I don't have to be specific on the version of the Excel file (like with OleDB). 

     

    The accessing of the data is working perfectly, but it's just that I need to be able to see the name of the sheet before accessing the sheet (at it requires a specific sheet name when performing the 'SELECT' query statement to pull the data from the spreadsheet).

     

    Can someone tell me how to get the Sheet Names of an Excel file with either ODBC or another internal type reference (not an Interop that needs to be available with the end executable of the compiled project)?

     

    Thank you 

    Thursday, June 19, 2008 5:37 PM

Answers

  • i am not sure about odbc but there is a getschema method which will return the tables

    yourdatatable = yourconnectionname.GetSchema("Tables")

     

    not sure if this works with odbc but worth a shot

     

    Thursday, June 19, 2008 6:00 PM
  • Your information gave me a great start (I thought it had something to do with '.GetSchema'.  After searching a bit more, I was able to add on to what you gave me.

     

    Code for everyone else:

     

    Dim CurrentSheet As String

    Dim SheetNumIndex As Integer = 0

    Dim TempTable As String()

    Dim SheetCount As Integer = 0

     

    g_ExcelConn.Open()

    SheetNameTable = g_ExcelConn.GetSchema("Tables")

    g_ExcelConn.Close()

     

    SheetCount = SheetNameTable.Rows.Count

    ReDim TempTable(SheetCount - 1)

     

    For Each dr In SheetNameTable.Rows

    CurrentSheet = SheetNameTable.Rows(SheetNumIndex)("TABLE_NAME").ToString

    TempTable(SheetNumIndex) = CurrentSheet

     

    ' Trims the '$' from the end of the Sheet Name in the pulled Excel Table info

    TempTable(SheetNumIndex) = TempTable(SheetNumIndex).TrimEnd("$")

     

    MsgBox(TempTable(SheetNumIndex))

    SheetNumIndex += 1

    Next

     

     

    I appreciate your help  

    Friday, June 20, 2008 5:04 AM

All replies

  • i am not sure about odbc but there is a getschema method which will return the tables

    yourdatatable = yourconnectionname.GetSchema("Tables")

     

    not sure if this works with odbc but worth a shot

     

    Thursday, June 19, 2008 6:00 PM
  • Your information gave me a great start (I thought it had something to do with '.GetSchema'.  After searching a bit more, I was able to add on to what you gave me.

     

    Code for everyone else:

     

    Dim CurrentSheet As String

    Dim SheetNumIndex As Integer = 0

    Dim TempTable As String()

    Dim SheetCount As Integer = 0

     

    g_ExcelConn.Open()

    SheetNameTable = g_ExcelConn.GetSchema("Tables")

    g_ExcelConn.Close()

     

    SheetCount = SheetNameTable.Rows.Count

    ReDim TempTable(SheetCount - 1)

     

    For Each dr In SheetNameTable.Rows

    CurrentSheet = SheetNameTable.Rows(SheetNumIndex)("TABLE_NAME").ToString

    TempTable(SheetNumIndex) = CurrentSheet

     

    ' Trims the '$' from the end of the Sheet Name in the pulled Excel Table info

    TempTable(SheetNumIndex) = TempTable(SheetNumIndex).TrimEnd("$")

     

    MsgBox(TempTable(SheetNumIndex))

    SheetNumIndex += 1

    Next

     

     

    I appreciate your help  

    Friday, June 20, 2008 5:04 AM