0

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?

shariful
  • 455
  • 1
  • 9
  • 21
  • 1
    Two things: 1) What problem are you getting that's preventing you connecting to the Excel file you want? 2) Do you really need to use FireDAC + ODBC - connecting to an Excel file using the TAdoConnection and the MS Jet 4.0 OLE DB driver works fine. – MartynA Aug 26 '19 at 07:39
  • I am getting an exception on opening the FDConnection if I select xlsx. Using FireDAC I am able to use SQL query whereas with TADOConnection can't. – shariful Aug 26 '19 at 08:33
  • What SQL did you use with your TADOConnection? What does the exception you get with TFDConnection actually say? If you want help on SO, you should provide info like this without having to be asked. – MartynA Aug 26 '19 at 09:10
  • I know about both FD and ADO, thanks. Ime, there are problems with FD + Excel that don't arise with ADO, that's why I asked. Anyway, I'm done with this q. I suggest you provide a proper [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) rather than screenshots and incomplete details. – MartynA Aug 26 '19 at 14:29

1 Answers1

1

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

roumen
  • 563
  • 6
  • 15
  • Are you using Win10 64-bit and, if so, which version? – MartynA Aug 26 '19 at 17:44
  • Windows 10 Pro 64-bit Version 1803 – roumen Aug 26 '19 at 17:50
  • I did all the things which you mentioned. For using ADO, ODBC DSN required which I am trying to avoid because my client doesn't know anything about ODBC DSN. I have to provide the client with a simple solution. He opens an excel file and my app will generate data for him. I did successfully with the xls file without any exception with the same code which I mentioned. TFDPhysODBCDriverLink does not have xlsx driver which only has Microsoft Excel Driver (*.xls). – shariful Aug 26 '19 at 18:26