PivotTable Values problem (VB-related)

[winstein]

I am doing a Macro that involves creating a Pivot Table, but I am having some trouble with the implementation.

These are the configurations I want to make in the Pivot Table:
Report Filter: DESCRIPTION
Row Labels: STORE
Values: BUDGET (Sum), ACTUAL (Sum)
Filter: DESCRIPTION = TOTAL GROSS SALES

The trouble I have is implementing the Values portion of the code. The error I got when I run the code below is "Unable to set the Function property of the PivotField class". What do I need to do to get it to work?

Code: Select all

VBSTART
    Sub CreatePivotTable (File,MaxRows)
      xlRowField=1
      xlPageField=3
      xlColField=4
      xlMaxRows="M"+MaxRows
      
      xlSum=1
      
      Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = True
      Set objWorkbook = objExcel.Workbooks.Open(File)
      
      objWorkBook.Sheets("All Store").Activate
      objWorkBook.Sheets("All Store").Cells(13,1).Value="A"
      objWorkBook.Sheets("All Store").Cells(13,2).Value="B"
      objWorkBook.Sheets("All Store").Cells(13,3).Value="STORE"
      objWorkBook.Sheets("All Store").Cells(13,4).Value="D"
      objWorkBook.Sheets("All Store").Cells(13,5).Value="E"
      objWorkBook.Sheets("All Store").Cells(13,6).Value="F"
      objWorkBook.Sheets("All Store").Cells(13,7).Value="G"
      objWorkBook.Sheets("All Store").Cells(13,8).Value="H"
      objWorkBook.Sheets("All Store").Cells(13,9).Value="DESCRIPTION"
      objWorkBook.Sheets("All Store").Cells(13,10).Value="J"
      objWorkBook.Sheets("All Store").Cells(13,11).Value="BUDGET"
      objWorkBook.Sheets("All Store").Cells(13,12).Value="ACTUAL"
      objWorkBook.Sheets("All Store").Cells(13,13).Value="PERCENTAGE"
      objWorkBook.Sheets("All Store").Range("A13:"+xlMaxRows).Select
      
      
      Set objTable = objWorkbook.PivotTableWizard
      Set objField = objTable.PivotFields("STORE")
      objField.Orientation = xlRowField
      
      Set objField = objTable.PivotFields("DESCRIPTION")
      objField.Orientation = xlPageField
      objField.CurrentPage = "TOTAL GROSS SALES"
      
      Set objField = objTable.PivotFields("BUDGET")
      objField.Orientation = xlDataField
      objField.Function = xlSum
      objField.Caption = "Sum of BUDGET"

    End Sub
VBEND

VBRun>CreatePivotTable,ExcelFile,MaxRows

Thanks for reading.

[hagchr]

Hi, I am in a hurry today so I just threw your data into one of my old pivot scripts. It creates a new pivot table into Sheet1 (can be changed in the script). Problem is if the pivot table already exists you will get an error message - so if you want to update an existing table then it needs to be modified. Hope it helps in terms of structure. I have some more time tomorrow to look at your script if you still have a problem.

Code: Select all

VBSTART
    Sub CreatePivotTable(File,MaxRows)

      xlMaxRows="M"+MaxRows
      SourceRange="A13:"+xlMaxRows

      xlRowField=1
      xlColField=2
      xlDataField=4
      xlPageField=3
      xlDataBase=1

      Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = True

      Set objWorkbook = objExcel.Workbooks.Open(File)

      objWorkBook.Sheets("All Store").Activate
      objWorkBook.Sheets("All Store").Cells(13,1).Value="A"
      objWorkBook.Sheets("All Store").Cells(13,2).Value="B"
      objWorkBook.Sheets("All Store").Cells(13,3).Value="STORE"
      objWorkBook.Sheets("All Store").Cells(13,4).Value="D"
      objWorkBook.Sheets("All Store").Cells(13,5).Value="E"
      objWorkBook.Sheets("All Store").Cells(13,6).Value="F"
      objWorkBook.Sheets("All Store").Cells(13,7).Value="G"
      objWorkBook.Sheets("All Store").Cells(13,8).Value="H"
      objWorkBook.Sheets("All Store").Cells(13,9).Value="DESCRIPTION"
      objWorkBook.Sheets("All Store").Cells(13,10).Value="J"
      objWorkBook.Sheets("All Store").Cells(13,11).Value="BUDGET"
      objWorkBook.Sheets("All Store").Cells(13,12).Value="ACTUAL"
      objWorkBook.Sheets("All Store").Cells(13,13).Value="PERCENTAGE"
      objWorkBook.Sheets("All Store").Range(SourceRange).Select

