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
output issues, formulae in sql statement (Read 3862 times)
YaBB Newbies


Posts: 2
output issues, formulae in sql statement
14.04.08 at 20:46:55
At work our department has Excel 2003 and SQL*XL 4.0.21
After upgrading to Office 2003 we see a couple of problems on which we would like feedback.  Our Excel data templates have formulae in multiple columns to the right of the output that are deleted upon completion of the query.  Also, once we connect to a database the escape key will not cancel the Copy function in Excel like it does when not connected to a database.  Any help will be appreciated.
Back to top
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: output issues, formulae in sql statement
Reply #1 - 17.04.08 at 21:50:35
You also supplied a file with an example.
Cells B5 to I5 (one row) contain the SQL in each row with the result displayed (one row one column) in the cell itself.  
The row below contains a set of formulas. The complaint is that the formulas are overwritten upon query refresh.
You should be able to do this by forcing no feeback output (set feedback off).
However the use of SQLQueyValue is much more appropriate in your case. This function binds the results of the SQL provided to the output cells. In the most simpel application you use the function in one cell. This is how you can use it. If you have more than one column or row as output you can still use this technique but you should use the function as an array function. There is some information about this on this forum as well. Just search for the SQLQueryValue function.
In your cell type the following formula:
=SQLQueryValue("select sum(mycol) from mytable where mycol2=1234")
Back to top

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