Re: Python in Excel

[Bob Gailer]

Can I write Excel macros/scripts using Python?

I mean to actually put Python into an Excel document, not using
Python to access data, for example, from some Excel document.

Do you want to use Python as an Excel macro language instead of VBA?

If so, the answer is almost guaranteed to be NO.

Why do you want to do this? What alternatives can you accept?

Bob Gailer
(e-mail address removed)
303 442 2625

 

[Tom Locke]

Can I write Excel macros/scripts using Python?
These days you can script office with any .NET language, and that includes
Python:

http://www.activestate.com/Products/Visual_Python/

At one point I know this implementation was horribyl slow - I'm not sure
where they're at now.

If you want to actually embed the script inside the spreadsheet, I believe
that is only possible in Office 2003 - currently in beta. Right now you can
write a standalone program that can launch and then control Excel. You can
do much more than just access the data - you can do anything that you can do
with VB. The only thing missing is embedding the .NET code in the document.

Tom.

 

[hannibal]

You can use Microsoft Script Control. If you have the win32 extensions
of python, you can use python in place of vb in this control

-open the VBA script editor - In menus/Tools/References add Microsoft
Script Control
-Make a new module and declare a new MsScriptControl.ScriptControl
Global sc as new MsScriptControl.ScriptControl
-Initialize the language attibute with python - Note that you and users
of your document must have python and its win32 extensions installed.
Activestate python distribustion include it.
You can put
sc.language="python"
in the routine Workbook_Open()

Now you can import python modules using ExecuteStatement method of the
control in vba and have results from python functions with eval method.
One interesting thing is that you can pass an object to the control with
AddObject method and have python manipulate it. And so on..



John J. Lee a écrit :

These days you can script office with any .NET language, and that includes
Python:

[...]

Or use good old COM. There's an example of controlling Excel (the
version from Office 2000) in the Python for Windows Extensions (aka
win32all) COM test code. IIRC, it'll be somewhere like
C:\Python\win32com\tests\.


John

 

[Afanasiy]

You can use Microsoft Script Control. If you have the win32 extensions
of python, you can use python in place of vb in this control

-open the VBA script editor - In menus/Tools/References add Microsoft
Script Control
-Make a new module and declare a new MsScriptControl.ScriptControl
Global sc as new MsScriptControl.ScriptControl
-Initialize the language attibute with python - Note that you and users
of your document must have python and its win32 extensions installed.
Activestate python distribustion include it.
You can put
sc.language="python"
in the routine Workbook_Open()

Now you can import python modules using ExecuteStatement method of the
control in vba and have results from python functions with eval method.
One interesting thing is that you can pass an object to the control with
AddObject method and have python manipulate it. And so on..

Thanks this is almost what I am looking for, very cool.
It would be nice not to have to do so much VB with it.

Is there any way around that? From my perspective, every Python function
used will be wrapped in a VB function which calls the Python function.

-AB

 

[Ajith Prasad]

This looks very useful to Excel users who would like to tap the power
of Python. However, not knowing enough about VBA syntax it was not
possible to proceed beyond step(a). Is it possible to provide an
Idiot's guide to steps (b), (c),(d) and (e)? In other words, what is
the explicit VBA code/steps need to do (b) and (c) and could simple
(even trivial) examples be given of steps (d) and (e). Thanks in
advance.

You can use Microsoft Script Control. If you have the win32 extensions
of python, you can use python in place of vb in this control

-(a)open the VBA script editor - In menus/Tools/References add Microsoft
Script Control
-(b)Make a new module and declare a new MsScriptControl.ScriptControl
Global sc as new MsScriptControl.ScriptControl
-(c)Initialize the language attibute with python - Note that you and users
of your document must have python and its win32 extensions installed.
Activestate python distribustion include it.
You can put
sc.language="python"
in the routine Workbook_Open()

(d)Now you can import python modules using ExecuteStatement method of the
control in vba and have results from python functions with eval method.
(e)One interesting thing is that you can pass an object to the control with
AddObject method and have python manipulate it. And so on..



John J. Lee a écrit :

Can I write Excel macros/scripts using Python?

I mean to actually put Python into an Excel document, not using
Python to access data, for example, from some Excel document.

These days you can script office with any .NET language, and that includes
Python:

[...]

Or use good old COM. There's an example of controlling Excel (the
version from Office 2000) in the Python for Windows Extensions (aka
win32all) COM test code. IIRC, it'll be somewhere like
C:\Python\win32com\tests\.


John

 

[Afanasiy]

This looks very useful to Excel users who would like to tap the power
of Python. However, not knowing enough about VBA syntax it was not
possible to proceed beyond step(a). Is it possible to provide an
Idiot's guide to steps (b), (c),(d) and (e)? In other words, what is
the explicit VBA code/steps need to do (b) and (c) and could simple
(even trivial) examples be given of steps (d) and (e). Thanks in
advance.

Global sc As New MSScriptControl.ScriptControl

Public Function os_getcwd()
sc.Language = "python"
sc.ExecuteStatement ("import os")
os_getcwd = sc.Eval("os.getcwd()")
End Function

With this you can set your Excel formula to =os_getcwd()

For me it returns "C:\Documents and Settings\Administrator\My Documents",
which I needed to know at the time so I didn't have to screw around with
the ever annoying pythonpath.

You can put the first two lines of the function in the Workbook_Open hook,
but I don't know where that is. I hope to use more Python in Excel soon.
Hmm, actually, I suppose you can put those first two lines of the function
after the Global declaration as well. I know just about zero VBScript and
didn't get a chance to do anything else beyond proof of concept yet.

I figured I would write something dynamic which allowed more transparent
access to Python, maybe allowing formula like =py("os.getcwd()"), etc.

-AB