'Define Source Sheet for Pivot Data
      SourceSheet = "All Store"
      Set objWorkSheet = objWorkBook.WorkSheets(SourceSheet)

'Define Name, Destination Sheet and Range for Pivot Table
      PTName = "Result"
      PTSheet = "Sheet1"
      PTRange = "R1C7"
      PTDestination = PTSheet & "!" & PTRange

'Define source range for pivot table
      objWorkSheet.PivotTableWizard xlDataBase, objWorkSheet.Range(SourceRange), PTDestination, PTName

'Active Sheet with Pivot Table
        Set PTWorkSheet = objWorkBook.Worksheets(PTSheet)
        PTWorkSheet.Activate

'To Get Classic Pivot view
      PTWorkSheet.PivotTables(PTName).InGridDropZones = True

'Set Pivot Table Field Orientations
      PTWorkSheet.PivotTables(PTName).PivotFields("STORE").Orientation = xlRowField
      PTWorkSheet.PivotTables(PTName).PivotFields("DESCRIPTION").Orientation = xlPageField
      PTWorkSheet.PivotTables(PTName).PivotFields("BUDGET").Orientation = xlDataField
      PTWorkSheet.PivotTables(PTName).PivotFields("ACTUAL").Orientation = xlDataField
      PTWorkSheet.PivotTables(PTName).PivotFields("DESCRIPTION").CurrentPage="TOTAL GROSS SALES"
      
End Sub
VBEND

Let>ExcelFile=C:\Users\Christer\Desktop\Test Pivot.xlsx
Let>MaxRows=23

VBRun>CreatePivotTable,ExcelFile,MaxRows

[winstein]

When I substituted the VB portion of my script, I got an error on this line:

Code: Select all

objWorkSheet.PivotTableWizard xlDataBase, objWorkSheet.Range(SourceRange), PTDestination, PTName

The error message is: PivotTableWizard method of Worksheet class failed. Do you know why I got this error?

[hagchr]

I guess you don't have a sheet called Sheet1, so there is a problem to paste the pivot table?

I have changed the code slightly so that the pivot table is always placed in a new sheet in cell A1, then you should never have a problem that the sheet is either missing or there is already a pivot table available in the target cell. In the end it depends on where the pivot table should be placed when the macro is run.

I also note you use MaxRows to determine how many rows to include in the pivot table. If you always use the full available table then there is the possibility to from eg cell A13 select the CurrentRegion as basis, then you do not need to pass the number of rows as a parameter (if you have more than 13 columns in the table then you need to adjust so extra columns are not included), but again, maybe you do not need the full table (see row 33 and 50 for remarked examples).

Hope it helps.

Code: Select all

VBSTART
    Sub CreatePivotTable(File,MaxRows)

      xlMaxRows="M"+MaxRows
      SourceRange="A13:"+xlMaxRows

      xlRowField=1
      xlColField=2
      xlDataField=4
      xlPageField=3
      xlDataBase=1

      Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = True

      Set objWorkbook = objExcel.Workbooks.Open(File)

      objWorkBook.Sheets("All Store").Activate
      objWorkBook.Sheets("All Store").Cells(13,1).Value="A"
      objWorkBook.Sheets("All Store").Cells(13,2).Value="B"
      objWorkBook.Sheets("All Store").Cells(13,3).Value="STORE"
      objWorkBook.Sheets("All Store").Cells(13,4).Value="D"
      objWorkBook.Sheets("All Store").Cells(13,5).Value="E"
      objWorkBook.Sheets("All Store").Cells(13,6).Value="F"
      objWorkBook.Sheets("All Store").Cells(13,7).Value="G"
      objWorkBook.Sheets("All Store").Cells(13,8).Value="H"
      objWorkBook.Sheets("All Store").Cells(13,9).Value="DESCRIPTION"
      objWorkBook.Sheets("All Store").Cells(13,10).Value="J"
      objWorkBook.Sheets("All Store").Cells(13,11).Value="BUDGET"
      objWorkBook.Sheets("All Store").Cells(13,12).Value="ACTUAL"
      objWorkBook.Sheets("All Store").Cells(13,13).Value="PERCENTAGE"
      objWorkBook.Sheets("All Store").Range(SourceRange).Select
      'objWorkBook.Sheets("All Store").Range("A13").CurrentRegion.Select

