Re: Excel: hide multiple columns with Applescript
Re: Excel: hide multiple columns with Applescript
- Subject: Re: Excel: hide multiple columns with Applescript
- From: Cameron Knowlton <email@hidden>
- Date: Sun, 20 Mar 2011 11:02:44 -0700
Thanks, David, that was insightful; I wouldn't have though to use a column range. Unfortunately, that doesn't quite do it:
set entireColumns to entire column of pivotColumns
set hidden of entireColumns to true
I'm not trying to hide *spreadsheet columns*, I'm trying to made *pivot columns* not visible.
The critical difference is that the *all* data in the affected *spreadsheet columns* gets hidden, rather than just hiding the *pivot columns* of a particular pivot table.
Adjusting this to set VISIBLE to false (rather than HIDDEN to true), I get this error:
set visible of entireColumns to 0
-- Microsoft Excel got an error: Can't set visible of entire column of column range of pivot table "myPivotTable" of worksheet "mySheet" of active workbook to 0.
I even tried to follow your example by addressing all the pivot items of the table, but ended up just hanging Excel (and Script Debugger):
set entirePivotItems to pivot items of pivot field "Date"
-- hang Excel, force quit
-- 'every pivot item' and ''
Incidentally, this [corrected] line that you provided ends up with the exact same error I was having originally:
set pivotTable to pivot table "myPivotTable" of currentSheet
tell pivotTable
set hidden of entire column of range ("$D:$J") to true
end tell
-- AppleScript Runtime Error:
-- Microsoft Excel got an error: The object you are trying to access does not exist
I feel I'm very close here, but just not landing it.
Cameron Knowlton
At 11:44 AM +1300 11/03/20, David Wignall wrote:
>On 20 Mar 2011, at 7:04 AM, Cameron Knowlton wrote:
>
>> I've been working more on my Excel scripting, and hope someone here can help with an efficiency.
>>
>> Currently, I use a loop to hide excessive pivot table columns:
>
>This specifically hides all columns of a pivot table:
>
> set Pivot_Table to pivot table (1) of active sheet
> set Pivot_Columns to column range of Pivot_Table
> set Entire_Columns to entire column of Pivot_Columns
> set hidden of Entire_Columns to true
>
>More generally this will hide columns on any sheet:
>
> set hidden entire column of range ("$B:$J") to true
>
>--
>Dave
>
--
Cameron Knowlton
KnowledgeVine SEO Dashboards
email@hidden
P: 250.382.0226
http://www.knowledgevine.net
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden