Can't Find Worksheet

Question

I am trying to select the "Report Details" worksheet from my excel file. However, I am having trouble selecting it,

The Microsoft Jet database engine could not find the object 'Report Details'. Make sure the object exists and that you spell its name and the path name correctly.

if (fileExtension == ".xls")
{
    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}

OleDbConnection con = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
cmd.CommandText = "SELECT * FROM [Report Details]"; //ERROR HERE
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
con.Close();
GridView1.DataSource = dtExcelRecords;
GridView1.DataBind();

Viewing my "Tables" in the dataset viewer, the connection string is able to access the file. The Report Details column is displayed as 'Report Details$'. I have tried entering it that way, but I am still getting an error.

You don't show the connect string. Are you sure it is correct? — OldProgrammer, Dec 11 '13 at 01:39
Maybe using dtExcelSheetName to get the real "TABLE NAME" for Worksheet — Mate, Dec 11 '13 at 01:47
check this http://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order — Mate, Dec 11 '13 at 01:54
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Dec 11 '13 at 02:02

Siddharth Rout · Answer 1 · 2013-12-11T02:01:03.720

4

Your question title says EXCEL/C# Cant Find Worksheet

Your post says I am trying to select the "Report Details" WORKSHEET from my excel file.

The error you are getting is

The Microsoft Jet database engine could not find the object 'Report Details'. Make sure the object exists and that you spell its name and the path name correctly.

Solution

You are missing a $ sign

Try this (TRIED AND TESTED)

cmd.CommandText = "SELECT * FROM [Report Details$]";

edited Dec 11 '13 at 02:01

answered Dec 11 '13 at 01:51

Siddharth Rout

147,039
17
206
250

I believe the '$ and 's are causing the error. I have tried it that way with no luck. – user1698144 Dec 11 '13 at 02:03
My code is not for the table it for the worksheet. :) Like I said I have tried and tested it. I addressed your main problem. But anyways you have found what you `actually` wanted :) – Siddharth Rout Dec 11 '13 at 02:05

user1698144 · Answer 2 · 2013-12-11T02:04:01.150

0

I was able to get the tabel name throuh dtExcelSheetName

cmd.CommandText = "SELECT * FROM [" + con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[1][2].ToString() + "]";

It is working now.

edited Dec 11 '13 at 02:04

answered Dec 11 '13 at 01:55

user1698144

754
4
13
36

1

This is not the actual reason for the error that you were getting `The Microsoft Jet database engine could not find the object 'Report Details'. Make sure the object exists and that you spell its name and the path name correctly. ` – Siddharth Rout Dec 11 '13 at 01:56

Can't Find Worksheet

2 Answers2