'Define Source Sheet for Pivot Data
      SourceSheet = "All Store"
      Set objWorkSheet = objWorkBook.WorkSheets(SourceSheet)

'Define Name, Destination Sheet and Range for Pivot Table
      PTName = "Result"
      PTRange = "R1C1"
      Set PTSheet = objWorkBook.Sheets.Add
      PTSheet.Activate
      PTSheet = PTSheet.Name
      
      PTDestination = PTSheet & "!" & PTRange
      
'Define source range for pivot table
      objWorkSheet.PivotTableWizard xlDataBase, objWorkSheet.Range(SourceRange), PTDestination, PTName
      'objWorkSheet.PivotTableWizard xlDataBase, objWorkSheet.Range("A13").CurrentRegion, PTDestination, PTName

'Active Sheet with Pivot Table
        Set PTWorkSheet = objWorkBook.Worksheets(PTSheet)
        PTWorkSheet.Activate

'To Get Classic Pivot view
      PTWorkSheet.PivotTables(PTName).InGridDropZones = True

'Set Pivot Table Field Orientations
      PTWorkSheet.PivotTables(PTName).PivotFields("STORE").Orientation = xlRowField
      PTWorkSheet.PivotTables(PTName).PivotFields("DESCRIPTION").Orientation = xlPageField
      PTWorkSheet.PivotTables(PTName).PivotFields("ACTUAL").Orientation = xlDataField
      PTWorkSheet.PivotTables(PTName).PivotFields("BUDGET").Orientation = xlDataField
      
      'Select one of the two for either col or row presentation of data
      PTWorkSheet.PivotTables(PTName).DataPivotField.Orientation = xlColField 'Gives data in column orientation
      'PTWorkSheet.PivotTables(PTName).DataPivotField.Orientation = xlRowField 'Gives data in row orientation
     
      PTWorkSheet.PivotTables(PTName).PivotFields("DESCRIPTION").CurrentPage="TOTAL GROSS SALES"
      
End Sub
VBEND

Let>ExcelFile=C:\Users\Christer\Desktop\Test Pivot.xlsx
Let>MaxRows=23

VBRun>CreatePivotTable,ExcelFile,MaxRows

[winstein]

Thank you for the information, hagchr, but there's one thing I need to know: how do you convert the column values to SUM? Right now the value is in COUNT, and from what I see the VBA in Excel works differently from the VB code in Macro.

[hagchr]

If the data are all numbers then it should default to SUM, so you need to check the actual/budget data, to ensure not formatted as text and also check there ar no non-numbers, eg text or spaces etc.

If you highlight/select the actual/budget data you can easily check at the bottom right on the screen - if you just see Count=xxx and not Sum=xxx then you likely have text.

I am not sure how you get your source data, so either try to store it as numbers when it goes into excel, alternatively you need to convert text to numbers in excel.

One way could be to add two extra columns to the right (either directly in excel or use VB to put them there) and use the function =Value(xxx), to convert it to number. Then just use the two new columns in the pivot table.

[winstein]

I have highlighted the fields in the Pivot table, and there is a Sum (in addition to Average and Count). Moreover, when I attempt to manually adjust the Values item, it can be converted from Count to Sum. Isn't there a way to change a column from Sum to Value within the code?

[hagchr]

You need to add this:

Code: Select all

      For Each pf In PTWorkSheet.PivotTables(PTName).DataFields
        If Not pf Is Nothing Then
            With pf
                .Function = xlSum
            End With
        End If
      Next

So all in all, something like this:

Code: Select all

