Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How to access an excel spreadsheet through Delphi 5

Posted on 2011-09-28
Medium Priority
Last Modified: 2012-05-12
My problem seems to be simple.  I need to open up an odbc driver linked to an excel spreadsheet in Delphi and then read some data from it.  Seems simple, but I can't seem to find the best way.  

First of all I set up a odbc datasource for the spreadsheet.

I then setup a tdatabase component and connected it without a hitch.  I then drop a ttable on the form and reference the tdatabase but when I try to select the tablename the system crashes.  

Using ADO I dropped a tadoconnection on the form, setup the connection string, tested the connection and set the connected to true, no problems.  I then drop a tadotable and referrence the tadoconnection.  There is nothing in the drop down for tablename and when I try to set active = true, it tells me the tablename is missing.

Question by:Randy Rich
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 23

Expert Comment

by:Ferruccio Accalai
ID: 36720470
It's a little bit long to explain how to connect to excel using ado
Here's a tutorial to start in this way http://delphi.about.com/od/database/l/aa090903a.htm
LVL 19

Expert Comment

ID: 36813047
Have you already checked this...

Connecting an Excel Spreadsheet to Delphi using ADO
LVL 19

Expert Comment

ID: 36813056
Another useful link...

Creating a Microsoft Excel Spreadsheet Viewer Using Delphi 5's ADOExpress
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 25

Expert Comment

ID: 36813246
you may have to setup in excel a region of your spreadsheet that will be the table. that is useful when you have complex speadsheet and want only a specific area of it to be accessible as a table.
I think that is done by selecting the region (with the first line being the column headers), and setting a name to that region. In ribbons GUI, it is in the 'Formula' ribbon, 'Name Manager' section, then 'Define a name'
I know Excel should allow access to each spreadsheet as a whole table, without setting an area name. But maybe there is now security functionality that prevent it to work.
I'll try to test it with an environment as close as possible as yours : what is your Delphi version and what is your Office version ?
LVL 25

Accepted Solution

epasquier earned 2000 total points
ID: 36813843
Here is a full sample project, with comments inside the app and the excel file.
Accessing Excel from ADO, while convenient with simple needs, is definitely NO DB alternative, when adding data and joints are required ADOExcel.zip

Author Comment

by:Randy Rich
ID: 36814046
I'm using Delphi 5 and Excel 2010.

Author Closing Comment

by:Randy Rich
ID: 36814339
Wish I could give an A++++.  This was a great solution and the example made it easy to work through!  I know it's not always possible to go to this length to explain something but in this case with all the many variables, it was a tremendous help. Thank you!!!!

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question