Scripting Excel with Python via appscript

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

Hi, <br>
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). <br>
This allows for great new possibilities (just think of the many libraries available for Python), although I have three issues: <br>
1) being used to Excel's VBA api I still have to adapt to the AS dictionary (and I am also finding many &quot;idiosincracies&quot;, a few of which might be actual bugs, see my separate message on &quot;get address of rows&quot;) <br>
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 <br>
3) I think there is no way to write user defined functions <br><br>Besides that, I really like being able to use &quot;serious&quot; 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. <br><br>Cheers <br>
Chris
0
CRP
3/8/2010 10:16:28 AM
mac.office.excel 1146 articles. 0 followers. Follow

4 Replies
1545 Views

Similar Articles

[PageSpeed] 4

CRP@officeformac.com wrote:
> 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

-- 
Jim Gordon
Mac MVP
Co-author of Office 2008 for Mac All-in-One For Dummies
http://tinyurl.com/Office-2008-for-Dummies
0
Jim
3/10/2010 12:24:02 AM
Jim, <br>
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). <br><br>So I will reformulate my (admittedly naive) rant: <br>
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. <br><br>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 &quot;.cells(2,3)&quot; versus &quot;cell 3 of row 2&quot;, or &quot;font object&quot; instead of &quot;font&quot;. <br>
And there are bugs as well. Try this: <br>
&nbsp;get address of rows of range &quot;B1:D3&quot; <br><br>Chris
0
CRP
3/10/2010 8:47:39 AM
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


On 10/03/10 7:47 PM, in article 59bb4501.1@webcrossing.JaKIaxP2ac0,
"CRP@officeformac.com" <CRP@officeformac.com> wrote:

> 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 "B1:D3"
> 
> Chris

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

 -- 

John McGhie, Microsoft MVP (Word, Mac Word); Consultant Technical Writer,
McGhie Information Engineering Pty Ltd, Sydney, Australia.
Ph: +61 (0)4 1209 1410; mailto:john@mcghie.name


0
John
3/10/2010 11:25:40 PM
CRP@officeformac.com wrote:
> 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 "B1:D3"
>
> 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

-- 
Jim Gordon
Mac MVP
Co-author of Office 2008 for Mac All-in-One For Dummies
http://tinyurl.com/Office-2008-for-Dummies
0
Jim
3/11/2010 12:29:31 AM
Reply:

Similar Artilces:

Excel on website
Hi, I have an Excel file which opens maximum at my desk, but it opens minimum when I view it on my company's intranet. I use FrontPage 03 and Excel 07. Please advice! Thanks Chi After moving the file to server and linked it to the page I got it! Thanks Chi "Chi" wrote: > Hi, > > I have an Excel file which opens maximum at my desk, but it opens minimum > when I view it on my company's intranet. I use FrontPage 03 and Excel 07. > > Please advice! > > Thanks > Chi > > > > > > Aft...

Sum
Hi, I just found this "feature" at Excel formulas today. Please, have a look at: http://danielgudang.multiply.com/journal/item/192 (in portuguese, but images show all) Let me explain: some cell C1 = sum (C2:C5) some cell D1 = sum (D2:D5) some cell E1 = sum (E2:E5) A1 = C1 - D1 - E1 B1 = C1 - (D1 + E1) A1 = B1 ??? Oh, not always! Sometimes A1 will be +0, sometimes -0. Really strange! It's a feature of any application that uses IEEE double precision floating point math (e.g., every commercial spreadsheet I know of). It's the result of having finite precision ...

HTML Code Fragment via Merge
Just learned how to create a catalog with Publisher and I was pretty pleased with this capability that I had never even noticed before. Now what I would like to do is create a web version of my catalog and include an item in each catalog entry that is an HTML code fragment. These would basically be paypal code items for a shopping cart entry relative to each catalog item. I would like to add the code for my fragment via another column in my XL spreadsheet that is the source for my merge. Does anyone know if this is doable? Get a program suitable to the task called WebPlus10 http://www.se...

Embedded Excel worksheet in PowerPoint
I'm working with an embedded excel worksheet in powerponit. The doc is sent to me and if I have to edit the worksheet, I find that some cells have missing characters once I open the worksheet to edit it. It appears that characters beyond some length (256?) are being truncated off. How do I resolve this so I don't have this problem again? Thanks, Barb Reinhardt This kind of problem may occur if the material was inserted into the embedded worksheet via copy/paste from an external sheet. A reliable approach is to open the embedded sheet first, and from that sheet do: File ...

Excel 2003 01-27-10
Hi all, hopefully you can help me. In cell B21 the user will always input an email address, i have created a button and now need the button to automatically email the email address in cell B21. I then need it to default and save to a specific location with the file name automatically generated from b12. Please help. :) ...

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

