Code:
With ActiveWorkbook.Connections("Invoiced Sales").OLEDBConnection
.BackgroundQuery = False
.CommandText = Array( _
"Select" & Chr(13) & "" & Chr(10) & "RBC.Year," & Chr(13) & "" & Chr(10) & "RBC.Period," & Chr(13) & "" & Chr(10) & "case when " & Chr(13) & "" & Chr(10) & "OD.OrderType = 'Return Order' then 'Returns'" & Chr(13) & "" & Chr(10) & "else 'Gross Sales'" & Chr(13) & "" & Chr(10) & "end a" _
, _
"s 'Order Type'," & Chr(13) & "" & Chr(10) & "p.Department," & Chr(13) & "" & Chr(10) & "p.Manufacturer," & Chr(13) & "" & Chr(10) & "p.CategoryName," & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "SUM(od.OrderedPrice/od.VATRate ) as ReportedSa" _
, _
"les," & Chr(13) & "" & Chr(10) & "SUM(od.OrderedPrice/od.VATRate - (OD.QuantityDespatched * isnull(HSCR.LastCost,P.LastCost))) as ReportedMargi" _
, _
"n" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "From" & Chr(13) & "" & Chr(10) & "[WiggleBI].dbo.DevOrderDetail od" & Chr(13) & "" & Chr(10) & "left join [Wiggle-reporting].dbo.tblCombinedProductStockOrderSales as " _
, _
"P on od.ProductID = P.ProductID" & Chr(13) & "" & Chr(10) & "left join [wiggle-reporting].dbo.RBCalender RBC on CONVERT (date, od.InvoiceDate, " _
, _
"103) = RBC.Date" & Chr(13) & "" & Chr(10) & "left join [Wiggle-reporting].dbo.tblHistStockCostReceiptDate as HSCR on od.ProductID = HSCR.Produc" _
, _
"tID and rbc.Abs_Month = HSCR.AbsMonth" & Chr(13) & "" & Chr(10) & "left join [wiggle-reporting].dbo.RBKeyCountries2 KC on od.CountryID = KC.Cou" _
, _
"ntryID" & Chr(13) & "" & Chr(10) & "join [wiggle-reporting].dbo.udt_constantcurrencyrates c on od.CurrencyCode = c.currency" & Chr(13) & "" & Chr(10) & "join [wiggleBI].db" _
, _
"o.V6_Orders d on od.OrderID = d.OrderID" & Chr(13) & "" & Chr(10) & "left join (select Products, SUM(Quantity) AS PackSize from [wiggleBI].dbo." _
, _
"V6_ProductAssembly group by Products) PA on OD.ProductID = PA.Products " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "Where" & Chr(13) & "" & Chr(10) & "RBC.Year >= 2015" & Chr(13) & "" & Chr(10) & "and" & Chr(13) & "" & Chr(10) & "P.Divisi" _
, _
"on not in ( 'Other' , 'Unknown' )" & Chr(13) & "" & Chr(10) & "and od.QuantityDespatched != 0" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "Group by" & Chr(13) & "" & Chr(10) & "RBC.Year," & Chr(13) & "" & Chr(10) & "RBC.Period," & Chr(13) & "" & Chr(10) & "case when " & Chr(13) & "" & Chr(10) & "" _
, _
"OD.OrderType = 'Return Order' then 'Returns'" & Chr(13) & "" & Chr(10) & "else 'Gross Sales'" & Chr(13) & "" & Chr(10) & "end," & Chr(13) & "" & Chr(10) & "p.Department," & Chr(13) & "" & Chr(10) & "p.Manufacturer," & Chr(13) & "" & Chr(10) & "p.CategoryN" _
, "ame")
.CommandType = xlCmdSql
.Connection = Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=wiggle-reporting;Data Source=sqlexttrade" _
, _
"pack;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=NH-LAPTOP-153;Use Encryption for Data=False" _
, ";Tag with column collation when possible=False")
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Invoiced Sales")
.Name = "Invoiced Sales"
.Description = ""
End WithHi All
I have the above code linked to a SQL server, that produces a Pivot table directly.
At the moment, this code is set to the maximum amount of lines available (24 I believe)
'Cus when I try and add extra data fields to the SQL query, it tells me 'too many line continuations' in VBA.
I understand there is a way to work around this, so you can have as many lines for a SQL server query as you like.
From what I've read, it's something along the lines of:
strSql = line 1
line 2
....
line 24
strSql = strSql & line 25
line 26
....
I'm not sure how to apply this to my query, or if this method is possible.
Any help would be greatly appreciated
Kind regards.
