If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pivot Table Problems
I created a workbook (in Excel 97) that has 8 identical sheets in it and
another sheet with a pivot table to summarize the data on the 8 sheets. I added a button to run a macro to refresh the pivot table after the data was entered on the 8 sheets. I sent the file to several people to be completed monthly. The file needs to be saved with a new name each month to identify the report period. The users have various versions of Excel. When I opened the returned files, some were OK but for some, Excel reported that the pivot table was invalid and had been discarded. I recreated the pivot tables in Excel 2003 but still encounter the same problem. The problem doesn't seem to be related to the Excel version, however, because I'm having the same problem with files used only on machines with Excel 2003. I'm suspicious that it might be related to the changing file names and/or paths. I've had to recreate the pivot tables in these files repeatedly so I decided to try to automate the process. I recorded a macro while creating the pivot table. But I'm having 2 problems. When I look at the recorded code, it has the file name in it, as follows: CreatePivotTable TableDestination:="'[DAV 02-28-05.xls]Recap'!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 Is there any way to change the code so it doesn't have to be manually edited to change the file name every time it's run? The users also need to be able to refresh the pivot table to see the summary data so I need to make the macro work without editing. The second problem is that if the existing pivot table IS valid, the macro bombs because the pivot table already exists. Ideally my macro would just refresh the table if it still exists or recreate the table if does not exist but I haven't been able to figure out how to do that. I would appreciate any help. And I would like to thank all of you who have contributed so much of your time and knowledge to helping others use Excel. I have learned so much from reading this newsgroup! Thank you! Becky |
#2
|
|||
|
|||
You can remove the workbook name from the TableDestination string, and
the macro should work correctly. To test for a pivot table, you could use code similar to the following: '========================= On Error Resume Next Set pt = wksPivot.PivotTables(1) On Error GoTo 0 If pt Is Nothing Then ' code to build the pivot table Else pt.PivotCache.Refresh End If '==================== Rachel Gonsior wrote: I created a workbook (in Excel 97) that has 8 identical sheets in it and another sheet with a pivot table to summarize the data on the 8 sheets. I added a button to run a macro to refresh the pivot table after the data was entered on the 8 sheets. I sent the file to several people to be completed monthly. The file needs to be saved with a new name each month to identify the report period. The users have various versions of Excel. When I opened the returned files, some were OK but for some, Excel reported that the pivot table was invalid and had been discarded. I recreated the pivot tables in Excel 2003 but still encounter the same problem. The problem doesn't seem to be related to the Excel version, however, because I'm having the same problem with files used only on machines with Excel 2003. I'm suspicious that it might be related to the changing file names and/or paths. I've had to recreate the pivot tables in these files repeatedly so I decided to try to automate the process. I recorded a macro while creating the pivot table. But I'm having 2 problems. When I look at the recorded code, it has the file name in it, as follows: CreatePivotTable TableDestination:="'[DAV 02-28-05.xls]Recap'!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 Is there any way to change the code so it doesn't have to be manually edited to change the file name every time it's run? The users also need to be able to refresh the pivot table to see the summary data so I need to make the macro work without editing. The second problem is that if the existing pivot table IS valid, the macro bombs because the pivot table already exists. Ideally my macro would just refresh the table if it still exists or recreate the table if does not exist but I haven't been able to figure out how to do that. I would appreciate any help. And I would like to thank all of you who have contributed so much of your time and knowledge to helping others use Excel. I have learned so much from reading this newsgroup! Thank you! Becky -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Thank you, Debra. Your suggestions are just what I needed. I've got it
working now. "Debra Dalgleish" wrote in message ... You can remove the workbook name from the TableDestination string, and the macro should work correctly. To test for a pivot table, you could use code similar to the following: '========================= On Error Resume Next Set pt = wksPivot.PivotTables(1) On Error GoTo 0 If pt Is Nothing Then ' code to build the pivot table Else pt.PivotCache.Refresh End If '==================== Rachel Gonsior wrote: I created a workbook (in Excel 97) that has 8 identical sheets in it and another sheet with a pivot table to summarize the data on the 8 sheets. I added a button to run a macro to refresh the pivot table after the data was entered on the 8 sheets. I sent the file to several people to be completed monthly. The file needs to be saved with a new name each month to identify the report period. The users have various versions of Excel. When I opened the returned files, some were OK but for some, Excel reported that the pivot table was invalid and had been discarded. I recreated the pivot tables in Excel 2003 but still encounter the same problem. The problem doesn't seem to be related to the Excel version, however, because I'm having the same problem with files used only on machines with Excel 2003. I'm suspicious that it might be related to the changing file names and/or paths. I've had to recreate the pivot tables in these files repeatedly so I decided to try to automate the process. I recorded a macro while creating the pivot table. But I'm having 2 problems. When I look at the recorded code, it has the file name in it, as follows: CreatePivotTable TableDestination:="'[DAV 02-28-05.xls]Recap'!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 Is there any way to change the code so it doesn't have to be manually edited to change the file name every time it's run? The users also need to be able to refresh the pivot table to see the summary data so I need to make the macro work without editing. The second problem is that if the existing pivot table IS valid, the macro bombs because the pivot table already exists. Ideally my macro would just refresh the table if it still exists or recreate the table if does not exist but I haven't been able to figure out how to do that. I would appreciate any help. And I would like to thank all of you who have contributed so much of your time and knowledge to helping others use Excel. I have learned so much from reading this newsgroup! Thank you! Becky -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
You're welcome. Thanks for letting me know that it helped.
Rachel Gonsior wrote: Thank you, Debra. Your suggestions are just what I needed. I've got it working now. "Debra Dalgleish" wrote in message ... You can remove the workbook name from the TableDestination string, and the macro should work correctly. To test for a pivot table, you could use code similar to the following: '========================= On Error Resume Next Set pt = wksPivot.PivotTables(1) On Error GoTo 0 If pt Is Nothing Then ' code to build the pivot table Else pt.PivotCache.Refresh End If '==================== Rachel Gonsior wrote: I created a workbook (in Excel 97) that has 8 identical sheets in it and another sheet with a pivot table to summarize the data on the 8 sheets. I added a button to run a macro to refresh the pivot table after the data was entered on the 8 sheets. I sent the file to several people to be completed monthly. The file needs to be saved with a new name each month to identify the report period. The users have various versions of Excel. When I opened the returned files, some were OK but for some, Excel reported that the pivot table was invalid and had been discarded. I recreated the pivot tables in Excel 2003 but still encounter the same problem. The problem doesn't seem to be related to the Excel version, however, because I'm having the same problem with files used only on machines with Excel 2003. I'm suspicious that it might be related to the changing file names and/or paths. I've had to recreate the pivot tables in these files repeatedly so I decided to try to automate the process. I recorded a macro while creating the pivot table. But I'm having 2 problems. When I look at the recorded code, it has the file name in it, as follows: CreatePivotTable TableDestination:="'[DAV 02-28-05.xls]Recap'!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 Is there any way to change the code so it doesn't have to be manually edited to change the file name every time it's run? The users also need to be able to refresh the pivot table to see the summary data so I need to make the macro work without editing. The second problem is that if the existing pivot table IS valid, the macro bombs because the pivot table already exists. Ideally my macro would just refresh the table if it still exists or recreate the table if does not exist but I haven't been able to figure out how to do that. I would appreciate any help. And I would like to thank all of you who have contributed so much of your time and knowledge to helping others use Excel. I have learned so much from reading this newsgroup! Thank you! Becky -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |
(blank) displayed in pivot table | Keith | Worksheet Functions | 1 | February 28th, 2004 02:53 AM |
Pivot Table Too Many Rows Or Colums | Joe | Worksheet Functions | 0 | October 9th, 2003 05:17 PM |
Pivot table from ODBC database problems | Andy Cunningham | Setting up and Configuration | 0 | September 26th, 2003 02:10 PM |