locked
The Microsoft Access database engine could not find the object 'Sheet1$'. Error RRS feed

  • Question

  • User-1842880510 posted

    I have a test web app.  I am testing OLEdB connections under various trust levels.   I built an app to test an OLEDB connection using the Microsoft.ACE.OLEDB.12.0 provider.  I built the app on my development machine which has Office 2010 installed on it.  I also built an excel file on that machine directly under the application root.  I ran the web app in debug everything worked fine.  I published the app and deployed it to the test web server directly under wwwroot.  Since the web server does not have Office Installed on it I ran AccessDatabaseEngine_x64.exe on that machine.  Then I ran the app.  The App opens but when I push the button to run the code the read the file I get "The Microsoft Access database engine could not find the object 'Sheet1$'."  This puzzling because the on sheet in the work book is Sheet1.  Not to mention it worked just fine on the developer machine.

    Here is the Page_Load code.

         if (IsPostBack)
                {
                    String cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  Server.MapPath("~/TestWebExcel.xlsx")  + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
     
                    OleDbConnection cn = new OleDbConnection(cs);
                    cn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandText = "Select PartNumber FROM [Sheet1$]";
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Connection = cn;
                    OleDbDataReader dr = cmd.ExecuteReader();
                    this.ListBox1.DataSource = dr;
                    this.ListBox1.DataTextField = "PartNumber";
                    this.ListBox1.DataValueField = "PartNumber";
                    this.ListBox1.DataBind();
    
    
                }

    Here is the location of the Excel File C:\inetpub\wwwroot\TestWebAppExcelRedo\TestWebExcel.xlsx .
    I rewrote the app to merely display the connection string being used.  Like this:

                if (IsPostBack)
                {
                   String cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +  Server.MapPath("~/TestWebExcel.xlsx")  + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
     
                    OleDbConnection cn = new OleDbConnection(cs);
                    cn.Open();
    
                    this.Label2.Text = cs; 
    
                }

    The result of this site shows the connection string as being
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\TestWebAppExcelRedo\TestWebExcel.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"

    This is the case even when Trust Level is set to Full, It is also the case when Trust Level is set to custom trust Level with OLEDB permission added.

    But when the Web app project is moved to the server without publishing it, then this error does not occur. 

    What's going on here?

    Wednesday, July 23, 2014 11:44 AM

Answers

  • User-1842880510 posted

    OK.  I am an idiot!!!  I am leaving this post here in case someone else is having the same issue (and I did see that error message entered all over the internet.)

    As one person stated, the issue is with the error message from command.  This message is the same for not only when the sheet is missing but when the file is missing as well.

    I did the publish on the project, the file was marked as not included in project.  even though I added the file to the published package, as far as the assembly knew there was no such file in the project. So it did not see it.  I went back and copied the unpublished project folder to the web server and everything worked as designed.  Then I looked at the original project and realized that the file was not included in the project, so I included it and then published.  That made all of the difference in the world.

    So if others are having this issue, see if the file is included in the project.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 23, 2014 12:00 PM