Re: Python in Excel

Discussion in 'Python' started by Bob Gailer, Jul 3, 2003.

  1. Bob Gailer

    Bob Gailer Guest

    At 07:37 PM 7/3/2003 +0000, Afanasiy wrote:

    >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

    303 442 2625


    ---
    Outgoing mail is certified Virus Free.
    Checked by AVG anti-virus system (http://www.grisoft.com).
    Version: 6.0.492 / Virus Database: 291 - Release Date: 6/24/2003
     
    Bob Gailer, Jul 3, 2003
    #1
    1. Advertising

  2. Bob Gailer

    Tom Locke Guest

    > >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:

    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.
     
    Tom Locke, Jul 4, 2003
    #2
    1. Advertising

  3. Bob Gailer

    hannibal Guest

    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 :
    > "Tom Locke" <> writes:
    >
    >
    >>>>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


    ---
    Posted via news://freenews.netfront.net
    Complaints to
     
    hannibal, Jul 5, 2003
    #3
  4. Bob Gailer

    Afanasiy Guest

    On Sat, 05 Jul 2003 02:48:15 +0100, hannibal <>
    wrote:

    >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
     
    Afanasiy, Jul 7, 2003
    #4
  5. Bob Gailer

    Ajith Prasad Guest

    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.


    hannibal <> wrote in message news:<be5amo$2phm$>...
    > 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 :
    > > "Tom Locke" <> writes:
    > >
    > >
    > >>>>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

    >
    > ---
    > Posted via news://freenews.netfront.net
    > Complaints to
     
    Ajith Prasad, Jul 11, 2003
    #5
  6. Bob Gailer

    Afanasiy Guest

    On 11 Jul 2003 08:16:33 -0700, (Ajith Prasad) wrote:

    >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
     
    Afanasiy, Jul 11, 2003
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SmltIEhlYXZleQ==?=

    Excel _WorkBook vs. Excel.Workbook

    =?Utf-8?B?SmltIEhlYXZleQ==?=, Sep 29, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    3,705
    Steve C. Orr [MVP, MCSD]
    Sep 29, 2004
  2. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,433
    Carl Prothman [MVP]
    Jan 12, 2005
  3. =?Utf-8?B?U3JpZGhhcg==?=

    using Microsoft Excel image for Export to Excel button

    =?Utf-8?B?U3JpZGhhcg==?=, Dec 9, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    3,164
    =?Utf-8?B?U3JpZGhhcg==?=
    Dec 9, 2005
  4. zxo102
    Replies:
    3
    Views:
    3,324
    Stuart Corrie
    Aug 9, 2005
  5. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,604
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
Loading...

Share This Page