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
Excel cell as a parameter not working (Read 6082 times)
derek4u
YaBB Newbies
*


I Love SQL*XL

Posts: 2
Excel cell as a parameter not working
29.07.10 at 07:56:59
 
Hello,
 
The following works:
select cn from 'LDAP://company.com/OU=Clients,DC=company,DC=com' where objectclass='user'
 
The following does not work:
select cn from 'LDAP://company.com/OU=Clients,DC=company,DC=com' where objectclass= :A1;
 
I enter "user" into cell A1 and I get the error 'no such interface supported' and the Parameters window opens. I don't want to specify a parameter. I would like to put the value into an Excel cell. What am I doing wrong? Thanks!
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 16
Re: Excel cell as a parameter not working
Reply #1 - 30.07.10 at 10:59:38
 
Hi,
 
It seems that the driver cannot use the bind variable that you have tried to use. That is a limitation of the driver unfortunately.
 
You have sent me some further comments for which I have placed the answers below:
 
Quote:
>>
I am evaluating the SQL*XL product and would really like to purchase and use it at our company. We need to query Active Directory information and place the results into Excel.
>>
Yes, SQL*XL can do this.
 
 
Quote:
>>
I am able to get this to work using both the SQL*XL query button on the SQL*XL toolbar as well as the 'sqlqueryvalue' function in Excel cells. The sqlqueryvalue function is limited in that it cannot query multi-value object attributes but I am able to get it to work with cells as variables.
>>
That last point is not true. Your problem is that you have created the SQLQueryValue funtion as an ordinary cell function and not as an array function. As a cell function only one value displays. When you cast it into an array function multiple columns and rows can be shown.
 
Please read the following topics:
Using Array Formulas in Excel  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1150621743
 
Quote:
>>
The SQL*XL query button works with those attributes and works well with lists of data, but I cannot get it to work with an Excel field as a variable. I have a post in the forum for this under 'derek4u'.
>>
You can use the concatenate function or use the string concatenation operator &.  
 
Please see:
SQL*XL: Database query cell formula.
http://www.oraxcel.com/projects/sqlxl/demos/database_query_cell_formula.html
 
Quote:
>>
Ok, I have figured out how to make part of my LDAP query a variable by using the 'SQL Source' as 'A Worksheet' option when clicking the SQL query button. The actual cell that's referenced uses a =concatenate() function to build the final select...LDAP://... statement.
>>

Yes, that is the technique.
 
Quote:
>>
The problem is, how do I refresh this reference? If I change any of the values that create the cell with the SQLqueryvalue statement, it does not update. I've looked through your forums and even tried the macro option, but Excel macros don't seem to apply to the SQL*XL menus.
>>

Does it update when you press F9, the Excel shortcut to calculate the sheet?
You may want to set the workbook options to calculate immediately.
 
 
Back to top
 
« Last Edit: 30.07.10 at 11:02:18 by Gerrit-Jan Linker »  

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


I Love SQL*XL

Posts: 2
Re: Excel cell as a parameter not working
Reply #2 - 30.07.10 at 15:33:35
 
Thank you,
 
I'm getting closer. I think I understand now how to use variables in my queries by using the concatenate functions, however, I don't understand how an array will help me display multiple values. Please help!
 
My query
=sqlqueryvalue("select name from 'LDAP://company.com/OU=Users,DC=company,DC=com' where objectclass='user'")
 
This returns a list of multiple values, but in Excel only the first value is displayed in the cell. If I enter Ctrl+Shift+Enter, I get the {} brackets, but I don't understand how to display or work with those multiple values. How do I do this? Thanks for your help.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 16
Re: Excel cell as a parameter not working
Reply #3 - 30.07.10 at 17:24:28
 
Ok, what need to do is pretty simple but be warned that array formulas can be a bit nasty too in Excel. But you will get used to it.
 
To create an array formula, just do the same as for a normal function.  
Select the cell (for example A1) and enter the formula =sqlqueryvalue("select * from mytable")
 
This will indeed only display column 1 of row 1 of mytable.
 
Now it is very easy to cast this function into an array function. The array is easily expandable but not easily reduced.
Select a range of cells where you want the data to be displayed. For example A1:B10 to allow for 2 columns and 10 rows. Press F2 and you will see that the formula you typed in A1 is displayed. Now press Ctrl + Shift + Enter. Excel will create {} brackets around the formula to indicate it is an array formula.  
 
To expand the array further you need to ensure that you select the whole array and then make it larger.
Reducing the array is not possible (to my knowledge) I usually copy the first cell to a temporary location. Then clear the whole array and then start over.
 
I hope this helps!
 
See also:
Introducing array formulas in Excel
http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel -HA001087290.aspx
Back to top
 
 

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




Posts: 16
Parameters and Active Directory
Reply #4 - 01.08.10 at 12:14:48
 
Parameters and Active Directory
 
The use of bind variables is not possible when connecting to Active Directory using the ADSDSOObject provider.  
A bind variable is code like this:
select adspath,objectclass from 'LDAP://linker.nl' where objectClass= :my_bind_variable;
select adspath,objectclass from 'LDAP://linker.nl' where objectClass= :A1;
 
You can use a substitution variable. This can be thought of as a string replace variable:
select adspath,objectclass from 'LDAP://linker.nl' where objectClass= '&my_bind_variable';
select adspath,objectclass from 'LDAP://linker.nl' where objectClass= '&A1';
 
Note that with substitution variables you need to put the string delimiters '' around the parameter.
 
A change was made to SQL*XL to produce an error message instead that advices to use substitution variables instead.
 
See also:
SQL*XL: Excel Active Directory Query
http://www.oraxcel.com/projects/sqlxl/help/techniques/query/active_directory.htm l
Back to top
 
« Last Edit: 01.08.10 at 13:46:04 by Gerrit-Jan Linker »  

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


I Love SQL*XL

Posts: 1
Excel cell as a parameter not working
Reply #5 - 05.02.11 at 14:24:58
 
Rgr, anyway - resetting your controls is the way to go for starters.
Get it to work in vanilla mode - then go play with addon software afterwards ex TARGET.
One step at a time - that way you will rule out whats working or not.
Back to top
 
 
  IP Logged
Pages: 1