ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object.

[VA_er]

What is wrong with this part of code? I have only one sheet in the workbook, in this case, it is xlWorksheet

Thanks.




var filePath = string.Empty;
OpenFileDialog OpenFile = new OpenFileDialog();

OpenFile.Filter = "Excel Files|*.xl*"; //Filter for excel file
OpenFile.Title = "Select your test case file";
OpenFile.FilterIndex = 2; //Don't know what it mean
OpenFile.RestoreDirectory = true;

if (OpenFile.ShowDialog() == DialogResult.OK)
{
//Get the path of specified file
filePath = OpenFile.FileName;
}

//Read excel data into DataTable
//Create COM Objects. Create a COM object for everything that is referenced

Excel.Application xlApp = new Excel.Application();
//filePath should already be open in the above, correct? It seems Open is not correct here?
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
//The excel file has only one sheet, with header, want to import Column A-G into database
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];


SqlBulkCopy oSqlBulk = null;

// SET A CONNECTION WITH THE EXCEL FILE.
OdbcConnection myExcelConn = new OdbcConnection("Driver={Microsoft Excel Driver (*.xls)}; DBQ=" + filePath +"; DriverID=790");


try
{
myExcelConn.Open();

// GET DATA FROM EXCEL SHEET.
OdbcCommand ObjCmd =
new OdbcCommand("SELECT * FROM " + xlWorksheet, myExcelConn);

// READ THE DATA EXTRACTED FROM THE EXCEL FILE.
OdbcDataReader objBulkReader = null;
objBulkReader = ObjCmd.ExecuteReader();



xlWorkbook.Close(false);
xlApp.Quit();

Continue reading...