VBA code on Data Warehouse criteria

[Rowanhf12]

Hi

I want to use VBA code to update the criteria in the Data Warehouse table I'm using

The current code I have is:

Sub Macro1()
'
' Macro1 Macro
'


'
Sheets("List").Select
Range("A1").Select
Dim varCellValueList As String
varCellValueList = Range("G2").Value


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 ((view_Billing_v4.ProductNo In varCellValueList)" _
, _
"AND (view_Billing_v4.BillYear>2013)" _
)
.Refresh BackgroundQuery:=True
End With
Range("A2").Select
Sheets("List").Select
Range("A1").Select
End Sub


The code is written in red, and the problem areas (as far as I can see) are coloured blue


So it will be the ProductNo criteria that gets updated each time the Macro gets run. The ProductNo list is currently located in the 'List' tab cell G2.
All that happens at the moment is the criteria for ProductNo just reads 'varCellValueList', where I want it to read the list of Product Numbers written in cell G2.

Any help?

Thanks

 

[daverunt]

Hi,

you have varCellValueList inside string quotes so it is treated as a string and not a variable containing a value.
You need to 'add' it to a string by using "string" & varCellValueList & "string"
Any bits of string around it are enclosed in quotes.


Try

"WHERE ((view_Billing_v4.ProductNo In " & varCellValueList & ")" _

 

[Rowanhf12]

Thank you so much

That worked perfectly.