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
Refresh SQLQueryValue "No Queires found..." (Read 3174 times)
jdove78
YaBB Newbies
*


I Love SQL*XL

Posts: 4
Refresh SQLQueryValue "No Queires found..."
16.05.08 at 16:59:26
 
Hi all,
I'm a newbie with SQL*XL and I'm running into a problem.
 
I have created multiple queries within an XLS that have executed correctly (example below) however, when I open the XLS for the first time, connect successfully to the db (via odbc), and select the refresh button from the toolbar, I get the following message: "No queries found to refresh in the current workbook."  The cells display the SQLQueryValue string and not the data.  What am I doing wrong?
 
If I select the cell and go into it (either by clicking or hitting F2) then hit return the cell updates properly.
 
=SQLQueryValue(CONCATENATE("SELECT Sum(MY_TABLE.MATERIAL_EXP_ACT) AS MATERIAL_EXP_ACT  
FROM MY_TABLE  
WHERE MY_TABLE.YEAR=",E$2, " AND MY_TABLE.MONTH_NUM=",E$14, " AND MY_TABLE.SITE_NAME='",$B42,"'"))
 
Any help is greatly appreciated.  Thanks.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Refresh SQLQueryValue "No Queires found..."
Reply #1 - 17.05.08 at 08:18:04
 
The refresh method works takes SQL back into the SQL editor that you have executed through the SQL dialog before. You use the SQLQueryValue method which is refreshed from the insert functions menu.
 
There are 2 ways you can execute SQL.  
 
1) SQLQueryValue worksheet function.
One method to execute a select statement is by using the SQLQueryValue worksheet function. This is the method you use. The benefit of using the SQLQUeryValue method is that you bind one or more Excel cells to the data retrieved by the query.
To refresh the values, use the menu item in SQL*XL:  
insert function - calculation
 
2) Through the SQL dialog.
This is the traditional way to run SQL in SQL*XL. SQL is typed in in the dialog (or is taken from worksheet cells). After the SQL is executed by the database the results are put in the worksheet by SQL*XL by just copying all the values into the worksheet. It leaves the SQL behind in a comment in the upper left cell of the results block.  
When you press the refresh button SQL*XL looks for these comment texts, retrieves all the SQL back into the SQL editor and presents it to you for re-execution.
 
See also:
Refresh queries:
http://www.oraxcel.com/projects/sqlxl/help/menu%20items/refresh_queries.html
SQL dialog:
http://www.oraxcel.com/projects/sqlxl/help/dialogs/sql/index.html
SQLQueryValue worksheet functions:
http://www.oraxcel.com/projects/sqlxl/help/functions/sqlqueryvalue.html
Calculate SQL*XL functions:
http://www.oraxcel.com/projects/sqlxl/help/menu%20items/calculate_sqlxl_function s.html
Back to top
 
« Last Edit: 17.05.08 at 08:18:49 by Gerrit-Jan Linker »  

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


I Love SQL*XL

Posts: 4
Re: Refresh SQLQueryValue "No Queires found..."
Reply #2 - 22.05.08 at 22:37:50
 
Thanks for your response.  I like your product.. it certainly beats the heck out of messing around with MSQuery and creating Macros.
Back to top
 
 
  IP Logged
Pages: 1