1

The message says

The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.

The name of the sheet in the Worksheet is "Sheet1"

        string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";", fileName);
        string query = String.Format("SELECT [columnName1],[columnName2],[columnName3] from [{0}]", "Sheet1$");
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);
        DataTable YourTable = dataSet.Tables[0];
        listBox1.DataSource = YourTable.Columns["ColumnName1"];
Cocoa Dev
  • 9,361
  • 31
  • 109
  • 177
  • 1
    It would probably help if you posted some code. – Geoff Apr 19 '13 at 14:54
  • 1
    Assuming a Sheet1$ exists in your Excel file then there is something wrong in your connection string – Steve Apr 19 '13 at 14:58
  • The error is trying to tell you that there is no "Sheet1$" in the workbook. – RBarryYoung Apr 19 '13 at 15:44
  • Yes. The error occurs at dataAdapter.Fill(dataSet) – Cocoa Dev Apr 19 '13 at 15:52
  • Use excel 8 not 12 `Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";", fileName);` – user2140261 Apr 19 '13 at 15:58
  • ACtually on second look youhave a bad connectino string to many `"` Try this `String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 8.0;HDR=YES";", fileName);` – user2140261 Apr 19 '13 at 16:00
  • I got the string from ConnectionStrings.com – Cocoa Dev Apr 19 '13 at 16:09
  • @user2140261 you should type your answer in the answer section. But I get the same message when substituting your string with my string. It's referencing Sheet1$ – Cocoa Dev Apr 19 '13 at 16:12
  • all connection strings on http://www.connectionstrings.com/excel seem to agree with me. Posted an answer with code that worked for me. – user2140261 Apr 19 '13 at 16:21

2 Answers2

5

This works for me: 

string filename = @"C:\Book1.xlsm";

        string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";", filename);
        string query = String.Format("SELECT * from [{0}$]", "Sheet1");
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);
        DataTable YourTable = dataSet.Tables[0];

*NOTE: * If your data does not have headers to make HDR=NO

Also noticed that in your question you used 

[columnName1],[columnName2],[columnName3]

for your columns to select. Please remember these should be the value(s) of the first cell in the column(s) that you would like to grab.

To get column E Use: 

        string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO\";", filename);
        string query = String.Format("SELECT [F5] from [{0}$]", "Sheet1");

Replace 5 With any other column number you need so 

F1 = A
F2 = B
F3 = C

and so on.

The Error you are getting could be because you have the file open and active.

OR you are pointing at the wrong file (Remember you have to include full file path in the filename string. and make sure the sheet is correct. Alos take notice to the fact the i include the $ in my string not in my parameter so rememer to only put just the name of the sheet you are trying to get. If you are still having trouble supply me with the FULL file name for the worksheet you are using i.e. C:\Book1.xlsm and the sheet you are trying to get data from.

user2140261
  • 7,855
  • 7
  • 32
  • 45
  • I need Column E (no header) But I'm still struggling to open the file. Your code is now throwing another exception. saying "Cannot update. Database or object is read-only." – Cocoa Dev Apr 19 '13 at 16:32
  • Edited my answer. IN order to try and fulfill your needs. – user2140261 Apr 19 '13 at 16:41
  • The file is definitely not open or active. Ive closed all instances of Excel and I've modified the Connection String to say READONLY=False – Cocoa Dev Apr 19 '13 at 16:46
  • The problem is a new "question" since people don't like to have all problems in 1 question. The link is http://stackoverflow.com/questions/16109609/oledbexception-being-thrown-at-adp-fill-saying-could-not-find-installable-isam – Cocoa Dev Apr 19 '13 at 16:47
  • That link is no longer applicable to this issue. – DaveN59 Mar 14 '18 at 22:12
-1

Please put square brackets around Sheet1. So your query is: select * from [Sheet1$]

Ash
  • 1