Script to monitor email messages
Hi everyone, I hope someone can help me with my problem. I was wondering if it's possible to write a macro script that will monitor incoming emails and send info to an Access database? To make it more clearer, here's the scenario: I need to monitor emails wich have the word "Handovers" from specific groups and i'd like the macro script to put a "Yes" on one of the fields in an Access database. Is this even possible or do i have to ask someone who's more an expert on Access? Please, i hope someone out there can help me. Thanks, -- "excel newbie&...

EXCEL 2007
I have a GIS application which uses .dbf files (not sure if they are actually Dbase files). When I need to append data (fields) to the files, I have a big problem in that excel 2007 no longer allows save as / export to .dbf files. If I try bring the data in MS Access and save as .dbf files, I have untold problems with the GIS application... The only way I have been successful with this is to find a user with an older version of excel, insert the data as database columns and then save as .dbf Any suggestions as to how I can get around this with out purchasing a converter? ...

in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely tell which cells are selected from those that are not. Can I change the background color of cells that are selected? Thanks. Assuming Excel 2007 this is a known problem and as yet no patch for it. I don't have Excel 2007 but I have read that playing with your Contrast can help some. Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 12:51:00 -0700, flameretired <flameretired@discussions.microsoft.com> wrote: >When I select cells in Excel the shading is so light (blue) that I can barely >tell whic...

email from excel workbook button does nothing
In my old version of excel(2003) as long as i had outlook open i could email directly from the worksheet. Now i have office 2007 with "windows mail" when i press the button in the "quick access" tool bar it does nothing. I have tried having widows mail open when I do it too but the only way i can send is by making file attachments. seems like the long way around. Is there something I am doing wrong? I have tried to read some of the previous posts and dont understand anything about codes ect. Please help, Mark hi Mark Is Windows Mail your default m...

Delete duplicate rows from a list in Excel
Hi, Below are instructions on how to "delete duplicate rows from a list in Excel". I learned about this tip from the Microsoft Office Assistant website. However, each time I try step 4, my list is not filtered and no records are hidden. Is there a secret I am missing to make this work? Thanks for your help! -Greg http://office.microsoft.com/en-us/assistance/HA010346261033.aspx Applies to Microsoft Office Excel 2003 Microsoft Excel 2002 A duplicate row (also called a record) in a list is one where all values in the row are an exact match of all the values in another row. To delet...

How to tell Excel to insert cells and shift down from Access
Hi all, I have Access 2003. I am trying to tell Excel to insert cells and shift cells down from Access, with this line of code: oWksh.Cells.Insert Shift:=xlDown But Access does not like that line. It gave me run-time error 1004. I know if has to do with the part Shift:=xlDown Can you suggestion what I can do? Thank you in advance, Ben "Ben" <Ben@NoSpam.com> wrote in message news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl... > Hi all, > > I have Access 2003. I am trying to tell Excel to insert cells and shift > cells down from Access,...

Text Wrap in Excel 2002
I have been having difficultly wrapping text. I merge several cells and wrap text sucessfully. However, if I go into one of the cells and add a couple of sentences and enter, my text that was wrapped perfectly is simply a string of #########'s and I can't view the text correctly by changing the row height or reformatting (even though it is all still there - as I can see in the edit box - when my cursor is on the text box). Any suggestions? Try formatting the cell as General. bdean wrote: > > I have been having difficultly wrapping text. I merge > several cells an...

EXCEL Cell Formatting: Custom or Conditional
At work I have Excel 2002 and I have some cells that I import data in from the mainframe. I have a table where there is a column for GENDER (MALE or FEMALE) that I would like to see shown as "Male" or "Female" (mixed case). Is there a way to change all upper case to mixed case using either CUSTOM formatting or CONDITIONAL formatting? I would prefer not using a macro if there is something simpler. I know that =PROPER() produces the correct result, but I don't see how I can use this unless I'm referencing another cell. I would like to be able to just change what fa...

Excel Version When Posting Please State Your Excel Version!!!
On Jan 8, 6:58=A0pm, "Tyro" <T...@hotmail.com> wrote: > Subject: Excel Version When Posting Please State Your > Excel Version!!! And Please Include Your Questions and Comments In the Body of Your Posting!!! Not just in the subject line, even if it simply duplicates the subject line. Some news readers truncate the subject line. Some news servers do not permit you to reply to articles with empty bodies. Some news servers seem to reject such articles when they come in from the network, or perhaps the originating news server does not push them into the network. That ...

