Re: Excel: hide multiple columns with Applescript
Re: Excel: hide multiple columns with Applescript
- Subject: Re: Excel: hide multiple columns with Applescript
- From: David Wignall <email@hidden>
- Date: Tue, 19 Apr 2011 23:04:03 +1200
On Tue, Apr 19, 2011 at 04:59, Cameron Knowlton <email@hidden> wrote:
> Hi Paul, thanks for the suggestion. This was the first thing I tried (it
> didn't work), but I know I was on the right path.
> At 11:24 PM -0700 11/04/17, Paul Berkowitz wrote:
>
> On 4/17/11 2:26 PM, "Cameron Knowlton" <email@hidden> wrote:
>
> Out of curiosity, I wasn't just missing the technique to address all of the
> pivot items simultaneously... it's just not possible, is it? i.e. It would
> be up to the Microsoft developers to have built in access code that they
> didn't, correct?
>
> Or, am I missing an obvious Applescript reference idiom here?
>
> I don't have a suitable example to test on myself. Have you tried this:
>
> tell pivotTable
> set pivotHideCount to (pivotItemCount - 5) -- number of columns
> to hide, beginning from the left (oldest)
> set visible of pivot items 1 thru pivotHideCount of pivot field
> "Date" to false
> end tell
Cameron,
It's the recalculation of the pivot table that's sucking up the time.
This is *independent* of the worksheet calculation engine, so setting
worksheet calculation to manual achieves nothing useful.
I tested removing the field, and then hiding items:
set pivotTable to pivot table "myPivotTable" of active sheet
tell pivotTable
set pivotField to pivot field "Date"
set pivotHideCount to (count of pivot items of pivotField) - 5
set pivot field orientation of pivotField to orient as hidden
tell pivotField
repeat with pivotIndex from 1 to pivotHideCount
set visible of pivot item pivotIndex to 0
end repeat
end tell
set pivot field orientation of pivotField to orient as column field
end tell
and spent a minute or so watching the status bar alternate between
"calculating pivot table" and "Ready" despite the field not being
displayed in the table. As far as I can see the pivot table
calculation engine is not exposed to Applescript.
Just for jollies I went to Windows and recorded a VBA macro. The
relevant code was (from memory, something like)
With PivotField ("Order Date")
Set .PivotItem(1).Visible to False
Set .PivotItem(2).Visible to False
[... ad infinitum...]
End With
Based on this I think the code you have is pretty much as tidy as it
can be and there is little more you can do about the time it takes the
procedure to complete. I don't know your situation but the fastest way
to get the job done here is for the user (me) to click appropriately
in Excel...
--
Dave
_______________________________________________
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