I can import data from Excel using the following code if the spreadsheet is saved on the computer and has a non-empty path name:
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + oXL.ActiveWorkbook.Path + "\\" + oXL.ActiveWorkbook.Name + ";" + "Extended Properties=Excel 8.0";
OleDbCommand cmdSelect = new OleDbCommand("SELECT * FROM [" + oSheet.Name + "$]", conn);
However, if the running instance of Excel is not saved on the computer and has an empty path name, I need to change the connection line to:
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + oXL.ActiveWorkbook.Name + ".xls;" + "Extended Properties=Excel 8.0";
After, when I try to execute the command, I get an error:
"The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and path name correctly."
My guess, it cannot find Sheet1 of the current instance of Excel because there is no path name. Any thoughts?