Type Mismatch when running query on SQL Database

Hi,

I'm trying to run a query from Excel against an SQL Database. I get a Type
Mismatch error when it is executing the following line of code.

Sheets(SQLDestinationSheet).Range(SQLDestinationCell).QueryTable.CommandText
= Array(FullSQL)

Now this works fine for the rest of the program except for my last query and
I suspect it is because the SQL statement that I store in the variable
FullSQL is greater than 255 characters long.

Anybody come across this and know a way around it?

Ta.

have you tried splitting that string?
this is an ultra crude way of doing it...
Replace/Split need xl2k+

dim vaSql, sFullSQL$
if len(sFullSQL) <= 255 then
vaSQL = array(sFullSQL)
else
sFullSql = replace(sfullSql," ",chr$(22))
vaSql = split(sfullsql,chr$(22))
end if

... .QueryTable.commandtext = vaSql




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Neily wrote :

> Hi,
>
> I'm trying to run a query from Excel against an SQL Database. I get
> a Type Mismatch error when it is executing the following line of code.
>
> Sheets(SQLDestinationSheet).Range(SQLDestinationCell).QueryTable.Comma
> ndText = Array(FullSQL)
>
> Now this works fine for the rest of the program except for my last
> query and I suspect it is because the SQL statement that I store in
> the variable FullSQL is greater than 255 characters long.
>
> Anybody come across this and know a way around it?
>
> Ta.

Neily

Bill Manville and I were just discussing this. See this thread
http://groups-beta.google.com/group/...16603c1071b148

I don't know all the details, but you can split your strings into an array
and it will work. See Bill's functions for helping you do this here

http://www.*****-blog.com/archives/2...per-functions/


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


Neily wrote:
> Hi,
>
> I'm trying to run a query from Excel against an SQL Database. I get
> a Type Mismatch error when it is executing the following line of code.
>
> Sheets(SQLDestinationSheet).Range(SQLDestinationCell).QueryTable.CommandText
> = Array(FullSQL)
>
> Now this works fine for the rest of the program except for my last
> query and I suspect it is because the SQL statement that I store in
> the variable FullSQL is greater than 255 characters long.
>
> Anybody come across this and know a way around it?
>
> Ta.