Office COM automatisation - calling python from VBA

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

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

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.

"guy lateur" <guy.lateur@pandora.be> wrote in message
news:1119642545.750550.304450@g44g2000cwa.googlegr oups.com...[color=blue]
> 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![/color]

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

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

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

[color=blue]
> How, pray tell, do you add up (VBA+VBA+VBA+VBA+VBA) and have it come out
> equaling Python?[/color]

My total was this: 57*python + wxPython.

[color=blue]
> Do you think that might please a few of us here in this
> particular newsgroup?[/color]

Yes.

>>>>> "guy" == guy lateur <guy.lateur@pandora.be> 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

guy lateur wrote:[color=blue]
> 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).
>[/color]

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.

On 6/25/05, Stephen Prinster <prinster@mail.com> wrote:[color=blue]
> 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.[/color]

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

It has an example of VBA / PythonCOM interaction.

- kv

Hi guy,
[color=blue]
> 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).[/color]
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

On 6/25/05, Josef Meile <jmeile@hotmail.com> wrote:[color=blue]
> 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?)[/color]

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

Hi.
[color=blue][color=green]
> > 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.[/color]
>
> ...or at least read the chapter available online:
> http://www.oreilly.com/catalog/pytho...pter/ch12.html[/color]

Also, check out the following tutorials:
http://www.reportlab.com/ftp/talks/P...wsTutorial.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/ep...2626&track=690

HTH
Johan Lindberg
johan@pulp.se

Thanks for the input, people!

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