Passing new .CommandText to SQL QueryTable in Excel

[Guest]

How do I pass a very long and complicated (for me anyway) SQL query to a
querytable in Excel? I know I have to use .CommandText, but every time I try
and combine the elements of the query (which have been stored in a string
array), the SQL will display in Microsoft Query, but will not execute. I need
to do this as my User Interface asks the user to supply 3 parameters which
then need to be incorporated in the query so that the correct data is
returned to a worksheet.
I have tried separating the elements with a space - Chr(32) - and also the
sequence - Chr(13) & "" & Chr(10) - which I found elsewhere. I can supply the
query, but it is nearly 1900 characters long.
Thanks,
Steve

 

[Dick Kusleika]

Steve

If you're using a string array, the I don't think the length of SQL is the
problem. Can you reproduce the problem with a shorter SQL statement? If
so, post that statement. If not, post both the long and the short
statement.

You may be able to use a parameter query instead of changing the SQL
statement every time. See
http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters

 

[Guest]

Thanks for the reply, Dick.
With a little experimenting with line lengths (and actual text as opposed to
a string array), I managed to get the SQL passed correctly to Microsoft Query
and it now runs OK - picking up the 3 user choices on the way. I'd forgotten
about parameters too - when I get a chance I might try those out and see if
that method is any easier.

Steve