Excel Formulas
I have a spreadsheet showing whether customers have paid their debts or not. Each row represents a customer and details of the debt and the last cell in the row states whether the debt is "PAID" OR "UNPAID". Where the word "PAID" appears in a cell, I want to write a formula that will move that whole row to a different location on my sheet. I eventually want to record this action on a MACRO, so that every time I run a MACRO on my accounts spreadsheet it will take the updated information of customers marked "paid" and put in a seperate spreadsheet. Ple...

how can I change the a,b,c, column headers in excel to names
I've tried and can't figure out how to change the column header from a,b,c, etc. to names for each column. Any tips are appreciated You can only show A,B,C...,IV or numbers: 1,2,3,...,256 But you could put your names in Row 1 and then select A1, then select A2 and click on Window|Freeze panes. espray wrote: > > I've tried and can't figure out how to change the column header from a,b,c, > etc. to names for each column. Any tips are appreciated -- Dave Peterson ...

How do i set Outlook to receive/send email via wanadoo
http://dts-l.org/goodpost.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. "Wendy" <Wendy@discussions.microsoft.com> wrote in message news:1D969EE0-8D22-482B-901D-8F17FFBFA96E@microsoft.com... | ...

Copy/Paste Excel Macro
Hello, What I'm trying to do is automate a process using an excel macro. What I need the macro to do is to look for a blank cell in a range of data in Column A, and whenever there is a blank space within that range, paste some specific above information into this row. (I'm formatting an excel file after taking it from another program). I would greatly appreciate any help! Thank you! You could record a macro to filter on blank in coloum A then paste a formula +cell above- this would then fill in your blanks -- Thanks for your help "Justin" wrote: >...

How do I print excel doc from an email
I dont have excell but I received an excell document by email. I am trying to find a way to print the doc. Is this possible? Thanks for any help. Get the free Excel viewer http://www.microsoft.com/downloads/details.aspx?FamilyID=c8378bf4-996c-4569-b547-75edbd03aaf0&displaylang=EN -- Regards Ron de Bruin http://www.rondebruin.nl "kevinb" <kevinb@discussions.microsoft.com> wrote in message news:BB654C4D-8AB3-4D98-89C7-49DA1BDE0C42@microsoft.com... >I dont have excell but I received an excell document by email. I am trying to > find a way to print the doc. Is this ...

Print Preview in Excel
We have office 2000 for small business. we have a peer to peer on 3 systems all sharing one printer. However, the computer with windows xp and office 2000 for small business won't print preview when the network printer, on the other system is off. I've updated office for small business and it still doesn't work. If anyone can assit me with this, it would be most helpful. I am at wits end over this. The other computer with the hp laserjet III is working on windows 98 with office 2000 as well, with not problems. It just seems to be this new computer that has the issue...

Will Excel 2004 run on SnowLeopard OS X 10.6?
Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm a SW developer who uses a lot of VBA for Excel programing, for automaation and macros. Besides that, my customers use the spreadsheets I develop in Office for PC's. I know Office 2008 has no VBA for Excel, but I really need it. Can I install Office 2004 in my computer and will it run under Snow Leopard, so that I can keep on using VBA with Excel? Yes -- But do a fresh install. Don't try to 'migrate' Office from an earlier version of the OS if you're upgrading to 10.6 & you will have to use t...

subscript and superscript buttonsfor Excel 2003?
The first thing I do with a new installation of Excel is always to add superscript and subscript buttons to the toolbar. Usually it goes something like this: Formatting Toolbar >> Add or Remove Buttons >> Format >> Superscript. I just got Small Business Edition 2003 for my new laptop, and those buttons do not seem to be available!! Am I doing something wrong, or has MS discontinued the only buttons I actually use?! Please help! I think you're mixing up Word and Excel. Excel doesn't have this icon. John Walkenbach has a helpful addin that you may like: http...

Validation in Excel
Hi, I need to validate the format of user input in Excel. For instance , If I have a cell which should only contains number, I want the user only be able to input number. I've tried to right-click the cell, choose "Format Cells", and go t "Number" tab, and change the category from "General" to "Number" , bu the I still can input anything in the cell. Fyi, I'm using Windows 2000 and Excel 2000. Regards, Susil -- susilo_saj ----------------------------------------------------------------------- susilo_saja's Profile: http://www.excelfor...

How do I protect only formulas in Excel?
Hi, I am looking for a solution to be able to allow some cells that are unlocked to have other users enter data into them, however I don't wan't other users to be able to enter formulas without a password. I have heard of this being done before but I didn't know if it was an option I've overlooked or some kind of advanced VB code. Any help is greatly appreciated, RH Hi, Just an idea: try catching OnChange event and check if = is entered and then popup a password dialog Hope this helps. Peter -- http://blog.jausovec.net "RHmcse2003" je napisal: >...