Using VBA to add a calculated column to a pivot table in excel

[TinaS]

Good Morning!!

I currently am using Access 2003 vba to export data to an excel pivot table via automation. The table creation works great - and I have added a column to the end of the table to calculate percentages on 2 of the rows (it looks like this):


ClosedBy (All)
Closed (All)

Count of TRRID Timely
OpenMonthYear No Yes Grand Total Percent Worked Timely
04-Apr-2011 1 1 100%
05-May-2011 194 3176 3370 94%
06-Jun-2011 147 2537 2684 95%
07-Jul-2011 307 1613 1920 84%
Grand Total 648 7327 7975 92%



The problem is, since I am creating the last column in code with the percentages, if the user tries to manipulate the data in the pivot table, the percentages go away.

I haven't worked much with pivot tables, so don't know if there is a way to have a percentage column actually calculate accordingly in the pivot table? if there is a way to do it, I imagine I can program it (just need to figure out how ) I don't think adding the column to the query in access would work, as I don't believe it exports the formulas -( I could be wrong ) so that won't work. Anyone have any ideas?

THANK YOU!!!!

 

[SkipVought]

Hi,

How is your percent calculated with what fields?

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

 

[TinaS]

here's a section of the code. let me know if this helps..


If InStr(1, sFullPath, "TRRTimelyPivotTableData_AsOf_", 1) Then
With oXL.ActiveWorkbook.Worksheets("qryBasicReportInfo-Pivot").Range("E5")
.Value = "Percent Worked Timely"
End With

With oXL.ActiveWorkbook.Worksheets("qryBasicReportInfo-Pivot").Range("E6")
.Value = "=SUM(C6/D6)"
.Style = "Percent"
.AutoFill Destination:=oXL.ActiveWorkbook.Worksheets("qryBasicReportInfo-Pivot") _
.Range("E6:E" & PTLastRow)
End With


oXL.ActiveWorkbook.Save




End If

 

[SkipVought]

Why...
[tt]
=SUM(C6/D6)
[/tt]
There is NOTHING to SUM!!!

All you have is a quotient!

What are C6 & D6 anyhow?

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

 

[TinaS]

the program is working - currently i am forcing the formula into the last column of the pivot table. I know that column E is the last column, the row is determined by a subroutine that finds the last row of the pivot table. Since I know the column and number of rows, I am autopopulating the formula to calculate the percentages. My earlier post shows the result. It is working fine - to add the column and calculate.

My question is, though, if the user changes any of the values in the pivot table, my calculated column will be useless because the pivot table format may change. What I am wondering is, if there is a way to have a pivot table contain calculated cells? if not, then I'll have to keep what I have as it is working fine - as long as the user doesn't manipulate the pivot table.

Can we add a calculated column to a pivot table? that when the user changes the value of a pivot table it auto adjusts? that part I am not sure of.

THANK YOU!

 

[TinaS]

Let me add that the code above is code that is added AFTER the pivot table is created - it manipulates the data in the sheet after the pivot table.

The code to generate the raw pivot table is thus:

objWB.Activate


With objWB.PivotCaches
With .Add(SourceType:=xlDatabase, SourceData:=strWrkSheet & "!R1C1:" & LastCell)
Set Pt = .CreatePivotTable(TableDestination:="", TableName:="AnalysisPivot", DefaultVersion:=xlPivotTableVersion10)
Pt.AddFields RowFields:=strRowFields, ColumnFields:=StrColumnFields, PageFields:=Array(strPageFields, "ClosedBy")
With Pt.PivotFields(strPivotFields)
.Orientation = xlDataField
.Function = xlCount
.Name = "Count of " & strPivotFields
.Position = 1
End With
With Pt.TableRange1
PTLastRow = .Rows.Count + 3

'MsgBox LastRow
End With
End With
End With





I've done some searching and found an older post by you that has this:
pt.CalculatedFields.Add "delta", pt.PivotFields(i).Name & " - " & pt.PivotFields(i + 1).Name

for another similar question. I will try to play with this and see if I can get the calculated field into the pivot table so I can omit the post process to add a percentage column.

If you have any other suggestions please let me know - otherwise I'll post after I have a chance to test this.

THANK YOU!!!

 

[SkipVought]

Check out the TableRange1 & TableRange2 propertied.

Using those, you can calculate thelast column of the table as

with YourPT.TableRange1
  FirstCol = .column
  LastCol = firstcol + .columns.count - 1
end with

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

 

[TinaS]

Thanks Skip. After playing with this for awhile, I am beginning to think what I want to happen is probably not possible.

Right now, I have columns in the pivot table that look like this
No Yes Grand Total

I created one outside the table that is called "Percent Worked Timely"


