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
Change in Worksheet does not work (Read 7784 times)
hezi
YaBB Newbies
*




Posts: 3
Change in Worksheet does not work
20.10.05 at 08:22:50
 
I have already created a sheet and try to add a new worksheet. Now when i'm entering the sql statement and run the sql
it works. But when i start rerun queries i got the message "wrong cell". I change it in the sql editior like:
 
[MyExcelSheet.xls]Sheet6!A3
 
but this doesn't work. Is there any hint how i can make this happen? What is the right procedure to change an existing
Sheet with adding or removing?
Back to top
 
« Last Edit: 31.10.05 at 14:22:12 by Gerrit-Jan Linker »  
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Change in Work sheet doesn't work
Reply #1 - 20.10.05 at 09:01:45
 
What happens when you rerun the query is that SQL*XL picks the SQL up from the comments it has created in previous runs. To know where to put the results for each query it will add a ´to clause´ to each query. If you edit the comment cell to remove the ´to clause´ from your query, SQL*XL should put the correct ´to clause´ on the SQL when you rerun it with the refresh queries button. If this is not the case I would like to know about it.  
 
For your information, this is the help page about the to clause:  
http://www.oraxcel.com/projects/sqlxl/help/commands/selectto.html
 
What could happen is that you have renamed the sheet or the workbook. In that case you have a point. Perhaps I should consider to remove the ´to clause´ from the queries when you use the refresh queries option and to immediately replace them with the correct ones. I will have a look at this. Thanks for pointing this out to me.  
To summarise, I think the requery may go wrong when you save the workbook with a different name, when you rename the worksheet and when you insert or remove rows or columns that causes the cell addresses to change.
 
Back to top
 
 

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




Posts: 3
Re: Change in Work sheet doesn't work
Reply #2 - 20.10.05 at 11:05:54
 
It's not obviuos and easy to do that, but it runs now. It should be possible to add the complete sql statements in a txt file where you can edit and add/remove statements.
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Change in Work sheet doesn't work
Reply #3 - 20.10.05 at 13:04:37
 
I will have a look at this this afternoon. I agree I could improve SQL*XL a little here.
 
The ability to put SQL in a file is already present. The easiest is if you save a SQL statement as a favourite. See:
http://www.oraxcel.com/projects/sqlxl/help/menu%20items/favorites.html
 
The SQL Favorites feature lets you save the SQL in a file and organise them in a directory structure. If you have .sql files (plain text files) you can just put them into these directories to make them available in the SQL Favorites menu.
 
To execute any SQL file or text file containing SQL you can use the following commands that you can simply execute from the SQL dialog:
http://www.oraxcel.com/projects/sqlxl/help/commands/start.html
 
Examples:
start c:\sql\myfile.sql;
@c:\sql\myfile.sql;
 
A file can contain as many statements as you like. Just like you can execute multiple statements in the SQL dialog you should end each statement with a semi colon. Example:
 
select * from emp;
select * from dept;
 
For more information and examples about executing multiple SQL statements at once please see:
http://www.oraxcel.com/projects/sqlxl/help/techniques/multisql.htm
Back to top
 
« Last Edit: 20.10.05 at 13:05:45 by Gerrit-Jan Linker »  

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




Posts: 75
Re: Change in Work sheet doesn't work
Reply #4 - 20.10.05 at 15:24:27
 
I have fixed the issue with the to clause now. In version 4.0.25 I have corrected this problem. It will be released soon, in a couple of weeks possibly.  
 
I have changed the code to remove any existing to clause from the SQL when you are refreshing the query. After I have taken the old to clause off I will add the new current address to the to clause. That way it will always work correctly. I don't want to automatically strip off the to clause from the SQL comments. I know some people use the comments to store their SQL queries dumping the output elsewhere.
Back to top
 
 

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