mkmko
asked on
Access Excel 2013 from Delphi 7 via ODBC/ADO
For an quite old project, I need to read data from a Excel 2013 file, which could have one or more worksheets. Using SQL commands, I need to enumerate all worksheet names within the file and then selecting the records within one of those worksheets.
It is important that this will work with Delphi 7.
Any hints or docs are highly appreciated.
TIA
Michael
It is important that this will work with Delphi 7.
Any hints or docs are highly appreciated.
TIA
Michael
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If yoy don´t know how to access excel directly to deal with files and columns in a sheet, you can transform (using OLE inside delphi) a sheet and saving it (converting it) to text file delimited by commas. In that way you can work with a simple text file.
regards
Lopem
regards
Lopem
ASKER
Sorry for being late - I wasn't able to solve this problem. I guess it's caused by incompatibilities between old Delphi and new Excel.
At least I decided to purchase Delphi 10 Seattle, and now I'm looking for solutions using this version. Still looking for a way to get (Excel-) table access by ODBC, for accessing using SELECT SQL commands.
Is there any good information available for my current environment?
Thanks again (and sorry for not being able to solve the in the first try)
Michael
At least I decided to purchase Delphi 10 Seattle, and now I'm looking for solutions using this version. Still looking for a way to get (Excel-) table access by ODBC, for accessing using SELECT SQL commands.
Is there any good information available for my current environment?
Thanks again (and sorry for not being able to solve the in the first try)
Michael
ASKER
Ok, now I'm able to explain a little bit better what I'm trying to accomplish:
I have Excel2013 on a W2K8 server somewhere in the intranet and I have a Citrix session to run an application (written in Delphi 10 Seattle), accessing the Excel workbook via SQL/ADO.
On the Citrix server, Excel is installed, and so the (Jet?) drivers are.
I need to enumerate all Worksheets contained within one workbook, and then I need to access the worksheet data in a simple way, that is, every row is a data record and every column is a field within that record.
I'd like to do this all using plain SQL. Possible?
TIA,
Michael
I have Excel2013 on a W2K8 server somewhere in the intranet and I have a Citrix session to run an application (written in Delphi 10 Seattle), accessing the Excel workbook via SQL/ADO.
On the Citrix server, Excel is installed, and so the (Jet?) drivers are.
I need to enumerate all Worksheets contained within one workbook, and then I need to access the worksheet data in a simple way, that is, every row is a data record and every column is a field within that record.
I'd like to do this all using plain SQL. Possible?
TIA,
Michael
Is there any good information available for my current environment?
You have been given a link that leads to a good article. What did you try so far, and what result did you get?
You have been given a link that leads to a good article. What did you try so far, and what result did you get?
ASKER
@jimyX: I tried to use the code parts along with the connection string provided, but I couldn't get it working with Excel 2013, even if I changed the connection string to something else (i.e. 'Extended Properties=Excel 12.0' or '...=14.0' or the like). And because I've to try and run it on a Citrix server (where also MS Office is installed), it's very hard to find the cause - Citrix always cuts my entire session if the application can't connect and throws an exception. In other words, I need too much time for too less tries.
Installing Office on my development PC isn't an option, because we don't have local licenses available. The licenses are available on a per-user base within our server environment.
To save test and development time, our management at least purchased Delphi 10 Seattle, which doesn't need do go legacy ways of programming - i hope :)
OTOH - if only my connection string could be updated to work with Excel2013, it should also be usable with D10. But I couldn't get it working so far....
TIA
Michael
Installing Office on my development PC isn't an option, because we don't have local licenses available. The licenses are available on a per-user base within our server environment.
To save test and development time, our management at least purchased Delphi 10 Seattle, which doesn't need do go legacy ways of programming - i hope :)
OTOH - if only my connection string could be updated to work with Excel2013, it should also be usable with D10. But I couldn't get it working so far....
TIA
Michael
ASKER
Finally I got it working - it had to do with the connection string. I hope that splitting my points is ok. Thanks to you both.
Michael
Michael
Should have refreshed before posting.