Re: VBA to Applescript
Re: VBA to Applescript
- Subject: Re: VBA to Applescript
- From: Paul Berkowitz <email@hidden>
- Date: Thu, 14 Feb 2008 11:18:24 -0800
- Thread-topic: VBA to Applescript
I do not have time to test now. Perhaps you can.
There seems to be a mistake (just one of many) in the Excel dictionary and
Reference. If you're lucky, the bug is only in the Reference and in the
descriptio of the dictionary, and not in the implementation. (Good luck.)
If you check the 'set subtotals' command, you'll see that it lists both an
optional parameter 'subtotals index' - which is an enumeration (automatic
subtotal, average subtotal....etc.), and also a required parameter 'value' -
which is supposedly boolean - true/false. However the *description* of
'value', and the Reference - instead list the enumeration again, but assign
integers to the enumeration constants (1,2,3,4...etc.). In VBA, enumerations
almost always have integer equivalents like that, and they usually hold also
in AppleScript, even where not documented. That's by the way.
Now, if it's just the description that's wrong, and the 'value' parameter
really is a boolean, you should be able to do what you want. If the
implementation is screwed, you won't be able to. It's worth a try.
The other thing you have to bear in mind is that in VBA, there are
Collections (what in AppleScript would be a list of 'every element' of some
class) that have fixed, custom values that cannot be altered, sort of like a
'read-only' element. That's a gap in AppleScript - in Applescript, anything
defined as an element of a class must be class itself, and can ne created
with whatever values you want for its various properties. So there are a lot
of such Collections in Excel and Word and PPT that could not be translated
to AS as elements of a class. Instead, their class is in a sort of limbo not
in the object hierarchy, and are only available as results of special
commands. Similarly, some VBA Properties (usually an Object in their on
right) are just enumerations in AppleScript.
One such is subtotals. As you have discovered, there is no such 'subtotal'
element of a pivot field in AppleScript. There can't be - because you can't
create your own varieties of subtotals - they're fixed. Instead, they are an
enumeration - 'subtotal index' of the 'get subtotals' command and the 'set
subtotals' command. For the latter, the enumeration is even spelled out both
as constants (automatic subtotal, average subtotal, etc. ) and as numbers
(1,2,3,...etc.).
You'll probably need to spell out each one, by name, and set the value of
each one to false (and hope that that works, if value really is boolean).
You cannot, as in your VBA example, to a repeat loop because there's no
collection of subtotal elements. Or you can create your own list, and loop
through that.
I'm a little suspicious of your VBA example I that it first sets each
subtotal to True and then immediately to False. Why do that? Why not just
set it to False? It may be that it first has to be set to True to 'create'
it, otherwise there might be an error. So you might have to do something
similar in AppleScript, I wouldn't know without testing.
If you're lucky, you should be able to do this:
(You'll find that 'value false' compiles as 'without value')
tell application "Microsoft Excel"
repeat with i from 1 to (count pivot tables of active sheet)
set pt to pivot table i of active sheet
repeat with j from 1 to (count pivot fields of pt)
try
set subtotals subtotal index 1 value false
end try
try
set subtotals subtotal index 2 value false
end try
try
set subtotals subtotal index 3 value false
end try
try
set subtotals subtotal index 4 value false
end try
try
set subtotals subtotal index 5 value false
end try
try
set subtotals subtotal index 6 value false
end try
try
set subtotals subtotal index 7 value false
end try
try
set subtotals subtotal index 8 value false
end try
try
set subtotals subtotal index 9 value false
end try
try
set subtotals subtotal index 10 value false
end try
try
set subtotals subtotal index 11 value false
end try
try
set subtotals subtotal index 12 value false
end try
end repeat
end repeat
end tell
If this doesn't work, try it instead with the enumerated values 'automatic
subtotal', 'average subtotal', etc. (but no quotes) that you'll find in the
'set subtotals subtotal index' enumeration.
However if it *does work*, you might be able to also get it to work much
more compactly like this:
tell application "Microsoft Excel"
repeat with i from 1 to (count pivot tables of active sheet)
set pt to pivot table i of active sheet
repeat with j from 1 to (count pivot fields of pt)
repeat with k from 1 to 12
try
set subtotals subtotal index k without value
end try
end repeat
end repeat
end repeat
end tell
And just in case nothing works, try what's in your VBA sample, setting first
to true before false (seems silly, but who knows?)
Compact version:
tell application "Microsoft Excel"
repeat with i from 1 to (count pivot tables of active sheet)
set pt to pivot table i of active sheet
repeat with j from 1 to (count pivot fields of pt)
repeat with k from 1 to 12
try
set subtotals subtotal index k with value
set subtotals subtotal index k without value
end try
end repeat
end repeat
end repeat
end tell
Remove the try/end try with just one type to see what sort of error you get
if nothing works. Maybe do that as a first step.
Happy testing.
--
Paul Berkowitz
> From: Steve Digby <email@hidden>
> Date: Thu, 14 Feb 2008 12:05:29 +0000
> To: <email@hidden>
> Subject: VBA to Applescript
>
> Hi,
>
> Does anyone know the Applescript equivalent of the Vba code below.
>
>
>
> Sub NoSubtotals()
> 'turns off subtotals in pivot table
> '.PivotFields could be changed to
> '.RowFields or .ColumnFields
> Dim pt As PivotTable
> Dim pf As PivotField
> On Error Resume Next
> For Each pt In ActiveSheet.PivotTables
> For Each pf In pt.PivotFields
>
> pf.Subtotals(1) = True
> pf.Subtotals(1) = False
> Next pf
> Next pt
>
> End Sub
>
> Any help, greatly appreciated.
>
> Steve Digby
>
>
>
>
>
> This message and any attachments are intended solely for the addressees and
> are confidential. If you receive this message in error, please delete it and
> immediately notify the sender.
> The Chrysalis Group cannot accept liability for any statements, views or
> opinions made which are clearly the sender's own and not expressly made on
> behalf of the Chrysalis Group.
> The contents may contain information which is confidential and may also be
> privileged. It may not be copied, used or disclosed without authorisation.
>
> Chrysalis Group plc
> Registered Office: The Chrysalis Building, 13 Bramley Road, London, W10 6SP
> Registration Number: 946978 England
> _______________________________________________
> 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
>
_______________________________________________
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