Passing new .CommandText to SQL QueryTable in Excel

[Steve Wadey]

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.*****-clicks.com/excel/Ex...htm#Parameters


--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Steve Wadey wrote:
> 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

[Steve Wadey]

Thanks for the reply, ****.
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

"**** Kusleika" wrote:

> 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.*****-clicks.com/excel/Ex...htm#Parameters
>
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Steve Wadey wrote:
> > 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
>
>
>