Linker IT Software
Google
Web www.oraxcel.com
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
Embed a query in a cell (Read 4823 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Embed a query in a cell
21.12.05 at 10:05:04
 
Ike from the Phillipines asked the following question:
 
Question:
Can I execute different queries in each cell. I want to embed a query in a particular cell using some sort of an excel formula. Is this possible?
 
Answer:
Yes! SQL*XL has a function called SQLQueryValue. Example
 
=sqlxl.xla!SQLQueryValue("select count(*) from emp")
 
This will return the value of the count in the Excel cell. Note that if you want to use a value in another cell in the query that you can use the concatenate function. Example:
 
=sqlxl.xla!SQLQueryValue( concatenate( "select count(*) from emp where empno = ",D12) )
 
The execution of this query is the same as for other Excel formulas. Only when D12 is changed the formula is run. If you want to always run the query you need to use the =sqlxl.xla!SQLQueryValueVolatile function. This function is exactly the same as the SQLQueryValue function but it is different in the sense that whenever Excel calculates cells that it will also run the formula in this cell.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Embed a query in a cell
Reply #1 - 06.02.06 at 13:14:41
 
This topic is about how to keep a cell on the spreadsheet bound to a value in the database. In other words, the master copy of the data is in the database.
 
You can also try to achieve the opposite. You could have a database that records the current values in your spreadsheet. Using the SQLExecute cell formula you can keep the database in synch with values in the sheet. See:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1139227538  
 
You could implement spreadsheet auditing in this way where all changed values are inserted into a table with a timestamp...
Back to top
 
 

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