The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

import-bot (20211) [Avatar] Offline
#1
[Originally posted by jimmymac]

Hi,
I've bought this book and the O'Reilly one (Python Programming on Win32) but I
can't find out if it is possible to start an Excel macro from Python (not by
autostart in Excel) and return control to Python after it has finished
executing. Anybody know if this is possible or not? And, if so, how to do it ?

thanks
Jim
import-bot (20211) [Avatar] Offline
#2
Re: python and excel macros
[Originally posted by daryl harms]

> Hi,
> I've bought this book and the O'Reilly one (Python Programming on Win32) but I
> can't find out if it is possible to start an Excel macro from Python (not by
> autostart in Excel) and return control to Python after it has finished
> executing. Anybody know if this is possible or not? And, if so, how to do it
?
>
> thanks
> Jim

Hi Jim,

I'm sorry but I don't have an answer for you on this. Andy Robinson was the
one who wrote this chapter (and also co-authored Python Programming on Win32)
and he are Mark Hammond might be the best people to ask this question.

One option though appears to be to rewrite the macro in Python code ala:
http://groups.google.com/groups?q=%2Bpython+%2Bexcel+%2Bmacro&start=10&hl=en&lr=lang_en&rnum=13&selm=HOTW5.4117%24xW4.39082%40news-server.bigpond.net.au


but this is of course more work.

Daryl
import-bot (20211) [Avatar] Offline
#3
Re: python and excel macros
[Originally posted by jimmymac]

Hi Daryl,

Thanks for your response - even when not so positive. Perhaps you could pass
my query on to Andy and Mark ? It seems to me that this is such an obvious
thing that one would want to do with Python/Excel; anyone coming from the
Excel world and wanting to learn Python will almost certainly know VBA and
will not want to reinvent the wheel in Python.

I'm surprised it is not mentioned in either of these books - even if it's just
to say that it can't be done.

I should add that I find your book (and Python) very good, Python is brilliant
for cleaning up data in one system before entering in another.

cheers
Jim
import-bot (20211) [Avatar] Offline
#4
Re: python and excel macros
[Originally posted by jimmymac]

Hallo again,
I've made some progress on this after getting some insight from a Perl site,
of all places.
import-bot (20211) [Avatar] Offline
#5
Re: python and excel macros
[Originally posted by jimmymac]

Hi,
Oops did not mean to post just yet. This script executes a simple print macro
in the excel file "testmacro1.xls", the macro is called "testmac" and for
those who are interested it looks like this :

Sub testmac()
Activecell.Value = "your message goes here"
End Sub

The Python script is as follows (on Pythonwin naturally):

import os
from win32com.client import Dispatch
xlApp = Dispatch("Excel.Application")
os.chdir("C:your_dir_path") # ie change to where the excel file resides
mydir = os.getcwd() # assign this path to a variable for conciseness

xlApp.Workbooks.Open(mydir+" estmacro1.xls") # open the excel file
xlApp.Visible =1 # make it visible
xlApp.Run("testmac") # this is the key syntax !

# End of script

How robust this is remains to be seen but it is a start !

Think I'go and have some spam,spam beans and spam now (or as the latter is
known here in Germany - Fleischkase [literally flesh-cheese]).

cheers
Jim