Question Excel As Dataset

[ggunter]

I have a project in which I read data from two Excel worksheets and use this to create 25+ reports. The program would be far easier if I could query the worksheets directly as opposed to reading the data into tables. I found some code for this but I can't seem to get my test code to work.

I am using Microsoft Excel 2003 and Visual Basic 2005.

Dim xlsFile As String = Directory.GetCurrentDirectory() & "\testxls.xls"

Dim xlsSheetName As String = "test"

Dim xlsConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" & xlsFile & "; Extended Properties=Excel 8.0;")

Dim strSql As String = "SELECT * FROM [" & xlsSheetName & "]"

Dim xlsCommand As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(strSql, xlsConnection)

ggTbl = New System.Data.DataSet

xlsCommand.Fill(ggTbl)

xlsConnection.Close()

When I run the above, it raises an error stating: "The Microsoft Jet database engine could not find the object 'test'. Make sure the object exist and that you spell its name and the path name correctly."

I have checked all the variables and paths and everything is correct. What am I missing?

 

[Tom]

Are you sure you have a worksheet name 'test' in that file?

Also try the following line replacement:

Dim strSql As String = "SELECT * FROM [" & xlsSheetName & "[COLOR="Red"]$[/COLOR]]"

 

[ggunter]

Tom,

Thank you for the quick reply. I wasn't aware the worksheet names needed to be followed with "$". Added it and the program runs fine!!