Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Keep master copy of Excel file in the database (Read 3721 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Keep master copy of Excel file in the database
24.02.06 at 13:25:12
Mukesh from India asked the following question:
I have installed and activated the program, i gone through the program. Its really wonderfull.
Our main requirement is like existing Excel file data would be stored into the database automatically and whenever we open the Excel file and connect with the database application will get the latest data from the database. Purpose of this type of requirement is that if we loose the excel file then data would be remains there in the database.
Again, I would like to say Application is working fine.
Thank you for the kind words about SQL*XL.
You can achieve what you require by making a database table for the spreadsheet. The database is more strict in the use of data types etc than the spreadsheet. That is why you need to start at the database level. Ensure that each column allows for the data you need. You may or may not use column names. You could also create it like this:  
create table book1 (a varchar2(255), b number, c date);
Note that you can run the create statement through SQL*XL if you like.
Now retrieve the data from your table. With the example table above I would use:  
select a,b,c from book1 to A1;
Simply refreshing this query will get the data into the spreadsheet each time.
If you want to automate this you can record the connecting to the database and the fetching from the database in a macro.
Important for the updates to work is that you do not disconnect from your database. Changed data is sent back to the database using the multiple rows update feature. New data can be bulk loaded using the multiple rows insert feature. You need to keep the new rows separate from the existing rows in which you may make changes.
In this process it is important that you do not order the spreadsheet or add/remove rows and columns. Otherwise the update does not work.
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1