How Do I Pass Variables To The Addfields Method Of the Pivottable Class (1 Viewer)

chergh

blah
Local time
Today, 11:10
Joined
Jun 15, 2004
Messages
1,414
Hi Folks,

I am trying to pass variables to a sub which creates a pivottable.

The following line of code works:

Code:
pt.AddFields RowFields:=Array("BG_DETECTION_DATE", "BG_SEVERITY"), ColumnFields:=Array("BG_PROJECT_DB", "BG_USER_01")

I want to create multiple pivottables so I created a seperate sub and intend to pass information to them. I have tried doing it as follows but it doesn't work:

Code:
dim row_fields as String
dim column_fields as string

Row_Fields = """BG_DETECTION_DATE"", ""BG_SEVERITY"""

Column_Fields = """BG_PROJECT_DB"", ""BG_USER_01"""

I have defined these as strings I then call the sub which creates the pivottable as follows:

Code:
Call pt_td_metrics(Row_Fields, _
                   Column_Fields)

Here is the start of the sub for creating the pivot tables:

Code:
Sub pt_td_metrics(Row_Fields As String, _
                  Column_Fields As String)

And here is the line where I have the problem:

Code:
pt.AddFields RowFields:=Array(Row_Fields), ColumnFields:=Array(Column_Fields)

Here the code breaks and I get Run-time error '1004': AddFields method of the pivottable class failed.

As the text string looks right when I use a message box to look at it I am guessing that passing this stuff as a string is completely the wrong way to do it.

This is a cut down version of the code and will post it all should anyone think it necessary.

Anyone able to help?
 

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 06:10
Joined
Jul 5, 2007
Messages
586
I don't think you can pass the quotation marks as text to a variable value.
I see what you're trying to do, but I'm pretty sure you can't do that.
Try building your strings using the Char function to represent the quotation marks (I can't remember which character number the quotation is.
 

chergh

blah
Local time
Today, 11:10
Joined
Jun 15, 2004
Messages
1,414
You can pass the quotation marks the way I have them but I have also tried using chr(34), as you suggested, and I get the same error. Even when I simplify to:

Code:
Row_Fields = Chr(34) & "BG_DETECTION_DATE" & Chr(34)

Column_Fields = Chr(34) & "BG_PROJECT_DB" & Chr(34)

It still doesn't work. Fortunatley I've got other things to keep me busy at the moment but when I do return to this if I can't get this to work I'm just going to have to repeat a lot of code.
 

chergh

blah
Local time
Today, 11:10
Joined
Jun 15, 2004
Messages
1,414
Sorted it now by using:

row_fields = Array("BG_DETECTION_DATE", "BG_SEVERITY")
column_fields = Array("BG_PROJECT_DB", "BG_USER_01")

bit of a doh moment when I didnt use it in the first place.
 
Last edited:

Users who are viewing this thread

Top Bottom