VBA code on Data Warehouse too long

[Rowanhf12]

I'm trying to run a query in the Data Warehouse. But with varying criteria each time it gets run.

I have this code so far:
Sub Macro2()
'
' Macro2 Macro
'


'
Sheets("List").Select
Range("A1").Select
Dim Code1a As Excel.Range, Code1b As Excel.Range, Code1c As Excel.Range
Dim Code2a As Excel.Range, Code2b As Excel.Range, Code2c As Excel.Range
Dim Code3a As Excel.Range, Code3b As Excel.Range, Code3c As Excel.Range
Dim Code4a As Excel.Range, Code4b As Excel.Range, Code4c As Excel.Range
Set Code1a = Range("U2")
Set Code1b = Range("V2")
Set Code1c = Range("W2")
Set Code2a = Range("U3")
Set Code2b = Range("V3")
Set Code2c = Range("W3")
Set Code3a = Range("U4")
Set Code3b = Range("V4")
Set Code3c = Range("W4")
Set Code4a = Range("U5")
Set Code4b = Range("V5")
Set Code4c = Range("W5")


Sheets("Data Part1").Select
Range("A2").Select
With Selection.ListObject.QueryTable
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=DW-SQL;UID=gbell;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=SOUR-00093-L;DATABASE=RDW"
.CommandText = Array( _
"SELECT view_Billing_v4.BillingDoc, view_Billing_v4.BillToCountry, view_Billing_v4.BillingDate, view_Billing_v4.ShipToCountry, view_Billing_v4.SalesDoc, view_Billing_v4.SalesDistrictText, view_Billing_" _
, _
"v4.ProductNo, view_Billing_v4.ProductText, view_Billing_v4.BillingQty, view_Billing_v4.ProductHierarchy, view_Billing_v4.USD_NetSales3, view_Billing_v4.USD_Cost, view_Billing_v4.BillMonth, view_Billin" _
, _
"g_v4.BillQuarter, view_Billing_v4.BillYear, view_Billing_v4.ProductHierarchyText_Product, view_Billing_v4.ProductHierarchyText_Material, view_Billing_v4.ItemCategoryCode" & Chr(13) & "" & Chr(10) & "FROM RDW.dm.view_Billing_v4 v" _
, _
"iew_Billing_v4" & Chr(13) & "" & Chr(10) & "WHERE (" & Code1a & ")" _
, _
" AND (" & Code1b & ")" _
, _
" AND (" & Code1c & ")" _
, _
" OR (" & Code2a & ")" _
, _
" AND (" & Code2b & ")" _
, _
" AND (" & Code2c & ")" _
, _
" OR (" & Code3a & ")" _
, _
" AND (" & Code3b & ")" _
, _
" AND (" & Code3c & ")" _
)
.Refresh BackgroundQuery:=True
End With
Range("A2").Select
Sheets("List").Select
Range("A1").Select
End Sub


However, when I try and include Code4 criteria, VBA tells me 'too many line continuations'.
Is there anyway to pause that piece of code and carry on a separate piece that just continues from the previous code.

I'm aware that if I just run a separate piece of code after it, Code 4 will just replace Code1,2 and 3 instead of adding to it.

Any help??

 

[Richard U]

You have several options.

- Put your entire select statement in a cell on a worksheet, then the code would just be....

.CommandText = Array(sheets(mySheet(cells(1,1)))

or just put the entire select statement into a string or an array.

The way you are doing it just taxes Excel VBA too much.