VBA - too many line continuations

albertan

New Member
Joined
Nov 16, 2014
Messages
36
I have a very long SQL statement which I'm trying to put into my VBA code.

In my VBA I have the following format for SQL:

With ActiveWorkbook.Connections ("Database").OLEDBConnection
.CommandTest = Array ("Select * bla bla bla")

I looked through some web information and it was suggested to combine SQL in severable variables.
I used VAR1 and VAR2 to split my SQL statement.
However it doesn't work when I put in VBA in a format

.CommandTest = Array (VAR1& VAR2)

I also tried it to concatenate it as per below link but it didn't work
https://stackoverflow.com/questions/1807775/sql-select-error-too-many-line-continuations
https://www.anysitesolutions.com/vba-error-too-many-line-continuations/

Can anyone please help me
thanks
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

albertan

New Member
Joined
Nov 16, 2014
Messages
36
Unfortunately, if I put variable in .CommandText = Array (Varname1) the code is not working

I get the Runtime error '1004': "The command text isn't set for the connection Database. To edit the command text, go to the Connections Manager on the Data tab and open the properties for this connection.

Without variable, the code works. The connection is seen clearly as it is established. I'm not sure why I can't split the SQL in variables...
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,106
Office Version
2019, 2016, 2013
Platform
Windows
never tried it with commandtext
its possible to split the bits out and ultimately reassemble, i build in sql then convert to strings for excel
 

Watch MrExcel Video

Forum statistics

Threads
1,089,922
Messages
5,411,265
Members
403,353
Latest member
ecboy1605

This Week's Hot Topics

Top