The Microsoft Jet database engine could not find the object 'Sheet1$'

Question

I'm attempting to read a spreadsheet file called Book1.xls which contains a worksheet called Sheet1

However I'm getting the following error:

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

Here is a snippet of the code I'm using:

Dim dt As DataTable = New DataTable()
Select Case fileExt
    Case ".csv"
        Dim reader As New CsvReader
        dt = reader.GetDataTable(filePath)
    Case ".xls", ".xlsx"

        Dim oleDbConnStr As String
        Select Case fileExt
            Case ".xls"
                oleDbConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
            Case ".xlsx"
                oleDbConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
        End Select



        Using oleDbConn As OleDbConnection = New OleDbConnection(oleDbConnStr)
            oleDbConn.Open()

            Dim oleDbCmd As New OleDbCommand("SELECT * FROM [Sheet1$]", oleDbConn)
            Dim oleDbDa As New OleDbDataAdapter(oleDbCmd)
            oleDbDa.Fill(dt)

            oleDbConn.Close()
        End Using



End Select

I can't understand why the code cannot find my worksheet. Why is this, and how can I resolve it?

Newsmagazine

try once using absoulte path: Data Source=C:\\myexcel.xls; – Carmacarmack

@AshwiniVerma filepath is the absolute path because I use Server.MapPath() – Newsmagazine

visit this link and try getting sheetname by programming: forums.asp.net/t/1751143.aspx/1 – Carmacarmack

@Dante Yeah the file wasn't being stored in the right location. https://mcmap.net/q/922188/-the-microsoft-jet-database-engine-could-not-find-the-object-39-sheet1-39 I expected a different error message for this scenario though! Thanks for your help anyway. – Newsmagazine

Answer 1

I've found the problem.

It seems the spreadsheet was being saved to the wrong location, so filepath wasn't pointed to a file which exists.

I didn't check this at first because I assumed a different error message would appear. Something like "Book1.xls could not be found". However it seems like if it doesn't exist, then the message will just state that it cannot find the Worksheet.

Newsmagazine

Answer 2

If file name has additional dot character like below:

sample.data.csv

next select statement:

SELECT * FROM [sample.data.csv]

with connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Data\"; Extended Properties="text;HDR=Yes;Format=Delimited;";

will fail with exception:

Additional information: The Microsoft Jet database engine could not find the object 'sample.data.csv'.  Make sure the object exists and that you spell its name and the path name correctly.

Pectoralis

+1, why is this downvoted? this solved exactly my problem. Once I removed the dot from my filename it worked. – Maxima

Answer 3

Also - make sure you don't have the file open in Excel already. You won't be able to read the file if it's open somewhere else. I had the same error and realized I had the file open in Excel.

Fotinas

Answer 4

Not sure, I have some similar code (C#) that works well...

Maybe you can spot a difference?

string connectionString = string.Format(Thread.CurrentThread.CurrentCulture, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;'", excelFilePath);
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;
    using (DbCommand command = connection.CreateCommand())
    {
        command.CommandText = @"SELECT [File], [ItemName], [ItemDescription], [Photographer name], [Date], [Environment site] FROM [Metadata$]";
        connection.Open();
        using (DbDataReader dr = command.ExecuteReader())
        {
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    .......
                }
            }
        }
        connection.Close();
    }
}

Try renaming your sheet; or explicitly adding columns; or checking if it's case sensitive.

Dizen

Answer 5

Change your Excel file location, this error will be resolved. may put your file in the same folder where your source present

Footslog

Answer 6

best solution through vb coded from this link, all credits to these folks- http://www.vbforums.com/showthread.php?507099-data-from-excel-sheet-to-datagrid-(vb)

C# My expected solution below

string connString = "Driver={Microsoft Excel Driver (*.xls)};READONLY=FALSE;DriverId=790;Dbq=" + "C:\\Users\\BHARAVI\\Documents\\visual studio 2013\\Projects\\ERP\\ERPAutomation\\Assets\\Data\\Data.xls";

OdbcConnection conn = new OdbcConnection(connString);

conn.ConnectionTimeout = 500;
OdbcCommand CMD = new OdbcCommand("SELECT * FROM [Sheet1$]", conn);
OdbcDataAdapter myDataAdaptor = new OdbcDataAdapter(CMD);
DataSet ds = new DataSet();
myDataAdaptor.Fill(ds ,"Sheet1");
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
    loginId = dr["LoginId"].ToString();
    encryptedPassword = dr["PWD"].ToString();
    URL = dr["URL"].ToString();
}

Galenical