Scripting Excel with Python via appscript

[CRP]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi,
has anyone else here used Python as a scripting language? One of the (maybe unintended) consequences of MS making Applescript the only scripting possibility is that through AppScript (http://appscript.sourceforge.net/) one can also use Python (and Ruby and Cocoa/Objective C).
This allows for great new possibilities (just think of the many libraries available for Python), although I have three issues:
1) being used to Excel's VBA api I still have to adapt to the AS dictionary (and I am also finding many "idiosincracies", a few of which might be actual bugs, see my separate message on "get address of rows&quot
2) speed is not great, I think macros should be rewritten by minimizing access to table objects. in other words cycling over cells is not a good idea
3) I think there is no way to write user defined functions

Besides that, I really like being able to use "serious" scripting languages with Office, so I really do hope MS makes some effort to address the three issues I pointed out above, especially 1 and 3, instead of wasting time porting obsolete VBA to the next release of Office.

Cheers
Chris

 

[Jim Gordon Mac MVP]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor:
Intel Hi,
has anyone else here used Python as a scripting language? One of the
(maybe unintended) consequences of MS making Applescript the only
scripting possibility is that through AppScript
(http://appscript.sourceforge.net/) one can also use Python (and Ruby
and Cocoa/Objective C).
This allows for great new possibilities (just think of the many
libraries available for Python), although I have three issues:
1) being used to Excel's VBA api I still have to adapt to the AS
dictionary (and I am also finding many "idiosincracies", a few of which
might be actual bugs, see my separate message on "get address of rows")
2) speed is not great, I think macros should be rewritten by minimizing
access to table objects. in other words cycling over cells is not a good
idea
3) I think there is no way to write user defined functions

Besides that, I really like being able to use "serious" scripting
languages with Office, so I really do hope MS makes some effort to
address the three issues I pointed out above, especially 1 and 3,
instead of wasting time porting obsolete VBA to the next release of Office.

Cheers
Chris

Hi Chris,

The object model for Word, Excel and PowerPoint is based around VBA.
Some would argue that VBA is one of the most used programming languages,
although I don't know an objective way to quantify that. Certainly VBA
is used a lot. Calling VBA obsolete does not make a lot of sense to me.
Entourage scripting is entirely via VBA, so the AppleScript story for
Entourage does not involve VBA at all. We'll have to wait and see about
Outlook 2011.

It isn't a matter of porting when it comes to VBA in Mac Office. The
problem was that Apple switched from PowerPC processors to Intel
processors. The Mac VBA compiler was written for PowerPC processors. A
new compiler for Intel processors has been created and will debut in
Office 2011 later this year.

In Word, Excel, and PowerPoint, Applescript is still able to manipulate
the object model in 2008. I have little doubt AppleScript will continue
to be supported in 2011, as this is already in place. We won't be able
to do speed comparisons until Office 2011 comes out later this year, but
I would expect that if something (Python, Objective C, whatever) has to
tell AppleScript to tell Office to do something - that will take longer
than just having VBA do that same thing. Just the same, the fact that
you can pass variables and include AppleScript syntax in VBA expands the
capabilities of VBA. And vice-versa, the ability to command the Office
object model with languages such as Python and Objective C provide
additional capabilities and offer a strong argument for programming
Office on the Mac - even if speed isn't super perky. In many cases speed
is less of an issue than "can it be done."

If you are more comfortable in a different language and can still get
the Office object model to do what you need it to do, by all means
script away. The more the merrier. Blog about it. Post your conquests to
the newsgroups. Shout about it from the treetops. It's all good!

-Jim

 

[CRP]

Jim,
thanks for your reply. I really do think that, despite the userbase, the really friendly ide (windows only though) and the good speed (again, windows only, and VBA on mac was slower than the same on windows), VBA should go away as soon as possible. In fact, MS is already moving in that direction with .Net, which, as you may know, supports many different scripting languages (including, I think, Python).

So I will reformulate my (admittedly naive) rant:
Dear Microsoft, VBA is all good and well, but let's move forward on the Mac as well. Applescript is fine, but give us access to more scripting languages and allow us to write user defined functions in all of these.

About Office's Applescript dictionary being based on VBA's object model: that may be true, though there are enough differences to confuse or at least annoy new users. Just think about ".cells(2,3)" versus "cell 3 of row 2", or "font object" instead of "font".
And there are bugs as well. Try this:
 get address of rows of range "B13"

Chris

 

[John McGhie]

Just to weigh-in here: there are a couple of misapprehensions in your post.

1) VBA is not going "anywhere". Microsoft has announced that VBA will
receive on-going support and development for the foreseeable future. The
next version of Microsoft Office on the Mac is getting VBA 6.5, the same
version used by Office on the PC.

VBA (Visual Basic for Applications) was developed for the purpose of
automating an application from within that application. Code you write in
VBA becomes part of the Application's object model, which means it is much,
much simpler to write because it starts off on the right page, knowing that
"This is an Excel document" and knowing what an Excel Document can contain
and what those things mean.

