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
Re: Running multiple selects in different cells (Read 10218 times)
martin
YaBB Newbies
*




Posts: 5
Re: Running multiple selects in different cells
25.10.05 at 16:56:48
 
As mentioned above, due to the formula getting too large I have had to use the "to (cell reference)" method.  
I cannot split this into smaller sections as it is sql statements. This works ok.
As mentioned above though it would be nice to have to option to select the specific cell that the count in this case is written to( i.e. "to (cell reference)Sadcell reference)".  
This would allow a cell to be refreshed on it's own. Currently this will wipe the cells around it.
Only way I have found to get round this is to refresh all below the cell wanted as well.
Back to top
 
 
  IP Logged
martin
YaBB Newbies
*




Posts: 5
Running multiple selects in different cells
Reply #1 - 18.10.05 at 18:18:03
 
I have pulled in list of code into a grid and can run individual cells against the JDE system with the code in the cell, replacing the code with a count.
What happens when I try to do this all in one go by highlighting the whole grid is that I get a empty cell introduced into the spreadsheet under the count.
It seems that the calculated next cell from the start cell skips a row each time. I have set the skip rows to 0 in preferences but this change it to missing 1 row rather than 2. I can change the start point maunally after each query but will be trying to automate this so this so is not ideal solution.
It also seems to just go down the same column if I highlight across more than one column.
Is there anyway around this to get all the cells to replace the result into they cell that the query was originally in? I have been trying each of the options without much success.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Running multiple selects in different cells
Reply #2 - 19.10.05 at 09:55:22
 
If I understand you correctly you have a couple of select statements that return a single value. You have typed these statements in a block of cells and expect the sql to be replaced by the results. Example
 
A1: select count(*) from emp
A2: select count(*) from dept
B1: select count(*) from salgrade
B2: select count(*) from mytest
 
The preferred way to do this is through the SQLQueryValue function.
Put the following cell formulas in the cells:
A1: =sqlxl.xla!SQLQueryValue("select count(*) from emp")
A2: =sqlxl.xla!SQLQueryValue("select count(*) from dept")
B1: =sqlxl.xla!SQLQueryValue("select count(*) from salgrade")
B2: =sqlxl.xla!SQLQueryValue("select count(*) from mytest")
 
The alternative is to specify the target cell for the output in the To clause of the select statements. This will remove the dependency of the skip rows preference. Use the following statements in the cells:
 
A1: select count(*) from emp to A1
A2: select count(*) from dept to A2
B1: select count(*) from salgrade to B1
B2: select count(*) from mytest to B2
 
If you do not wish to hard code the target cells you can use the following formulas:
A1: =concatenate("select count(*) from emp to ",ADDRESS(ROW(A1),COLUMN(A1),4))
A2: =concatenate("select count(*) from dept to ",ADDRESS(ROW(A2),COLUMN(A2),4))
B1: =concatenate("select count(*) from salgrade to  ",ADDRESS(ROW(B1),COLUMN(B1),4))
B2: =concatenate("select count(*) from mytest to  ",ADDRESS(ROW(B2),COLUMN(B2),4))
 
Now when you copy the cells or insert rows & columns the to field addresses will be fixed automatically
Back to top
 
 

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




Posts: 5
Re: Running multiple selects in different cells
Reply #3 - 20.10.05 at 13:08:45
 
Hello
I tried the options given.  
With Option 1 I was a bit confused as to how this worked. Would this only work if entered into the code as a macro?
I just option 2 using "to A2" whcih works.
 
BUT.. when I go to refresh the cells it wipes out all the other queries in an 8 cell square round this cell. I am using the overwrite option and format output.
 
Thanks
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Running multiple selects in different cells
Reply #4 - 20.10.05 at 13:25:33
 
The reason why it wipes the cells is that SQL*XL looks for all filled cells to the right and to all filled cells down to clear the previous results. Unfortunately it also finds the cells where you have put other SQL statements...
 
Just thinking about this, should the following be of help? I could extend the to clause to restrict the cells SQL*XL puts the results in. Now you can use select * from emp to B5 so SQL*XL starts putting table emp in cells starting in B5. I could extend the use of the to clause to do this I guess: select * from emp to B5:C10. This way SQL*XL would only touch the block of cells B5 to C10. Any data put outside of this range will be discarded. That would help. If you could execute select count(*) from emp to B5:B5 you make it clear you do not wish anything else to be done apart from the data going into B5. I will have a look whether I can build this in.  
 
Having said all this about the to clause I do believe you are better off using the worksheet formula. You indicate that it is not clear to you how it should be used. In a normal cell that you have formatted as General you can type a formula. Type e.g. =Now() and it will display the current date and time. You can also use SQL*XL formulas here. That is what I meant. If you type the following formula in A1 it show the result in A1 of this query:  
=sqlxl.xla!SQLQueryValue("select count(*) from emp")
Back to top
 
 

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




Posts: 75
Re: Running multiple selects in different cells
Reply #5 - 20.10.05 at 13:44:33
 
I just had a look at the code that wipes the previous results when you refresh a query. It will look at the number of columns you have retrieved and remove these plus all the filled rows below it. That is the best I can do as I never know how many rows you retrieved previously.  
 
I need to clear the results as it would be confusing if SQL*XL would dump new results on top of the old results. Suppose you get fewer rows, then you never know whether there is a block of old data at the bottom of the table in Excel...
Back to top
 
 

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




Posts: 5
Re: Running multiple selects in different cells
Reply #6 - 20.10.05 at 14:46:21
 
The excel formula looks good.
The trouble I have is that I am pulling the formula in from an oracle database view to give me the grid then I want the formula to run against another source (JDE through ODBC link).
So once I had managed to manually get everything sorted was just going to set up a macro to do the following:
1. Connect to oracle database
2. Refresh queries that produce the grid.
3. Disconnect this oracle connection.
4. Run the queries against the other system.
5. Disconnect from this system.
 
This gives me the following issue. If I do this with the formula then the will query against the database that doesn't have these tables in it and fail.
If the errors can be surpressed then it could be ok.
On other tabs I have selects that would return multiple rows. I was wanting to transpose these so that I can put them along the columns.
Can you put the parameters for this into the formula?
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Running multiple selects in different cells
Reply #7 - 20.10.05 at 16:04:34
 
You cannot put a parameter into the select statement to preset whether the data is printed normally or transposed.
 
If you want more control you need to turn to VBA macro recording and coding. A few syntax elements that help with what you wish to achieve:
 
Use this property to switch off the error dialogs. The errors obviously still occur and they are still logged to the windows event log. The message box does not display.
SQLXL.LITErr.DisplayAlerts = False
 
To transpose data - print database rows in Excel columns - use the following property of the Excel Target object:
SQLXL.Targets(litExcel).Transpose = True
Back to top
 
 

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




Posts: 5
Re: Running multiple selects in different cells
Reply #8 - 21.10.05 at 01:53:58
 
Hello
Changed the code to output the columns in the form of the formulas.
After a few changes like putting columns to general instead of text and then recalculating the formulas everything seems to be fine and it does as I want.
As I get further on my sql statements get longer so much so that I get a "formula is too long" error.  
Is there anyway around this as using the formula is what I am looking for?
 
Thanks
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Running multiple selects in different cells
Reply #9 - 21.10.05 at 09:55:27
 
Nice to hear you got it all working ok.  
 
There is a limit to everything. The limit for formula in Excel is difficult to check. The limit is 1024 characters but Excel will expand any references you use to cells to the fully qualified names including sheet name and workbook name I read somewhere. If that happens you will reach any limit soon.
 
Another limit you may encounter is the nesting of formula. You can nest up to 7 levels in Excel. Example of nesting:
=if( concatenate(A1,A2) = "abcdef", "yes" , "no" )
Here the concatenate function is nested in the if statement. You could nest another function in the concateneate function. You can go to a maximum nesting level of 7.
 
How to get around these limitations?  
You need to break up your formula or you could use VBA functions.
 
To use VBA functions in your workbook press Alt-F11 to go to the VBA projects. Insert a new module. If you code the following:
 
Public Function TimesFour(Number As Double) As Double
  TimesFour = Number * 4
End Function
 
In Excel type the following formula in a cell:
=TimesFour(10)
You will see the result in the cell: 40
 
To break up your formula you can use the following techniques:
You can dump the base database information in a set of cells that you can hide. You could write it to a hidden worksheet for example. With formula you can then work on the data. If the formula get complex you can do it in stages. Do one part of the formula first. Then with this temporary result, make a new formula in a new cell where you do the next step, etc.  
 
 
To make it a little easier I have coded a warning message in SQL*XL when you hit a formula problem. I noticed that if you try a formula that is not correct, SQL*XL errors out. I have coded an option to optionally continue or stop processing.
Back to top
 
 

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