Excel & Office in BCB6 personal 1

[biot023]

Hallo - I don't even know if the version of BCB I'm using supports M$ Office tools, but I was given the option of selecting between 97 & 2000 when I installed, so maybe it does.
Can anyone point me in the direction of some information on reading & editing Excel 97 files?
Thanks alot,
Douglas JL.

A salesman is a machine for turning coke into obnoxious arrogance.

Common sense is what tells you the world is flat.

 

[hansaplast]

It is possible to access Excel and Access data through the ODBC-Drivers.

Here's a quick howto to get your data on a Form

1) Create an ODBC Data Source Name (DSN) (see Control Panel)
2) Put a TTable Object on a Form
3) Set the TTable property DatabaseName to the DSN
4) Set the TTable property TableName to a table in Excel or Access
5) Set the property Active to true
6) Drop a DataSource on the from
7) Set the DataSet property to TTable1
8) Drop a DBGrid on the form
9) Set the DataSource property to DataSource1
10) Compile and run

Let me know if it works.
Regards,
Hansa

 

[onrdbandit]

Howdy,

Just a question on this topic:

Does Excel or Access have to be running at the time, or can you open any file with .xls or whatever for the Excel and Access?

Thanks for the info,
onrdbandit

If we are only animals, how do we decide what is right and what is wrong?

 

[hansaplast]

I Don't know exactly what you mean but you can edit the xls/mdb files from within your application aswell as in Access or Excel. If your application can edit contents in a table (i.e. writing to a table) it would be a good idea to lock the file for writing by other apps. You can do this in the ODBC connector's DSN option "Read only" or "Exclusive" if you don't want other apps to access the file at all.

Just check it out and play with it.
For completeness I wil explain step1 in more detail:

1a) Start->Run->odbcad32.exe
b) Select the "User DSN" tab and klik the Add button
c) Select a driver (I used "Microsoft Access Driver") and click Finish
d) Fill in the "Data Source Name" (it will be used in step3)
e) Click the "Select" button and browse for your .mdb file and clik OK
f) optionaly: click Options and select Exlusive or "Read Only" (for file locking)
g) click OK and your done

proceed with step2

Good luck

 

[biot023]

That's brilliant, cheers!
One thing - I need to be able to open loads of different Excel files - is there a way I can do this?
(Off the top of my head, maybe change each workbook's filename to the one I used setting up the excel driver, maybe?)
Thanks again,
DJL

A salesman is a machine for turning coke into obnoxious arrogance.

Common sense is what tells you the world is flat.

 

[hansaplast]

Thats a possibility, but a better way would be to let your users choose their own database file. Now ODBC's DSN becomes unusable because you want a dynamic databaseconnection.
Take into account that Access and Excel databases are file-databases when you need to change database connections often you might concider using a database server like oracle or MySQL. These databases are easier to manage and have more functionality than Access or Excel. Maybe its a good idea to tell me what kind of application you are trying to create. Perhaps I can give some advise on what steps to take.

Anyway since you are using file-db's it might be a good idea to connect the TTable object on your Form to a TDatabase object. With the TDatabase object you can set the ODBC driver and database (files in your case) dynamicaly. Relevant properties are: DatabaseName, DriverName and Params. You can also use the AliasName but then you commit yourself to using DSN's (you don't want that).
I can't help you with setting up the TDatabase object since I don't know which "Params" to use, but do a google -Groups-search with "TDatabase Params mdb" and you will probably find something helpfull.
To connect your TTable to the TDatabase just set the property Table1->DatabaseName = Database1->DatabaseName