Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code to update existing Pivot Table
I have two worksheets in the same workbook: "PIR-DT MTH" and "PIR-D CAT" In "PIR-DT MTH", in cell "E3" there is a string for an range value, fo example "C4:L21" In "PIR-DT CAT" there is a Pivot Table called "PIR1DTCAT" with Ro Field "PIR-DTCAT" and Data Field "IR1 DT TIME" (there is no Colum Field). The Table uses the range value mentioned above to capture th source data for the table The range value in "E3" on "PIR-DT MTH" changes by different triggers the details of which are not important However, when the value in that particular cell changes, the Pivo Table in worksheet "PIR-DT CAT" must also update. So I have a macro, which is supposed to capture the new range value an update the pivot table. I tried the very same code on a sample workbook and it works just fine but when I test on the actual workbook, I get an error: The call to the function is as follows: Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT" "DTCAT", "1", "E3") The actual function is as follows (note I put the values that ar assigned to the internal variables in red for your reference): Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName A String, _ ObjWkSheetName As String, InfoType A String, _ IRNumber As String, RangeInfoCell A String) Dim myexcel As Object Dim myworkbook As Object Dim sourceworksheet As Object Dim objworksheet As Object Dim PivotTableName As String 'Dim PivotRowField As String 'Dim PivotDataField As String Dim PivotSourceData As String Set myexcel = GetObject(, "Excel.Application") 'Point to activ excel application Set myworkbook = Excel.Application.Workbooks("IRReports.xls" 'Point to the relevant workbook Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName 'Point to the relevant worksheet Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point t the relevant worksheet PivotTableName = "PIR" & IRNumber & InfoType '"PIR1DTCAT" PivotRowField = "PIR" & IRNumber & "-" & InfoType '"PIR-DTCAT" PivotDataField = "IR" & IRNumber & " DT TIME" '"IR1 DT TIME" If sourceworksheet.Range(RangeInfoCell).Value = "None" The '"C4:L21" MsgBox "You have to delete the Pivot Table" Else PivotSourceData = "'" & SourceWkSheetName & "'!" sourceworksheet.Range(RangeInfoCell).Value '"PIR-DT MTH'!C4:L21" objworksheet.PivotTables(PivotTableName).PivotTabl eWizar SourceType:=xlDatabase, SourceData:= _ PivotSourceData 'THIS IS THE LINE WHERE I GET THE ERROR ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False End If End Sub The error which occur on the line indicated above states: The PivotTable field name is not valid. To create a PivotTable report you must use data that is organized as a list with labeled columns. I you are changing the name of a PivotTable field, you must type a ne name for the field. I don't understand what is happening Now all of this can be avoided, if I could only figure out how t instead of putting the range reference like - 'PIR-DT MTH'!$C$4:$L$3 for the Pivot Table, put the value of the cell where the rang reference string is stored - =TEXT('PIR-DT MTH'!E3;) When I tried that I got an error Reference is not vali -- suzette ----------------------------------------------------------------------- suzetter's Profile: http://www.excelforum.com/member.php...nfo&userid=707 View this thread: http://www.excelforum.com/showthread.php?threadid=38935 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code to update existing Pivot Table
Hi Suzetter
Two things to try. First, with your existing set up make sure that all of the fields in your source table have headings. That error usually means there is a missing header. Maybe it got over written. That should fix it Second: You can use a named range as the source of your pivot table like this ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:="test"). _ CreatePivotTable TableDestination:="", TableName:="PivotTable3" Where "test" is a named range. Then you can write code to assign that name to whatever range is appropriate. Something like activecell.currentregion.name = "test" Refresh your table and you're good to go ActiveSheet.PivotTables("PivotTable3").RefreshTabl e regards, Mike "suzetter" wrote: I have two worksheets in the same workbook: "PIR-DT MTH" and "PIR-DT CAT" In "PIR-DT MTH", in cell "E3" there is a string for an range value, for example "C4:L21" In "PIR-DT CAT" there is a Pivot Table called "PIR1DTCAT" with Row Field "PIR-DTCAT" and Data Field "IR1 DT TIME" (there is no Column Field). The Table uses the range value mentioned above to capture the source data for the table The range value in "E3" on "PIR-DT MTH" changes by different triggers, the details of which are not important However, when the value in that particular cell changes, the Pivot Table in worksheet "PIR-DT CAT" must also update. So I have a macro, which is supposed to capture the new range value and update the pivot table. I tried the very same code on a sample workbook and it works just fine, but when I test on the actual workbook, I get an error: The call to the function is as follows: Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3") The actual function is as follows (note I put the values that are assigned to the internal variables in red for your reference): Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName As String, _ ObjWkSheetName As String, InfoType As String, _ IRNumber As String, RangeInfoCell As String) Dim myexcel As Object Dim myworkbook As Object Dim sourceworksheet As Object Dim objworksheet As Object Dim PivotTableName As String 'Dim PivotRowField As String 'Dim PivotDataField As String Dim PivotSourceData As String Set myexcel = GetObject(, "Excel.Application") 'Point to active excel application Set myworkbook = Excel.Application.Workbooks("IRReports.xls") 'Point to the relevant workbook Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName) 'Point to the relevant worksheet Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point to the relevant worksheet PivotTableName = "PIR" & IRNumber & InfoType '"PIR1DTCAT" PivotRowField = "PIR" & IRNumber & "-" & InfoType '"PIR-DTCAT" PivotDataField = "IR" & IRNumber & " DT TIME" '"IR1 DT TIME" If sourceworksheet.Range(RangeInfoCell).Value = "None" Then '"C4:L21" MsgBox "You have to delete the Pivot Table" Else PivotSourceData = "'" & SourceWkSheetName & "'!" & sourceworksheet.Range(RangeInfoCell).Value '"PIR-DT MTH'!C4:L21" objworksheet.PivotTables(PivotTableName).PivotTabl eWizard SourceType:=xlDatabase, SourceData:= _ PivotSourceData 'THIS IS THE LINE WHERE I GET THE ERROR ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False End If End Sub The error which occur on the line indicated above states: The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field. I don't understand what is happening Now all of this can be avoided, if I could only figure out how to instead of putting the range reference like - 'PIR-DT MTH'!$C$4:$L$32 for the Pivot Table, put the value of the cell where the range reference string is stored - =TEXT('PIR-DT MTH'!E3;) When I tried that I got an error Reference is not valid -- suzetter ------------------------------------------------------------------------ suzetter's Profile: http://www.excelforum.com/member.php...fo&userid=7078 View this thread: http://www.excelforum.com/showthread...hreadid=389355 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code to update existing Pivot Table
Believe it or not, I was trying the suggestion about the named range in the meantime, but I have run into other problems: I have 2 worksheets in the same workbook On worksheet "PIR-DT MTH" there is a cell (i.e. "E3") which contains a string value representing a range of cells, eg. "C4:L32" This range value changes due to specific triggers which aren't important In "PIR-DT MTH", there is a named range called "PIR1DB" with the same cell range that is stored in the cell "E3" On the other worksheet "PIR-DT CAT", there is a pivot table "PIR1DTCAT" which has it's data range defined as the named range "PIR1DB" on the previous worksheet So, the jist of the idea is that when the content in cell "E3" on "PIR-DT MTH" changes, I update the range that the workbook Name "PIR1DB" refers to When the named range is updated, the pivot table "PIR1DTCAT" will be refreshed to show data for the changed range I've toiled over this code over and over and over and all the variables show the correct values (using the VB Debug window) But for some strange reason, the line which is supposed to set a new range to the Name doesn't work at all The call to the function looks like this: Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3") The actual function looks like this (I have placed the actual values in comments at the end of the line so you can follow what is happening): Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName As String, _ ObjWkSheetName As String, InfoType As String, _ IRNumber As String, RangeInfoCell As String) Dim myexcel As Object Dim myworkbook As Object Dim sourceworksheet As Object Dim objworksheet As Object Dim PivotTableName As String Dim PivotSourceData As String Dim NameRef As String Set myexcel = GetObject(, "Excel.Application") 'Point to active excel application Set myworkbook = Excel.Application.Workbooks("IRReports.xls") 'Point to the relevant workbook Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName) 'Point to the relevant worksheet Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point to the relevant worksheet PivotTableName = "PIR" & IRNumber & InfoType ' PIR1DTCAT If sourceworksheet.Range(RangeInfoCell).Value = "None" Then ' C4:L33 'There is no downtime data, hence the pivot table will not be updated Else PivotSourceData = "='" & sourceworksheet.Name & "'!" & sourceworksheet.Range(RangeInfoCell).Value ' 'PIR-DT MTH'!C4:L33 NameRef = "PIR" & IRNumber & "DB" ' PIR1DB myworkbook.Names.Add NameRef, PivotSourceData 'This is the line which doesn't work objworksheet.PivotTables(PivotTableName).PivotCach e.Refresh myworkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False End If End Sub Changing the Named range doesn't work The source variable (i.e PivotSourceData) would have the correct information in but and after the line is executed and you check the source range for PIR1DB is says something completely different like "='PIR-DT MTH'!BJ9:BS38" I have no clue where it gets this from And everytime you run the code, the strange range changes Even when I hardcode the value (see below) , it still doesn't work myworkbook.Names.Add "PIR1DB", "='PIR-DT MTH'!C4:L33" This is just weird, the fact that hardcoding the values doesn't work means something is drastically wrong somwhere Am I using any predefined VB words? Is the sky falling? And yes all my columns have headings etc. This Excel is possessed, I know it :-) -- suzetter ------------------------------------------------------------------------ suzetter's Profile: http://www.excelforum.com/member.php...fo&userid=7078 View this thread: http://www.excelforum.com/showthread...hreadid=389355 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Adding to an existing Pivot Table | Excel Discussion (Misc queries) | |||
adding a pivot table to an existing worksheet- HELP!! | Excel Discussion (Misc queries) | |||
vb code to export data into existing Access table | Excel Programming | |||
viewing VBA behind existing pivot table | Excel Programming |