Excel cell as a parameter not working


Welcome, Guest. Please Login.

ExcelLock: Locking and securing your valuable Excel spreadsheets

encOffice: Protect your Excel file easy and safe

LIT Software Forums › SQL*XL › General Usage and Feature Troubleshooting

‹ Previous topic | Next topic ›

Pages: 1

Excel cell as a parameter not working (Read 4265 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!

IP Logged

Gerrit-Jan Linker

YaBB Administrator

Posts: 13

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.

« Last Edit: 30.07.10 at 11:02:18 by Gerrit-Jan Linker »

Gerrit-Jan Linker
Linker IT Software

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.

IP Logged

Gerrit-Jan Linker

YaBB Administrator

Posts: 13

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

Gerrit-Jan Linker
Linker IT Software

IP Logged

Gerrit-Jan Linker

YaBB Administrator

Posts: 13

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

« Last Edit: 01.08.10 at 13:46:04 by Gerrit-Jan Linker »

Gerrit-Jan Linker
Linker IT Software

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

LIT Software Forums › SQL*XL › General Usage and Feature Troubleshooting

‹ Previous topic | Next topic ›

Copyright (C) 1995-2007 Linker IT Software BV. All Rights Reserved. Oracle is a registered trademark of Oracle corporation. Excel and Office are registered trademarks of Microsoft corporation. Other names appearing on the site may be trademarks of their respective owners. Software, files, documents, articles and other material are provided "as is" and without warranties as to performance or mechantability or any other warranties whether expressed or implied. No warranty of fitness for a particular purpose is offered.
sitemap