Read from Excel with OLEDB

lape

Is it possible to Create an OLEDB Connection to an Excel Sheet and Read the Tables from the SchemaTable via DotNet Datatype?
I tryed the following:

ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\test.xls;Extended Properties="Excel 8.0;HDR=YES";';
OLEConnect := OLEConnect.OleDbConnection(ConnectionString);
OLEConnect.Open();

...
in C# i can do it like this:

DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 	new object[] {null, null, null, "TABLE"});

but in Dynamics the method table not avalible
...

OLEConnect.Close();

David_Singleton

HI Lape,

that's a pretty cool idea. I don't know how to solve it, but I hope you solve it, as I would like to try this also. I can see quite a few uses for this. Especially for automation projects where Excel is not installed.

Also a question. Do you think this would be faster than using excel automations? I have a lot of cases where clients have huge spread sheets that take quite some time to import or export.

toenne

Hi,
you can alternatively use following code to retrieve the sheet/tablenames:

lSheetName := 'Tabelle1$';
lSchemaTable := lOleDbConnection.GetSchema('Tables');
IF lSchemaTable.Rows.Count > 0 THEN BEGIN
  lDataRow := lSchemaTable.Rows.Item(0);
  lSheetName := lDataRow.Item(2);
END;                                                                     

Marije_Brummel

You mean like this?

http://vjeko.com/blog/excel-interoperab ... n-nav-2013

toenne

hi,

this was meant as alternative for the missing to do something like

DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,    new object[] {null, null, null, "TABLE"}); 

in c/al, if you don't know the names of the included worksheets.
the example retrieves the name of the 1st sheet.