2) VB.Net is designed to create programs that can (don't have to...) call
the operating system and other applications to do bits of what they want.
It sits outside both the applications and the operating system, and is thus
much more complex to write, because you have to declare and retrieve all the
properties of things.

Because it is outside the application, VB.Net is a smaller language that
does not include all the bells and whistles in VBA. But the bits of VBA
that are included are currently at version 6.5 also.

While VB.Net shares the same syntax as VBA, it is NOT the same language, and
changes are required to port code written in one to the other.

The .Net languages are designed to create distributed applications that
employ modules on multiple computers to do their business. They don't have
to, but if you don't need this, you would question the choice of VB.Net
because you'll get there faster with less code in VBA. In the same way as
VBA "can" automate across multiple applications, on multiple computers, but
if you need to do a lot of this you would be better off in VB.Net.

3) The Microsoft Visual Studio Development Environment enables a programmer
to work in whichever language he chooses. Visual Studio contains a compiler
and a linker that enables it to directly address the Application Programming
Interface of any program that has one (including Windows).

Vendors can create extensions to Visual Studio to support any language they
wish. If you want ObjectiveC, you simply need to create a Language Service
for ObjectiveC and plug it in to Visual Studio.

4) Theoretically, any application written in Visual Studio Tools for
Microsoft Office System (VSTO) should run unchanged in Office Mac 2011 when
it comes out.

In practice, I am sure it will turn into the usual wrestling-match needed to
get code running cross-platform. Nowhere near as bad as Java's "Write once,
test EVERYWHERE" but don't expect "Plug'N'Play" either

On the other hand, anything written in Xcode won't run on Windows. At all.
Which rather limits the available marketplace for application developers.

Hope this helps

Jim,
thanks for your reply. I really do think that, despite the userbase, the
really friendly ide (windows only though) and the good speed (again, windows
only, and VBA on mac was slower than the same on windows), VBA should go away
as soon as possible. In fact, MS is already moving in that direction with
.Net, which, as you may know, supports many different scripting languages
(including, I think, Python).

So I will reformulate my (admittedly naive) rant:
Dear Microsoft, VBA is all good and well, but let's move forward on the Mac as
well. Applescript is fine, but give us access to more scripting languages and
allow us to write user defined functions in all of these.

About Office's Applescript dictionary being based on VBA's object model: that
may be true, though there are enough differences to confuse or at least annoy
new users. Just think about ".cells(2,3)" versus "cell 3 of row 2", or "font
object" instead of "font".
And there are bugs as well. Try this:
get address of rows of range "B13"

Chris

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

 

[Jim Gordon Mac MVP]

Jim,
thanks for your reply. I really do think that, despite the userbase, the
really friendly ide (windows only though) and the good speed (again,
windows only, and VBA on mac was slower than the same on windows), VBA
should go away as soon as possible. In fact, MS is already moving in
that direction with .Net, which, as you may know, supports many
different scripting languages (including, I think, Python).

So I will reformulate my (admittedly naive) rant:
Dear Microsoft, VBA is all good and well, but let's move forward on the
Mac as well. Applescript is fine, but give us access to more scripting
languages and allow us to write user defined functions in all of these.

About Office's Applescript dictionary being based on VBA's object model:
that may be true, though there are enough differences to confuse or at
least annoy new users. Just think about ".cells(2,3)" versus "cell 3 of
row 2", or "font object" instead of "font".
And there are bugs as well. Try this:
get address of rows of range "B13"

Chris

Hi Chris,

First, upon re-reading my first reply I realize there is a giant typo
that I need to correct. I said that Entourage uses VBA. It does not.
Entourage is scripted via Applescript only.

As John pointed out, many Macs are deployed in a mixed Mac/Windows
environment, so the demand for write once deploy across the board is
pretty high. Hence the demand for VBA is high.

But now that large Mac-only installations are becoming commonplace, the
necessity for cross-platform implementation in such an installation is
not a factor. Creating Mac-only automation solutions is just fine. The
Mac-only market is large and growing, so by all means create and deploy
the solutions you can create. I sell a PowerPoint add-in that is
Mac-only and it does quite well. I try to encourage any developer to
create wonderful Mac only solutions. These will encourage more folks to
switch to Mac.

I'm not a software language snob. To me, the language is just a means to
an end. Certainly there are syntax, editing, compiling, and other
factors to consider, but in the end if whatever you did gets the job
done, be happy. The fastest is binary code, but almost no one writes
binary apps these days. If you are comfortable with Python and
Applescript, I say go for it!

If you encounter specific bugs in Office (via AppleScript or VBA in
particular), start a new thread in the newsgroup for each bug. Write a
character for character step-by-step set of instructions that a child
could follow so that others can try to reproduce the problem. Address
one problem in each thread.

Actually, someone (probably you) posted just such a thread and it was
picked up by XinXin, who is a Product Manager at Microsoft. Things do
get acted upon by Microsoft.

-Jim