Refer to sheet2 - even when named

  • Thread starter Thread starter Masa Ito
  • Start date Start date
M

Masa Ito

I am using OLEDB in vb.net, and processing Excel files where I need to
refer to a specific sheet number. I don't know the name of the sheet. I
have tried:
SELECT * FROM [Sheet2$]
SELECT * FROM [Sheet2]
....

Sheet2 has a 'name' - but I don't know it. I am using a standard
connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended
Properties=""Excel 8.0;HDR=Yes""

I can easily read from Sheet1 with:
SELECT * FROM [Sheet1$]
because it is actually named Sheet1. If I rename Sheet2, or open it and
find it's name, it is easy - but I need to find a way that will handle the
thousands of files (and tons more coming). Does anyone have any tips? I
was hoping to avoid the expensive COM plugins etc.

btw - I found this to be very helpful -
http://support.microsoft.com/kb/316934/en-us
but it didn't give me my answer. If anything, it makes me think that it
isn't possible unless I know the exact cell range (which I don't)

Thanks!
 
This may be helpful to you. Query the spreadsheet and return the name
property of the indexed sheet you would like to use.
http://weblogs.asp.net/donxml/archive/2003/08/21/24908.aspx
Click to expand...

Thank you - this worked perfectly.
fwiw I ended up creating the following function:

Public Shared Function GetExcelSheetNames(ByVal fiExcel As FileInfo) As
ArrayList
Dim arl As New ArrayList
Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & fiExcel.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes"""
Dim cn As New OleDbConnection(sConn)
Dim dt As New DataTable
cn.Open()
dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object()
{Nothing, Nothing, Nothing, "Table"})
cn.Close()

For Each r As DataRow In dt.Rows
If dt.Columns.Contains("TABLE_NAME") Then
arl.Add(r("TABLE_NAME"))
End If
Next

Return arl
End Function


For anyone googling and finding this, note that the sheet names end with
$, seems the last 'TABLE' is the file name. ie: excel file named MyFile
with two sheets named: Sheet1, MySheet will return 3 strings in this
arraylist - Sheet1$, MySheet$, MyFile
 
Back
Top