• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Best way to script Excel 2016 from Python
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Best way to script Excel 2016 from Python

  • Subject: Re: Best way to script Excel 2016 from Python
  • From: Felix Zumstein <email@hidden>
  • Date: Thu, 26 Mar 2015 01:51:14 +0100
Oh wow thanks for this thorough answer!

- I've done 1) although I don't trust in that solution...
- 2) seems to work quite nicely at first sight
- 4) seems to me like another easy and future proof way, but I ll go with 2) for now until I hit any roadblocks

the correction with aem also worked... 

thanks again!
Felix



On Wed, Mar 25, 2015 at 10:59 PM, has <email@hidden> wrote:
Felix Zumstein wrote:

> Office 2016 stopped working with the Python package appscript, see thread "New Office for Mac Beta and Applescript". Is PyObjC in connection with py-applescript the best way to deal with the situation going forward? Or are there any other options (that also work with MacOS < 10.10)?

 Assuming the work you're doing requires Excel automation and can't just be done with third-party Python modules (e.g. xlrd, xlwt, openpyxl) then you have various options, albeit none ideal:

1. Send a bug report to MS re. the busted `ascr/gdte` handler and hope they fix it. Pointing out that this bug affects AppleScript's Remote Apple Events support is probably the best way to make it relevant to them. I suspect the problem is apps with Carbon-based AE handlers setting the NSAppleScriptEnabled flag to true. [1] If that's the case (you can easily check Excel's Info.plist yourself), then all they'd need to do is set it back to false.

2. Stick with appscript [2], but using a static glue instead of fetching its dictionary dynamically. You'll need to export the dictionary from Excel 2011 using the dump() function as described earlier in the thread, then use that when creating an app object for Excel 2016. (Note: this assumes the dictionary in 2016 is unchanged. If it has changed, you'll need to hack Excel 2016's Info.plist yourself and - assuming that fixes the `ascr/gdte` behavior - dump the dictionary from that.)

3. Use OS X's Scripting Bridge framework (10.5+). SB is what would've replaced appscript, had it not been a pile of ass: missing and crippled features, application incompatibilities (especially for Carbon-based scriptable apps, which apparently the AS team doesn't consider important), crappy documentation and support, and obfuscation up the wazoo. You will definitely get compatibility problems with Excel [3], but depending on what you're doing you might get away with it.

4. Use py-applescript. That takes much of the pain out of calling into AppleScript from Python, and AppleScript is the one language that is guaranteed to work "correctly" with all scriptable apps, since all scriptable apps in the last 20+ years have been designed and tested to work with AppleScript.

5. Python->PyObjC->ASOC-AppleScript ought to work back to 10.6, but you might need to screw around a bit to create ASOC-based .scpt files that support older OS versions. Shane Stanley's your go-to guy for all ASOC questions here, should you decide to do that.

5. Curse out the current AppleScript team for being the reason we can't have nice things. Get a Windows box, which at least has the advantage of being *trustably* mediocre.



> With Office 2016, ASTranslate transforms this:
>
> tell application "Microsoft Excel"
>     set value of range ("A1") to 5
> end tell
>
> into this:
>
> app(u'Microsoft Excel').AS_newreference(app.elements('X117').byname(u'A1').property('DPVu')).set(5)
>
> but when I use this, it returns the following error:
> AttributeError: Unknown property, element or command: 'elements'

 Guessing you copied and pasted the displayed result. It's a cosmetic wart: aem objects' __repr__/__str__ methods don't include an `aem.` prefix when displaying their code representation; unfortunately, both appscript and aem modules export an attribute named `app`, so your copy-pasted code ends up trying to use appscript's app object instead of aem's app object. Change it to:

import aem

app(u'Microsoft Excel').AS_newreference(aem.app.elements('X117').byname(u'A1').property('DPVu')).set(5)

and it'll work. (The aem APIs are appscript's equivalent to AppleScript's raw chevron syntax, used when terminology is unavailable.)


HTH

has



[1] Which is a chain of wrongness all the way back to Apple's typically derpy decision not to [correctly] call it NSCocoaScriptingEnabled, thus confusing devs into thinking they should set it to true just to indicate their app is scriptable. They'd probably get away with it if their apps had SDEFs instead of AETEs, though SDEFs in turn introduce their own brokenness. [3]

[2] Obviously, other caveats to using appscript still apply: I can't guarantee that Apple and/or app vendors won't further bone it in future, which is why I can't in good conscience recommend it, even though it is the only competent technical option and something I still use heavily myself. The appscript status page (http://appscript.sourceforge.net/status.html) has more info, including a list of unresolved Radar tickets that block the release of a supported version which you can dupe if you like (not that I'm optimistic Apple will ever fix them).

[3] For example, four-char codes in Excel's AETE often contain null bytes, which SDEFs can't represent as ASCII 0 isn't allowed in XML, thereby breaking various keywords.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list      (applescript-users@lists.apple.com)
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
  • Follow-Ups:
    • Re: Best way to script Excel 2016 from Python
      • From: Christian Prinoth <email@hidden>
References: 
 >Re: Best way to script Excel 2016 from Python (From: has <email@hidden>)

  • Prev by Date: Re: Algorithms in Applescript
  • Next by Date: Help needed folder watch
  • Previous by thread: Re: Best way to script Excel 2016 from Python
  • Next by thread: Re: Best way to script Excel 2016 from Python
  • Index(es):
    • Date
    • Thread