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
Dynamic output cell (Read 2703 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Dynamic output cell
26.03.10 at 11:41:43
Dynamic output cell
I'm interested in your SQL XL 4-product, in specific, the SELECT TO syntax. As I can read from your site it is possible to put the result in a specific cell.

Would it also be possible to make the output-cell "dynamic"? e.g. SELECT nr, complex_function(nr) target FROM employees TO target, so that the target-cell is calculated from a select-statement.

Thank you for your interest in the SQL*XL product.
Yes, the target can be made flexible.
The way you seem to want to use it is by letting the database decide where the data should go:  select * from table to my_function(p)
This is not possible (currently) as the target is not actually submitted to the database. It stays local in Excel.
Hardcoded you can do this:
select * from mytable1 to A1;
select * from mytable2 to Z1;
You can also define a name. If you name A1 mytarget1 and Z1 mytarget2 then you could equally well use:
select * from mytable1 to mytarget1;
select * from mytable2 to mytarget2;
If you reset the target to the correct cell then you you can use:
select * from mytable1 to mytarget;
select * from mytable2 to mytarget;
An elegant way to go about would be to record a macro fetching the data and then to create a parameter to the macro to take the SQL. Your code may look something like this:
ActiveWorkbook.Names.Add Name:="mytarget", RefersTo:="A1"
my_query_macro("select * from mytable1 to mytarget")
ActiveWorkbook.Names.Add Name:="mytarget", RefersTo:="Z1"
my_query_macro("select * from mytable2 to mytarget")
But when you are recording macros anyways it is also easy to just set the StartInCell property of the Target object (I am leaving out less relevant statements for clarity. Record a macro to get alls statements):
SQLXL.SQL.setText "select * from mytable1"
SQLXL.SQL.Statements(1).Target.StartFromCell = "A1"
SQLXL.SQL.setText "select * from mytable2"
SQLXL.SQL.Statements(1).Target.StartFromCell = "Z1"
I suggest you to download our fully functional evaluation version. It gives you 10 days full access to the software.
When experimenting with macros it is always best to record the macros and to tailor the generated code. If nothing is generated when recording a macro, please switch on the macro recording support in SQL*XL's general options.
Back to top

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