Office COM automatisation - calling python from VBA

Discussion in 'Python' started by guy lateur, Jun 24, 2005.

  1. guy lateur

    guy lateur Guest

    Hi all,

    I am trying to write some code (macro's, if you like) to glue together
    our Office applications (mainly Word, Excel and Outlook). We have a lot
    of different projects going on simultaneously. The idea is to develop a
    centralized framework (starting point, common interface) for my users
    to view/control their documents/correspondence, on a per project basis.

    As an example, I'd like to have a control (button, menu entry) in
    Outlook that allows my users to bring up, say, an email for a certain
    contact (architect, owner, engineer, ..) on a certain project, with
    certain attachments, .. Currently, I have a 'public folder' in OL
    (Exchange) that reflects our project structure.

    I'll be using COM, and I could probably make an application that
    controls Outlook (externally). But I'd also like to have this
    functionality exposed in OL itself. So I guess I'll need to use VBA,
    but I don't really like VBA - relax, please, it's just an opinion.. ;)

    So, ideally, I'd like to program as much as possible in python (I'm
    pretty new to that, too, btw), and only use VBA if needed - say, to
    call python objects/methods (+ wxGUI, please).


    Would that be an easy, a hard, or an insane strategy? Maybe there are
    some tutorials on this (searched the list, but didn't quite find any).
    If anyone happens to have any xp/tips on this, please, fire away!


    Best regards,
    g
     
    guy lateur, Jun 24, 2005
    #1
    1. Advertising

  2. Hi !


    Perso, j'utilise ça (VBA) :

    Sub TestPonx()
    Dim oa As Object
    Set oa = CreateObject("Ponx.Mci")
    Cells(2, 4) = oa.PRet("123+45+6")
    Set oa = Nothing
    End Sub


    "Ponx.Mci" est le nom du serveur COM Python
    PRet() est équivalent à eval()


    Michel Claveau
     
    Do Re Mi chel La Si Do, Jun 24, 2005
    #2
    1. Advertising

  3. guy lateur

    Guest

    Can you make python into a com object? I think you can I just don't
    rember. If so you want to find a page about com add-ins for office.
    This is a com object that you can teach office to look for when It is
    started. I wrote one in vb years ago and havn't looked back. But I
    think that would be the way to go.
     
    , Jun 24, 2005
    #3
  4. "guy lateur" <> wrote in message
    news:...
    > Hi all,
    >
    > I am trying to write some code (macro's, if you like) to glue together
    > our Office applications (mainly Word, Excel and Outlook). We have a lot
    > of different projects going on simultaneously. The idea is to develop a
    > centralized framework (starting point, common interface) for my users
    > to view/control their documents/correspondence, on a per project basis.
    >
    > As an example, I'd like to have a control (button, menu entry) in
    > Outlook that allows my users to bring up, say, an email for a certain
    > contact (architect, owner, engineer, ..) on a certain project, with
    > certain attachments, .. Currently, I have a 'public folder' in OL
    > (Exchange) that reflects our project structure.
    >
    > I'll be using COM, and I could probably make an application that
    > controls Outlook (externally). But I'd also like to have this
    > functionality exposed in OL itself. So I guess I'll need to use VBA,
    > but I don't really like VBA - relax, please, it's just an opinion.. ;)
    >
    > So, ideally, I'd like to program as much as possible in python (I'm
    > pretty new to that, too, btw), and only use VBA if needed - say, to
    > call python objects/methods (+ wxGUI, please).
    >
    >
    > Would that be an easy, a hard, or an insane strategy? Maybe there are
    > some tutorials on this (searched the list, but didn't quite find any).
    > If anyone happens to have any xp/tips on this, please, fire away!


    You are using Microsoft Windows.
    You are trying to integrate ("glue together") Microsoft Office applications.
    You want to use other Microsoft COM objects.
    You want to your software to *be* a COM object ("functionality exposed in OL
    itself")
    You want a robust GUI interface that has the look, feel, and fits in with,
    MS Office.
    You want to use --- Python ???

    How, pray tell, do you add up (VBA+VBA+VBA+VBA+VBA) and have it come out
    equaling Python?

    Be reasonable here. You don't have to "like" VBA to see that this is the
    only practical choice in this situation. Imagine if Python had a robust GUI
    development system that fit right into your os platform AND was the native,
    built in, macro language for the top 5 high level applications you needed to
    work with. Do you think that might please a few of us here in this
    particular newsgroup? This is exactly what Microsoft has with Office/VBA.

    I don't particularly like VBA as a language either.
    And
    I don't like B. Gates
    And
    I may well be crazy.

    Just not *that* crazy!
    Thomas Bartkus
     
    Thomas Bartkus, Jun 24, 2005
    #4
  5. guy lateur

    guy lateur Guest

    > You want to use --- Python ???

    So far I haven't been informed of any serious arguments as to why I
    wouldn't.


    > How, pray tell, do you add up (VBA+VBA+VBA+VBA+VBA) and have it come out
    > equaling Python?


    My total was this: 57*python + wxPython.


    > Do you think that might please a few of us here in this
    > particular newsgroup?


    Yes.
     
    guy lateur, Jun 24, 2005
    #5
  6. guy lateur

    Chris Smith Guest

    >>>>> "guy" == guy lateur <> writes:

    guy> Hi all, I am trying to write some code (macro's, if you like)
    guy> to glue together our Office applications (mainly Word, Excel
    guy> and Outlook). We have a lot of different projects going on
    guy> simultaneously. The idea is to develop a centralized
    guy> framework (starting point, common interface) for my users to
    guy> view/control their documents/correspondence, on a per project
    guy> basis.

    guy> As an example, I'd like to have a control (button, menu
    guy> entry) in Outlook that allows my users to bring up, say, an
    guy> email for a certain contact (architect, owner, engineer, ..)
    guy> on a certain project, with certain attachments, .. Currently,
    guy> I have a 'public folder' in OL (Exchange) that reflects our
    guy> project structure.

    guy> I'll be using COM, and I could probably make an application
    guy> that controls Outlook (externally). But I'd also like to have
    guy> this functionality exposed in OL itself. So I guess I'll need
    guy> to use VBA, but I don't really like VBA - relax, please, it's
    guy> just an opinion.. ;)

    guy> So, ideally, I'd like to program as much as possible in
    guy> python (I'm pretty new to that, too, btw), and only use VBA
    guy> if needed - say, to call python objects/methods (+ wxGUI,
    guy> please).


    guy> Would that be an easy, a hard, or an insane strategy? Maybe
    guy> there are some tutorials on this (searched the list, but
    guy> didn't quite find any). If anyone happens to have any
    guy> xp/tips on this, please, fire away!


    guy> Best regards, g

    You can have VBA code invoke a python script asynchronously without
    much trouble.
    If you peruse MSDN, you can find some examples that will let you have
    VBA block while waiting on a python script.
    Serious interaction? I'd probably persue VisualStudio and IronPython,
    if ActiveState's PythonWin isn't going to be enough.
    Maybe PythonWin can make Python a COM server; never researched it.
    Hope these ideas help,
    Chris
     
    Chris Smith, Jun 24, 2005
    #6
  7. guy lateur wrote:
    > So, ideally, I'd like to program as much as possible in python (I'm
    > pretty new to that, too, btw), and only use VBA if needed - say, to
    > call python objects/methods (+ wxGUI, please).
    >


    If you are new to Python and want to use it with COM, definitely get
    yourself a copy of _Python Programming on Win32_ by Mark Hammond and
    Andy Robinson.

    Read the first few chapters to get yourself started, then ask more
    specific questions here. There always seem to be lots of helpful answers.

    Good luck!

    Steve P.
     
    Stephen Prinster, Jun 25, 2005
    #7
  8. On 6/25/05, Stephen Prinster <> wrote:
    > guy lateur wrote:
    >
    > If you are new to Python and want to use it with COM, definitely get
    > yourself a copy of _Python Programming on Win32_ by Mark Hammond and
    > Andy Robinson.


    ....or at least read the chapter available online:
    http://www.oreilly.com/catalog/pythonwin32/chapter/ch12.html

    It has an example of VBA / PythonCOM interaction.

    - kv
     
    Konstantin Veretennicov, Jun 25, 2005
    #8
  9. guy lateur

    Josef Meile Guest

    Hi guy,

    > I'll be using COM, and I could probably make an application that
    > controls Outlook (externally). But I'd also like to have this
    > functionality exposed in OL itself. So I guess I'll need to use VBA,
    > but I don't really like VBA - relax, please, it's just an opinion.. ;)
    >
    > So, ideally, I'd like to program as much as possible in python (I'm
    > pretty new to that, too, btw), and only use VBA if needed - say, to
    > call python objects/methods (+ wxGUI, please).

    You could try to do an addin/addon for Word, Excel, and Outlook. You
    don't need to code with VBA. Here you just need a language from where
    you can access the microsoft interop assemblies (ie: C++ or C#;
    IronPython maybe?)

    I'm now working in an addon for Visio, but I'm not using python. I'm
    using C#. The main idea is that we have an exe application, which
    creates a COM object, with the first running instance of visio (if some
    is found). Then, I use a class (event handler) to catch some events
    comming from visio (this is in part documented at the msdn). I even
    embebbed a windows form in Visio (it was an example in the sdk).

    So, I think in theory, you could do the same with python. I have heard
    you can somehow create COM objects there. You could also try the new
    version of python for net: IronPython. I guess you can from there access
    all the assemblies to interact with office:

    Microsoft.Office.Interop.Excel,
    Microsoft.Office.Interop.Word, and
    Microsoft.Office.Interop.Outlook

    A good start is to see the SDK documentation of each office product you
    need at each developer center in www.msdn.microsoft.com

    Good luck,
    Josef
     
    Josef Meile, Jun 25, 2005
    #9
  10. On 6/25/05, Josef Meile <> wrote:
    > You could try to do an addin/addon for Word, Excel, and Outlook. You
    > don't need to code with VBA. Here you just need a language from where
    > you can access the microsoft interop assemblies (ie: C++ or C#;
    > IronPython maybe?)


    Hmm... Why jump through .NET hoops when all you need is COM? I suppose
    msoffice interops are no different than other .NET interops, which are
    just that - .NET/COM interoperability layer. There's no need for them
    unless you're calling COM from .NET.
    For CPython win32com should be enough.

    - kv
     
    Konstantin Veretennicov, Jun 25, 2005
    #10
  11. Hi.

    > > If you are new to Python and want to use it with COM, definitely get
    > > yourself a copy of _Python Programming on Win32_ by Mark Hammond and
    > > Andy Robinson.

    >
    > ...or at least read the chapter available online:
    > http://www.oreilly.com/catalog/pythonwin32/chapter/ch12.html


    Also, check out the following tutorials:
    http://www.reportlab.com/ftp/talks/PythonWindowsTutorial.doc
    http://starship.python.net/crew/pirx/spam7/COMtut.PPT

    ....and if you're visiting EuroPython make sure to show up for Guy
    Dalbertos tutorial on Python+ Excel. If you're not, download his
    presentation and example code from:
    http://www.python-in-business.org/ep2005/talk.chtml?talk=2626&track=690

    HTH
    Johan Lindberg
     
    Johan Lindberg, Jun 25, 2005
    #11
  12. guy lateur

    Guy Lateur Guest

    Thanks for the input, people!
     
    Guy Lateur, Jun 27, 2005
    #12
  13. guy lateur

    guy lateur Guest

    Just an update: I've succeeded in writing a COM server, exposing wxPy
    funtcionality. I've also used this object from within Outlook - 2 lines of
    VBA: dispatch COM object & call method. If anyone is interested, I could
    post the source.

    A few days ago, I honestly didn't think I'd already be this far by now (it
    took about half a day). Especially the chapter of the book by Mark Hammond
    (my copy of which is being dispatched as we speak) was very helpful.

    8)
    g
     
    guy lateur, Jun 28, 2005
    #13
    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. Nelson Smith

    Calling VBA Script...

    Nelson Smith, Mar 2, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    387
    Chris Jackson
    Mar 2, 2004
  2. Stan Accrington
    Replies:
    1
    Views:
    945
    Michael Borgwardt
    May 13, 2004
  3. Oleg  Konovalov

    Java calling C# or VBA (MS Office API) ?

    Oleg Konovalov, Jun 2, 2004, in forum: Java
    Replies:
    6
    Views:
    1,405
    Andrew Thompson
    Jun 4, 2004
  4. partha

    VBA web services call not working in office XP

    partha, Jun 1, 2004, in forum: ASP .Net Web Services
    Replies:
    1
    Views:
    171
  5. Alain Bourgeois

    calling access vba function from asp?

    Alain Bourgeois, May 6, 2008, in forum: ASP General
    Replies:
    3
    Views:
    366
    Bob Barrows [MVP]
    Jul 24, 2008
Loading...

Share This Page