DAO with Excel, QueryInterface failed...

[Tim_Mac]

hi,
i need to use DAO to read the correct order of worksheets in an excel
file. ADOX uses the original ordering and disregards any worksheet
shuffling that the user may have done. I need the order as it appears
to the user. If i don't need to use COM at all that would be ideal,
but i haven't been able to find viable alternatives. I don't like the
sound of automation because it is a reasonably critical server that
this code will run on, apparently server-side automation is dodgy from
a security and stability point of view.

here is the c# code i'm using, with a COM reference to the DAO 3.6
library.

DAO.DBEngineClass engine = new DAO.DBEngineClass();
DAO.Database db = engine.OpenDatabase(path, null, true, "Excel 8.0;");

The second line above causes the following exception:
"QueryInterface for interface DAO._DBEngine failed"

i found a possibly related post at
http://groups.google.com/group/micr...&q=QueryInterface+DAO&rnum=2#f5268bf372b08b66
but i tried james' solution to no avail.

can anyone suggest a solution? i'm really stuck here!
thanks in advance
tim

 

[Paul Clement]

¤ hi,
¤ i need to use DAO to read the correct order of worksheets in an excel
¤ file. ADOX uses the original ordering and disregards any worksheet
¤ shuffling that the user may have done. I need the order as it appears
¤ to the user. If i don't need to use COM at all that would be ideal,
¤ but i haven't been able to find viable alternatives. I don't like the
¤ sound of automation because it is a reasonably critical server that
¤ this code will run on, apparently server-side automation is dodgy from
¤ a security and stability point of view.
¤
¤ here is the c# code i'm using, with a COM reference to the DAO 3.6
¤ library.
¤
¤ DAO.DBEngineClass engine = new DAO.DBEngineClass();
¤ DAO.Database db = engine.OpenDatabase(path, null, true, "Excel 8.0;");
¤
¤ The second line above causes the following exception:
¤ "QueryInterface for interface DAO._DBEngine failed"

COM interop with C# isn't particularly fun but I think the following should work for you:

DAO._DBEngine accessDBEngine = new DAO.DBEngine();

DAO.Database excelWB = accessDBEngine.OpenDatabase("C:\\Test Files\\Book10.xls", false,
true, "Excel 8.0;");
foreach (DAO.TableDef td in excelWB.TableDefs)
{
Console.WriteLine(td.Name);
}
excelWB.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWB);
excelWB = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(accessDBEngine);
accessDBEngine = null;


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Tim_Mac]

hi Paul,
thanks for the reply. i tried out your code in a windows forms app and
it worked (i think the code is equivalent to the code i originally
posted, just in case anyone else is wondering). but the code wouldn't
run in an ASP.NET web application on IIS 5 without throwing the
System.InvalidCastException: QueryInterface for interface DAO._DBEngine
failed.
but... i tried it out on a server 2003 box, and it runs fine, as long
as the web application identity account has read permissions on the
file. not sure exactly what doesn't work with IIS 5 but it works on
2003 anyway so i'm happy to relegate this problem to the history books

thanks for your help
tim