Thread: Refer to sheet2 - even when named

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/archiv.../21/24908.aspx

Masa Ito wrote:
> 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!

"Charlie Brown" <cbrown@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/archiv.../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

"Charlie Brown" <cbrown@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/archiv.../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

"Charlie Brown" <cbrown@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/archiv.../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

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

Greg
Masa Ito wrote:
> "Charlie Brown" <cbrown@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/archiv.../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