Excel Parseing

[guchu]

Excel.Application ExcelApp = new Excel.ApplicationClass(); ExcelApp.Visible = false; String WorkbookPath = filename; Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(WorkbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); Excel.Worksheet Sheet = (Excel.Worksheet)ExcelWorkbook.Sheets[1]; DataTable newTable = new DataTable(); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties= Excel 8.0"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + Sheet + "$]", conn); cmd.CommandType = CommandType.Text; OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(newTable); dataGridView1.DataSource = newTable; I am using this code to read an excel file and save the data in a datatable .... I am getting this error "System.__ComObject$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long." and the error pointer comes on the second last line of the code ... can neone help

[DarrenShultz]

You are building your SQL string by concatenating the object Sheet. This object is of type Excel.Worksheet - which is not a string. Thus, as with all objects, it is calling it's ToString() method to obtain a string value it can concatenate with the rest of the SQL string. Since Excel.Worksheet is a COM Object, it's ToString is returning "System.__ComObject". You'll need to obtain the actual text name of the worksheet and use that to build the SQL string.

[guchu]

Thankyou very much ... the answer was not only precise but was very well explained :)

[DarrenShultz]

You may also want to take a look at OleDbConnection.GetSchema (Google it to find the MSDN documentation link). This will allow you to directly obtain schema information for the Excel file. The schema is returned as a DataSet containing DataTables for a variety of entities. One of these DataTables will contain a list of table names (which are the Excel worksheet names you'll use in your queries). This will allow you to avoid using COM automation entirely (ie. Excel Object library) to obtain the list of Worksheets. The benefit: no dependency on Excel object library DLLs, registration, versioning, etc... all the headaches that come with COM automation.