Add Fields Method of Pivot Table 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 avriable type is a string VB puts an extra set of quotes around row_fields 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?

 

[PeterWallace]

You can try this function

' from Tek-Tips but unfortunate I can Not Remember Who Supplied


TheDesc = "Shrug Shoulders"

' Call By "
SQL = "SELECT Abc , efg From TheTable where FieldName = " & PutQuotes(TheDesc)

Or

SQL = "SELECT Xyz, Mno From TheTable where FieldName = " & PutQuotes("Nod Head")




Public Function PutQuotes(ByRef txtToQuote As String) As String
Dim iASCII As Integer

iASCII = 34

PutQuotes = Chr(iASCII) & txtToQuote & Chr(iASCII)
End Function