Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a pivot table through VBA
Hi Valeria,
A couple of suggestions : 1. To delete Totals, there is no need to delete, it can be part of Table Options With ActiveSheet.PivotTables("PivotTable1") .ColumnGrand = False .RowGrand = False End With 2. The xlLastCell instruction can be tricky, it might be safer to use Range(Selection, Selection.End(xlDown)).Select HTH Cheers Carim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a pivot table through VBA
http://support.microsoft.com/kb/319832/en-us
INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic Might give you some insights in what could possibly be causing this error. I doubt anything will leap off the page for you, but your might be able to identify something similar. this isn't the only article that shows this error string. You might search the knowledge base for "The object invoked has disconnected from its clients" or the error -2147417848 -- Regards, Tom Ogilvy "Valeria" wrote in message ... Hi Tom, I get to select the pivot table, thanks, and I am cutting the data lines I don't want in the new workbook. Only I always get the automation error "the Object has disconnected from its clients". It looks like VBA does not recognize the active workbook anymore. Do you know how to avoid this? I am spending hours trying to get rid of this error but I am beginning to feel quite helpless! Thanks Kind regards -- Valeria "Tom Ogilvy" wrote: the properties TableRange1 and TableRange2 should allow you to select the pivot Table. TableRange1 excludes PageFields and TableRange2 includes them. Activesheet.PivotTables(1).TableRange2.Select -- Regards, Tom Ogilvy "Valeria" wrote: Hello, unfortunately for some reason none of the 2 solutions work: on the pivot table, the fact of adding or deselecting the grand totals makes no change in the table. What could be the cause? On the selection, the xldown selects only half of the pivot table vertically and only on the first column... Thanks anyway! Kind regards -- Valeria "Carim" wrote: Hi Valeria, A couple of suggestions : 1. To delete Totals, there is no need to delete, it can be part of Table Options With ActiveSheet.PivotTables("PivotTable1") .ColumnGrand = False .RowGrand = False End With 2. The xlLastCell instruction can be tricky, it might be safer to use Range(Selection, Selection.End(xlDown)).Select HTH Cheers Carim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a pivot table through VBA
Hi Tom,
thanks. I have tried very unscientifically to copy the very same code and paste it on a new workbook and for any reason it does work without errors... Thanks again, Kind regards, -- Valeria "Tom Ogilvy" wrote: http://support.microsoft.com/kb/319832/en-us INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic Might give you some insights in what could possibly be causing this error. I doubt anything will leap off the page for you, but your might be able to identify something similar. this isn't the only article that shows this error string. You might search the knowledge base for "The object invoked has disconnected from its clients" or the error -2147417848 -- Regards, Tom Ogilvy "Valeria" wrote in message ... Hi Tom, I get to select the pivot table, thanks, and I am cutting the data lines I don't want in the new workbook. Only I always get the automation error "the Object has disconnected from its clients". It looks like VBA does not recognize the active workbook anymore. Do you know how to avoid this? I am spending hours trying to get rid of this error but I am beginning to feel quite helpless! Thanks Kind regards -- Valeria "Tom Ogilvy" wrote: the properties TableRange1 and TableRange2 should allow you to select the pivot Table. TableRange1 excludes PageFields and TableRange2 includes them. Activesheet.PivotTables(1).TableRange2.Select -- Regards, Tom Ogilvy "Valeria" wrote: Hello, unfortunately for some reason none of the 2 solutions work: on the pivot table, the fact of adding or deselecting the grand totals makes no change in the table. What could be the cause? On the selection, the xldown selects only half of the pivot table vertically and only on the first column... Thanks anyway! Kind regards -- Valeria "Carim" wrote: Hi Valeria, A couple of suggestions : 1. To delete Totals, there is no need to delete, it can be part of Table Options With ActiveSheet.PivotTables("PivotTable1") .ColumnGrand = False .RowGrand = False End With 2. The xlLastCell instruction can be tricky, it might be safer to use Range(Selection, Selection.End(xlDown)).Select HTH Cheers Carim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Pivot Table Raw Data | Excel Worksheet Functions | |||
How I can create a Pivot table by selecting two Different Pivot | Excel Worksheet Functions | |||
Help with Selecting Ranges in Pivot Table | Excel Programming | |||
Pivot Table Selecting Items | Excel Programming | |||
Selecting Rows in Pivot Table | Excel Programming |