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

Question

I am copying the template excel file saved in the server folder in to the same folder with different name. to insert the value..I am able to copy the file but when I try to insert the values it shows sheet!$ could not found. I have given correct sheet name..Only one sheet is added in the spread sheet named as sheet1.still it shows error .My code is given below.Nay idea about this error .I googled but asked me to check the folder and sheet name..it is correct only..please help me

        string xxx = "~/temp/" + "Tempfile" + dunsno + DateTime.Today.ToString("dd.MM.yyyy") + ".xlsx";
         DirectoryInfo directoryInfo = new DirectoryInfo(Server.MapPath("~/temp/"));
                            var fileList = directoryInfo.GetFiles();
                            string newFileName = Server.MapPath("~/temp/" + "Tempfile" + dunsno + DateTime.Today.ToString("dd.MM.yyyy") + ".xlsx");
                            foreach (FileInfo fleInfo in fileList     

                       {
                                fleInfo.CopyTo(newFileName, true);
                       }
         string connStr = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xxx;Extended Properties=""Excel 12.0 Xml;HDR=YES""");
         OleDbConnection MyConnection;
                            OleDbCommand MyCommand = new OleDbCommand();
                            MyConnection = new OleDbConnection(@connStr);

                            MyConnection.Open();
                            MyCommand.Connection = MyConnection;
                            string sql = "Insert into [Sheet1$] (id,name) values('3','c')";
                            MyCommand.CommandText = sql;
                            MyCommand.ExecuteNonQuery();
                            MyConnection.Close();

Solution 1

Replace:

string connStr = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xxx;Extended Properties=""Excel 12.0 Xml;HDR=YES""");

with

string connStr = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""", newFileName);

Solution 2

In case if you are still getting error as follows "The Microsoft Access database engine could not find the object..." followed by the sheet name from where you are trying to read data then try [sheetname$].