Trouble with code that is broken over multiple lines (space_)

[mr tom]

I've got the following code (generated by recording a macro), but it doesn't
work. I've not modified it. I'm sure it's because of the way it's spanning
lines.

Apologies - I'm not sure whether it's possible to duplicate the formatting
here, so I'll paste all the code and then the excerpt that excel highlights
red (syntax error)

Any ideas gratefully received.

Tom.

WHOLE CODE:

Sub Macro1()

Sheets("Initial Query").Select
Range("D3").Select
With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=CSCSQL02;UID=kcarswell;APP=Microsoft
Office XP;WSID=HP1847;DATABASE=dwAWD;Trusted_Connection=Yes"
.CommandText = Array( _
"SELECT DISTINCT dim_Adviser.AdviserAlias,
Sum(fct_CommissionAccrued.AccruedAmount) AS 'Sum of
AccruedAmount'"&chr(13)&""&chr(10)&"FROM dwAWD.dbo.dim_Adviser dim_Adviser,
dwAWD.dbo.dim_CommissionType dim_CommissionType, d" _
, _
"wAWD.dbo.dim_PolicyKPI dim_PolicyKPI,
dwAWD.dbo.fct_CommissionAccrued fct_CommissionAccrued,
dwAWD.dbo.vdw_OnRiskCalendar vdw_OnRiskCalendar"&chr(13)&""&chr(10)&"WHERE
fct_CommissionAccrued.AdviserID = dim_Adviser.Advise" _
, _
"rID AND fct_CommissionAccrued.CommissionTypeID =
dim_CommissionType.CommissionTypeID AND fct_CommissionAccrued.PolicyKPIID =
dim_PolicyKPI.PolicyKPIId AND vdw_OnRiskCalendar.OnRiskDateID = fct_Commiss" _
, _
"ionAccrued.PolicyOnRiskDateID AND
((dim_CommissionType.CommissionReallocatedAccType='Adviser') AND
(dim_CommissionType.CommissionSubPhase In ('Fee','Initial')) AND
(dim_PolicyKPI.KPI_OnRisk='OnRisk') " _
, _
"AND (vdw_OnRiskCalendar.TimeDate Between {ts '2007-01-01 00:00:00'}
And {ts '2007-12-31 00:00:00'}))"&chr(13)&""&chr(10)&"GROUP BY
dim_Adviser.AdviserAlias, dim_Adviser.AdviserUnused,
dim_Adviser.Department"&chr(13)&""&chr(10)&"HAVING (dim_" _
,)
.Refresh BackgroundQuery:=False
End With
End Sub


BROKEN SECTION:

..CommandText = Array( _
"SELECT DISTINCT dim_Adviser.AdviserAlias,
Sum(fct_CommissionAccrued.AccruedAmount) AS 'Sum of
AccruedAmount'"&chr(13)&""&chr(10)&"FROM dwAWD.dbo.dim_Adviser dim_Adviser,
dwAWD.dbo.dim_CommissionType dim_CommissionType, d" _
, _
"wAWD.dbo.dim_PolicyKPI dim_PolicyKPI,
dwAWD.dbo.fct_CommissionAccrued fct_CommissionAccrued,
dwAWD.dbo.vdw_OnRiskCalendar vdw_OnRiskCalendar"&chr(13)&""&chr(10)&"WHERE
fct_CommissionAccrued.AdviserID = dim_Adviser.Advise" _
, _
"rID AND fct_CommissionAccrued.CommissionTypeID =
dim_CommissionType.CommissionTypeID AND fct_CommissionAccrued.PolicyKPIID =
dim_PolicyKPI.PolicyKPIId AND vdw_OnRiskCalendar.OnRiskDateID = fct_Commiss" _
, _
"ionAccrued.PolicyOnRiskDateID AND
((dim_CommissionType.CommissionReallocatedAccType='Adviser') AND
(dim_CommissionType.CommissionSubPhase In ('Fee','Initial')) AND
(dim_PolicyKPI.KPI_OnRisk='OnRisk') " _
, _
"AND (vdw_OnRiskCalendar.TimeDate Between {ts '2007-01-01 00:00:00'}
And {ts '2007-12-31 00:00:00'}))"&chr(13)&""&chr(10)&"GROUP BY
dim_Adviser.AdviserAlias, dim_Adviser.AdviserUnused,
dim_Adviser.Department"&chr(13)&""&chr(10)&"HAVING (dim_" _
,)

 

[Joel]

It is something like this

Sub Macro1()

