Refer to sheet2 - even when named

13 Jun 2006 3:49 AM

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!

13 Jun 2006 4:05 AM

Charlie Brown

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

Masa Ito wrote:
Show quoteHide quote

> 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!

13 Jun 2006 2:08 PM

Masa Ito

"Charlie Brown" <cbr***@duclaw.com> wrote in
news:1150171508.201006.120950@f14g2000cwb.googlegroups.com:

> 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

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

13 Jun 2006 3:55 PM

GregR

Masa, could you repost paying attention to line break. TIA

Greg
Masa Ito wrote:
Show quoteHide quote

> "Charlie Brown" <cbr***@duclaw.com> wrote in
> news:1150171508.201006.120950@f14g2000cwb.googlegroups.com:
>
> > 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
>
> 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