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: Tue, 19 Apr 2011 09:47:35 -0700
Thanks for the feedback, Dave, and to everyone else. I'm definitely a lot further along than I was when I started.
This is (usually) a user-less process, but occasionally Excel craps out and I wake up to a stalled script. At least now it's *much* faster when I have to rerun it manually, and demonstrating the process no longer puts people to sleep.
thanks again everyone!
Cameron Knowlton
At 11:04 PM +1200 11/04/19, David Wignall wrote:
>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
--
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