Sheets("Initial Query").Select
Range("D3").Select
With Selection.QueryTable
.Connection = _
"ODBC;" & _
"DRIVER=SQL Server;" & _
"SERVER=CSCSQL02;" & _
"UID=kcarswell;" & _
"APP=Microsoft Office XP;" & _
"WSID=HP1847;" & _
"DATABASE=dwAWD;" & _
"Trusted_Connection=Yes"
.CommandText = Array( _
"SELECT DISTINCT dim_Adviser.AdviserAlias," & _
"Sum(fct_CommissionAccrued.AccruedAmount) AS " & _
"'Sum of AccruedAmount '" & _
Chr(13) & "" & Chr(10) & _
"FROM dwAWD.dbo.dim_Adviser dim_Adviser," & _
"dwAWD.dbo.dim_CommissionType dim_CommissionType, d," & _
"wAWD.dbo.dim_PolicyKPI dim_PolicyKPI," & _
"dwAWD.dbo.fct_CommissionAccrued fct_CommissionAccrued," & _
"dwAWD.dbo.vdw_OnRiskCalendar vdw_OnRiskCalendar" & Chr(13) & ""
& _
Chr(10) & "WHERE fct_CommissionAccrued.AdviserID = " & _
"dim_Adviser.Advise," & _
"rID AND fct_CommissionAccrued.CommissionTypeID = " & _
"dim_CommissionType.CommissionTypeID AND " & _
"fct_CommissionAccrued.PolicyKPIID = " & _
"dim_PolicyKPI.PolicyKPIId AND " & _
"vdw_OnRiskCalendar.OnRiskDateID = fct_Commiss," & _
"ionAccrued.PolicyOnRiskDateID AND " & _
"((dim_CommissionType.CommissionReallocatedAccType='Adviser') AND
" & _
"(dim_CommissionType.CommissionSubPhase In ('Fee','Initial')) AND
" & _
"(dim_PolicyKPI.KPI_OnRisk='OnRisk'), AND " & _
"(vdw_OnRiskCalendar.TimeDate Between {ts '2007-01-01 00:00:00'}
" & _
"And {ts '2007-12-31 00:00:00'}))" & Chr(13) & "" & Chr(10) &
"GROUP BY " & _
"dim_Adviser.AdviserAlias, dim_Adviser.AdviserUnused," & _
"dim_Adviser.Department" & Chr(13) & "" & Chr(10) & "HAVING
(dim,,)")
.Refresh BackgroundQuery:=False
End With
End Sub

 

[mr tom]

Thanks, Joel.

Why didn't it work originally?

It seems very odd for excel to record a macro that doesn't work!

Tom.

 

[Joel]

There are a number of recorded macros that don't work unless you modify them.
Specifically Querry Table and Shape selection 9including charts) require
minor tweeks. It is impossible for me to tell what was wrong with your code
because of the way code gets posted at this website. then line length is
limited so I can't easily tell the problems I corrected because of the line
length and problems you may of created when you made your modifications.


You can see from my posted code that parameter passed to a Querry Table are
really strings (not the actually parameter). This means you need to have
double quotes around these parameters, ampersands to connect the strings, and
the line continuation character (underscore) at the end of the lines.

Most people don't realize you can break string up into parts

newstring = "the lazy dog jumped over the fence"

equivalent

newstring = "The lazy dog " & "jumped over the fence"

or

newstring = "The lazy dog " & _
"jumped over the fence"

When lines run long I always break them up into multiple lines. It is
easier to read the code when you don't have to horizontally scroll to see the
entire code.

 

[mr tom]

Thanks again. That's really useful to know.

There are a number of recorded macros that don't work unless you modify them.
Specifically Querry Table and Shape selection 9including charts) require
minor tweeks. It is impossible for me to tell what was wrong with your code
because of the way code gets posted at this website. then line length is
limited so I can't easily tell the problems I corrected because of the line
length and problems you may of created when you made your modifications.


You can see from my posted code that parameter passed to a Querry Table are
really strings (not the actually parameter). This means you need to have
double quotes around these parameters, ampersands to connect the strings, and
the line continuation character (underscore) at the end of the lines.

Most people don't realize you can break string up into parts

newstring = "the lazy dog jumped over the fence"

equivalent

newstring = "The lazy dog " & "jumped over the fence"

or

newstring = "The lazy dog " & _
"jumped over the fence"

When lines run long I always break them up into multiple lines. It is
easier to read the code when you don't have to horizontally scroll to see the
entire code.