asked on

i would like to type the following line in Excel 2003 macro but its too long how do i correct it (divide it) that excel macro will except it?

Set comm.ActiveConnection = conn
comm.CommandText = _
"select cutdetail.cutno, cutdetail.style, cutdetail.color, cutdetail.qty, cutdetail.qty1, cutdetail.qty2, cutdetail.qty3, cutdetail.qty4, cutdetail.qty5, cutdetail.qty6, cutdetail.qty7, cutdetail.qty8, cutdetail.qty9, cutdetail.qty10, cutdetail.qty11, cutdetail.qty12, division.size1, division.size2, division.size3, division.size4, division.size5, division.size6, division.size7, division.size8, division.size9, division.size10, division.size11, division.size12 from cutdetail, ivtf, division where cutdetail.cutno = '9702204' and cutdetail.style = ivtf.code and cutdetail.color = ivtf.color and ivtf.division = division.code group by cutdetail.cutno, cutdetail.style, cutdetail.color , cutdetail.qty1, cutdetail.qty2, cutdetail.qty3, cutdetail.qty4, cutdetail.qty5, cutdetail.qty6, cutdetail.qty7, cutdetail.qty8, cutdetail.qty9, cutdetail.qty10, cutdetail.qty11, cutdetail.qty12 , cutdetail.qty, division.size1, division.size2, division.size3, division.size4, division.size5, division.size6, division.size7, division.size8, division.size9, division.size10, division.size11, division.size12"

the select stetment its too long to excel macro how do i string it or divided it to two part that excel macro will accept it?

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D_Wong

You could use a variable, for example ;

------------------------------------------------------------

Set comm.ActiveConnection = conn

SqlText = "first line of sql statement here"
SqlText = SqlText & "secondline line of sql statement here"
SqlText = SqlText & "etc"

comm.CommandText =SqlText

----------------------------------------------------------------

Rob Henson

You could make the separate parts as individual strings and then string them together.

For Example:

CutDetStrg = "cutdetail.qty,"
For CutDetCt = 1 to 12
CutDetStrg = CutDetStrg &  "cutdetail.qty" & CutDetCt &","
Next CutDetCt

DivSizeStrg = "division.size,"
For DivSizeCt = 1 to 12
DivSizeStrg = DivSizeStrg & "division.size" & DivSizCt & ","
Next DivSizCt

Open in new window

Repeated for each element

comm.CommandText = CutDetStrg & DivSizStrg & ...

Hope that helps
Rob H