What is the tab name when loading XLS file into vb 2008?

Question

I am trying to load an Excel 97 single tabbed spreadsheet into a SQL server table using the OleDbDataAdapter function. From all the documentation I have found, the first parameter should be "select * from [sheet1$]". This works fine for me only if the tab in the worksheet is named Sheet1. If I change what's in the [] brackets to the actual tab name, it also works fine.

The issue is that the spreadsheet being loaded is coming from another system and that system changes the tab name with each load. How can a figure out what the tab name is so that my users don't have to open the spreadsheet and rename the tab?

Answer 1

I've looked into this in the past, and I've always come to the conclusion that I had to enforce tab names on the people giving me the spreadsheets, there doesn't seem to be a reliable way to find them.

Answer 2

Ok, first, open an OleDB connection to your Excel spreadsheet.

Then, use something like the following (shown in VB.NET):

Imports System.Data
Imports System.Data.Odbc
Imports System.Data.OleDb

Dim DSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MYEXCELFILE.XLS;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=12"""
Dim OleDbConn As New OleDbConnection(DSN)
OleDbConn.Open()
Dim tables As System.Data.DataTable = OleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim tableName As String
Dim canBeImported As Boolean
For Each dr As System.Data.DataRow In tables.rows
    tableName = dr.Item("TABLE_NAME").ToString
    canBeImported = (tableName.IndexOfAny(New Char() {" ","[","]","'","""","`",";"}) < 0)
Next
OleDbConn.Close()

This will loop through all of the tables in the Excel file (table = sheets or named ranges) and get the name of each. In addition, it will filter out any tables that have characters in them that can't be accessed via ODBC (spaces are the most common issue).