AddFields method of PivotTable Class Failed

[safcftm117]

I've been handed a macro recorded some time ago by one of our users which creates a pivot table using data from an external database.

A new requirement has arisen whereby the users want to be able to define the criteria for the pivot table i.e. rows, columns and data fields. (They cannot use the wizard as they should not know the DSN information for the DB).

the current macro has the following:
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Item","Description","Supplier","Res_code"), ColumnFields:=Array("Model")
ActiveSheet.PivotTables("PivotTable1").PivotFields("Qty").Orientation = _
xlDataField

I want to pass in variables defined on a user form to the pivot table so I amended the code as follows:
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array(row_fields), ColumnFields:=Array(column_fields)
ActiveSheet.PivotTables("PivotTable1").PivotFields("data_fields").Orientation = _
xlDataField

Where row_fields = "Item","Description","Supplier","Res_code", column_fields = "model" and data_fields = "Qty"

The problem seems to be that because the variable type is a string VB puts an extra set of quotes around its content e.g. ""Item","Description","Supplier","Res_code"" and the program fails with the above error.

I have tried using Replace() to replace the double quotes with single ones but I get the same message. Any suggestions?

 

[SkipVought]

hi,

Why not declare row_fields as an array, load it and assign it directly to the RowFields property?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]