VBSTART
    Sub CreatePivotTable(File,MaxRows)

      xlMaxRows="M"+MaxRows
      SourceRange="A13:"+xlMaxRows

      xlRowField=1
      xlColField=2
      xlDataField=4
      xlPageField=3
      xlDataBase=1

      xlSum=-4157
      xlCount=-4112
      xlAverage=-4106

      Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = True

      Set objWorkbook = objExcel.Workbooks.Open(File)

      objWorkBook.Sheets("All Store").Activate
      objWorkBook.Sheets("All Store").Cells(13,1).Value="A"
      objWorkBook.Sheets("All Store").Cells(13,2).Value="B"
      objWorkBook.Sheets("All Store").Cells(13,3).Value="STORE"
      objWorkBook.Sheets("All Store").Cells(13,4).Value="D"
      objWorkBook.Sheets("All Store").Cells(13,5).Value="E"
      objWorkBook.Sheets("All Store").Cells(13,6).Value="F"
      objWorkBook.Sheets("All Store").Cells(13,7).Value="G"
      objWorkBook.Sheets("All Store").Cells(13,8).Value="H"
      objWorkBook.Sheets("All Store").Cells(13,9).Value="DESCRIPTION"
      objWorkBook.Sheets("All Store").Cells(13,10).Value="J"
      objWorkBook.Sheets("All Store").Cells(13,11).Value="BUDGET"
      objWorkBook.Sheets("All Store").Cells(13,12).Value="ACTUAL"
      objWorkBook.Sheets("All Store").Cells(13,13).Value="PERCENTAGE"
      objWorkBook.Sheets("All Store").Cells(13,11).Value="BUDGET"
      objWorkBook.Sheets("All Store").Cells(13,12).Value="ACTUAL"
      objWorkBook.Sheets("All Store").Range(SourceRange).Select
      'objWorkBook.Sheets("All Store").Range("A13").CurrentRegion.Select

'Define Source Sheet for Pivot Data
      SourceSheet = "All Store"
      Set objWorkSheet = objWorkBook.WorkSheets(SourceSheet)

'Define Name, Destination Sheet and Range for Pivot Table
      PTName = "Result"
      PTRange = "R1C1"
      Set PTSheet = objWorkBook.Sheets.Add
      PTSheet.Activate
      PTSheet = PTSheet.Name
      
      PTDestination = PTSheet & "!" & PTRange
      
'Define source range for pivot table
      objWorkSheet.PivotTableWizard xlDataBase, objWorkSheet.Range(SourceRange), PTDestination, PTName
      'objWorkSheet.PivotTableWizard xlDataBase, objWorkSheet.Range("A13").CurrentRegion, PTDestination, PTName

'Active Sheet with Pivot Table
        Set PTWorkSheet = objWorkBook.Worksheets(PTSheet)
        PTWorkSheet.Activate

'To Get Classic Pivot view
      PTWorkSheet.PivotTables(PTName).InGridDropZones = True

'Set Pivot Table Field Orientations
      PTWorkSheet.PivotTables(PTName).PivotFields("STORE").Orientation = xlRowField
      PTWorkSheet.PivotTables(PTName).PivotFields("DESCRIPTION").Orientation = xlPageField
      PTWorkSheet.PivotTables(PTName).PivotFields("ACTUAL").Orientation = xlDataField
      PTWorkSheet.PivotTables(PTName).PivotFields("BUDGET").Orientation = xlDataField

      'Select one of the two for either col or row presentation of data
      PTWorkSheet.PivotTables(PTName).DataPivotField.Orientation = xlColField 'Gives data in column orientation
      'PTWorkSheet.PivotTables(PTName).DataPivotField.Orientation = xlRowField 'Gives data in row orientation
     
      PTWorkSheet.PivotTables(PTName).PivotFields("DESCRIPTION").CurrentPage="TOTAL GROSS SALES"

'Change DataFields to xlSum
      For Each pf In PTWorkSheet.PivotTables(PTName).DataFields
        If Not pf Is Nothing Then
            With pf
                .Function = xlSum
            End With
        End If
      Next

End Sub
VBEND

Let>ExcelFile=C:\Users\Christer\Desktop\Test Pivot.xlsx
Let>MaxRows=23

VBRun>CreatePivotTable,ExcelFile,MaxRows

[winstein]

This seems to work. Thank you!

Also, I noticed that xlSum, xlCount and xlAverage are assigned negative numbers. How do you get those numbers for those specific functions?

[hagchr]

Start Excel and the VB Editor (ALT+F11) and then press CTRL+G to open the Immediate Window. In here you can easily get the value of constants, just enter Print and constant and you get the value, eg

Print xlSum
gives -4157

Print xlDataField
gives 4

etc.