CODEWITHSUNDEEP
exceldelphifiredac
shariful
shariful

Reputation: 505

Delphi FireDac open excel xlsx file

I am trying to open Excel(xlsx) file using FireDAC Delphi Rio 10.3.2. I did the following code:

FDConnection.Params.Clear;
FDConnection.Params.Add('Database=<myfile>');
FDConnection.Params.Add('ODBCDriver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}');
FDConnection.Params.Add('DriverID=ODBC');
FDConnection.Params.Add('ODBCAdvanced=HDR=No');
FDConnection.Open;

I noticed Delphi Rio FireDAC has up to Excel 97-2003 enter image description here

This is the error I am getting with FDConnection for xlsx file: enter image description here

Can anyone please guide how to achieve Excel (xlsx) file open with FireDAC?

Upvotes: 0

Views: 5620

Answers (1)

roumen
roumen

Reputation: 563

It is easier to use COM Access to Excel as per the following:SO:ADO access to Excel, but if you insist to use FireDAC, the following steps are necessary (checked for Delphi 10.2.3 Tokyo and MS Office 2016, have not tried to dig deeper why FireDAC does not perform these actions):

  1. Define Name for the cells that contain the data to be accessed (with the header) in Formulas->Define Name
  2. Excel must be running and the workbook should be opened (you can use COM to start Excel and make sure that the workbook is opened).
  3. If the workbook was changed by inserting/modifying/deleting data, the workbook has to be saved.

If any of the above is not done, you will see the very meaningful message: External table is not in the expected format.

Because you need COM access to Excel in order to make sure the workbook is open in Excel, I would suggest to use the method 2) from:Excel sheet to Grid

Upvotes: 1

Related Questions