OleDB troubles reading Excel spreadsheet

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I'm trying to use an OleDbConnection to read some data from an Excel
Spreadsheet. I create my connection string, and open the connection,
but when i query the sheet/table i get the error that OleDb could not
find the sheet. I've now tried to get a list of the sheets (to see if
i was using the wrong sheet name or something) but my list of sheets
comes back with 0 entries.
Any Help! this spreadsheet DOES have 2 sheets inside it... What am i
not doing? Are there any good examples/resources out there for reading
Excel in this way?

Private _connectionString as String

Dim rs As OleDbDataReader
Dim cmd As New OleDbCommand
Dim query As String
Dim table As String
Dim lookup As DataTable

'I build my connection string
newConnectionStr.Append("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=")
newConnectionStr.Append(fileName)
newConnectionStr.Append(";Extended Properties=Excel 8.0;")
_connectionString = newConnectionStr.ToString

'I open my connection and attempt to see what sheets are available
_connection.Open()
lookup = _connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
Nothing)
If lookup.Rows.Count > 0 Then
table = CStr(lookup.Rows(0)("TABLE_NAME"))
End If
_connection.Close()
_connection.Dispose()


This returns me nothing, 0 rows end up in 'lookup'.

If i try and run the query "Select * FROM [$Sheet 1$]" I get the "not
able to find" error.

Help?

Thanks!

jeffpriz
 
On 4 Dec 2003 09:04:12 -0800, (e-mail address removed) (Jeff) wrote:

¤ I'm trying to use an OleDbConnection to read some data from an Excel
¤ Spreadsheet. I create my connection string, and open the connection,
¤ but when i query the sheet/table i get the error that OleDb could not
¤ find the sheet. I've now tried to get a list of the sheets (to see if
¤ i was using the wrong sheet name or something) but my list of sheets
¤ comes back with 0 entries.
¤ Any Help! this spreadsheet DOES have 2 sheets inside it... What am i
¤ not doing? Are there any good examples/resources out there for reading
¤ Excel in this way?
¤
¤ Private _connectionString as String
¤
¤ Dim rs As OleDbDataReader
¤ Dim cmd As New OleDbCommand
¤ Dim query As String
¤ Dim table As String
¤ Dim lookup As DataTable
¤
¤ 'I build my connection string
¤ newConnectionStr.Append("Provider=Microsoft.Jet.OLEDB.4.0;
¤ Data Source=")
¤ newConnectionStr.Append(fileName)
¤ newConnectionStr.Append(";Extended Properties=Excel 8.0;")
¤ _connectionString = newConnectionStr.ToString
¤
¤ 'I open my connection and attempt to see what sheets are available
¤ _connection.Open()
¤ lookup = _connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
¤ Nothing)
¤ If lookup.Rows.Count > 0 Then
¤ table = CStr(lookup.Rows(0)("TABLE_NAME"))
¤ End If
¤ _connection.Close()
¤ _connection.Dispose()
¤
¤
¤ This returns me nothing, 0 rows end up in 'lookup'.
¤
¤ If i try and run the query "Select * FROM [$Sheet 1$]" I get the "not
¤ able to find" error.
¤
¤ Help?

If you have embedded spaces in the worksheet name you need to enclosed it within single quotes:

Select * FROM ['Sheet 1$']


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top