Ideally, i'd have liked to have had the column put inside the table, but from what I can tell, I don't think I can do that. Everytime I try to reference a column (and I don't have the experience to call it correctly) I get an error. If I try Timely(Yes), or Timely.[Yes] or Timely![yes] etc.. Nothing works. The only other option I can see is to reference the cells specifically, such as Timely(yes).cellnumber or something - but that would require to update each cell in the pivot which i wouldn't think is necessary.

I've gotten so far as getting my new calculated field to show up in the field list but it won't let me drag it anywhere.

At the risk of spending too much time on this, and other work not getting done, I'm going to have to table it. I have the workaround to show the percentages, I just don't like it, but don't know if I can get what I would like - which is to have the exact same thing INSIDE the pivot table versus outside.

Thanks for your help.

 

[SkipVought]

Try looking at the PivotTable > Tools > Formulas, and construct a formula within PT to use in aggregation.

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

 

[TinaS]

attempting to paste a copy of the table here:

 

[SkipVought]

You must paste TEXT.

use TGML TT Tags (Search for TGML on this page to find the LINK to understand what to do)

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

 

[Andrzejek]

FYI - use TGML TT Tags

[tt][blue][ignore][tt][/ignore][/blue]
OpenMonthYear No Yes Grand Total Percent Worked Timely
04-Apr-2011 1 1 100%
05-May-2011 194 3176 3370 94%
06-Jun-2011 147 2537 2684 95%
07-Jul-2011 307 1613 1920 84%
Grand Total 648 7327 7975 92%
[blue][ignore][/tt][/ignore][/blue][/tt]

Have fun.

---- Andy

 

[TinaS]

thank you for your help, unfortunately, I am nearly 5 hours into researching this and am only gaining tiny steps in the progress. I really would like it to work, but I am beginning to think I will have to give up and come back to it in the future (maybe weeks or months away). I just can't afford to spend days researching a small issue like this.

If there is anyway someone can help with the syntax that would be great. (please don't tell me to look here or there, as I've spent 5 hours 'looking' everywhere)

I've been working in access for many years on the vba side, but have very little experience calling excel. I've done some, but not a lot. I have so far been able to get a calculated field into the table, but it is not the way it should be. (it does not show up as a column but it is in there) I have a calculated field, but could not get the formula to work in vba the way it worked in excel so am trying to identify the columns numerically (which I can't even test if I have the right column because I can't see a calculation yet.)

With objWB.PivotCaches
With .Add(SourceType:=xlDatabase, SourceData:=strWrkSheet & "!R1C1:" & LastCell)
Set Pt = .CreatePivotTable(TableDestination:="", TableName:="AnalysisPivot", DefaultVersion:=xlPivotTableVersion10)
Pt.AddFields RowFields:=strRowFields, ColumnFields:=StrColumnFields, PageFields:=Array(strPageFields, "ClosedBy")
With Pt.PivotFields(strPivotFields)
.Orientation = xlDataField
.Function = xlCount
.Name = "Count of " & strPivotFields
.Position = 1
End With
If InStr(1, strPath, "TRRTimelyPivotTableData_AsOf_", 1) Then
Pt.CalculatedFields.Add Name:="Percent Timely2", Formula:="=" & Pt.PivotFields(4).Name & " / " & Pt.PivotFields(5).Name
With Pt.PivotFields("Percent Timely2")
.Orientation = xlDataField
'.Function xlSum
.Position = 7
'.NumberFormat = "#0.0%"
.Name = "Percent Timely"
End With



the above is a jumbled mess at the moment. I just can't believe this is this difficult to do. The excel formula of :

= Timely[yes]/( Timely[Yes] + Timely[no]

only threw errors in vba - it worked when I inserted in the spreadsheet but would not work in the vba.

I was really hoping, if someone had done this before, for a couple of simple lines for reference, but am worried now for the time I have lost researching it.

Thanks,

Tina

 

[SkipVought]

Did you try doing it IN EXCEL, on the SHEET without VBA? THAT is where you ought to be spending your time to find this feature and use it.

THEN and ONLY THEN, after you have perfected the MANUAL PROCESS of creating a formula and using it in the PT as a Pivot Field, THEN turn on your macro recorder and record doing that learned process. Observe your recorded code.

I have learned quite a bit using this simple technique!

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

 

[TinaS]

that is the table the code is creating at the moment. the percent timely field is not showing in the right location at all, and isnt giving me any values (which could be because the column references are wrong)

 

[SkipVought]

I cannot help you if you will not answer my question.

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

 

[TinaS]

Actually - I had not tried that yet - mainly because I have only done the macro's in excel maybe 2 times before, and since I haven't used them much, it simply didn't occur to me. I didn't answer right away because I was trying that option first, so I could reply with a result.

Here's the macro result
Range("B4").Select
ActiveSheet.PivotTables("AnalysisPivot").PivotFields("Timely").CalculatedItems. _
Add "PercentTimely2", "=Yes /(No+Yes)", True


no matter what, when I run it again in excel, or in VBA, it gives me "ambiguous Item name" so I am getting the same error I was before.

 

[TinaS]

FINALLY! After a day of banging my head against the wall - here is the elusive Pain in the.. you know what.. single little line of code (if I could break something now I would...)

Pt.PivotFields("Timely").CalculatedItems.Add "Percent Timely", "= timely[yes] /(timely[No]+timely[Yes])", True


it's beautiful. the only thing left for me now is to convert the numbers to percentages, which I am pretty sure will be much simpler.

Thanks for your help. I apologize if I was getting a little testy, this was REALLY starting to get to me.

Thanks again

 

[TinaS]

Well it's working- but now My Grand totals are off.. LOL.. oy.

[tt]
Count of TRRID Timely
OpenMonthYear No Yes Percent Timely Grand Total
04-Apr-2011 1 100 101
05-May-2011 194 3176 94.24 3464.24
06-Jun-2011 147 2537 94.52 2778.52
07-Jul-2011 307 1613 84.01 2004.01
Grand Total 648 7327 372.77 8347.77
[/tt]


** i did use the [ tt] and [ /tt] without the spaces here but it didn't reformat. I'm out for the night - will readdress in the morning

thanks again

 

[SkipVought]

So glad that you figured it out.

The percent is just a FORMAT. You want to leave your values as quotients and format the FIELD as percent displaying so many decimals.

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