Writing to stdout from within a Microsoft VBA macro

Question

I'm using Python (and the Win32 extensions) to execute macros in an Excel spreadsheet via the COM interface, as shown below:

import win32com.client
o = win32com.client.Dispatch("Excel.Application")
o.Visible = 1
o.Workbooks.Open (r"C:\test.xls")
o.Application.Run("macro1")

What I'd like to do is have the Excel macro output text warning messages, which the Python code can catch via COM or stdout (or some other mechanism). Is it possible to write to stdout from within a Microsoft VBA app? Is there another way to do this that I don't know about?

Answer 1

What happens if you write to stdout? Either:

print "Hello, world!"

or:

import sys
sys.stdout.write("Hello, world!\n")

or even:

import sys
sys.__stdout__.write("Hello, world!\n")

sys.stdout is the current stdout file, __stdout__ is the original stdout when the Python interpreter started.

Answer 2

You almost certainly have to use Win32 API to get anywhere here. The question http://stackoverflow.com/questions/1692987/redirect-stdout-to-an-edit-control-win32 refers to using stdin/stdout in a GUI application. Unfortunately the questioner has not solved their problem yet.

I'm interested in what mechanism in Windows supports stdin / stdout. It seems very much a second class citizen compared to its role in Unix.

Answer 3

You can write to and read stdout with VB6 with a bit of drama.. but creating a console app with Excel and VBA? I'm not sure that is possible.

I've found a simple Python COM example which you may have already investigated. Could you make your macro just create a sheet a write to a cell and have Python poll that cell? Also you could raise custom errors via Err.Raise() ... or worse case use an environment variable.. these would be better than firing up a console.

Since i'm guesing you want asynchronous communication another possibility is DDE or Dynamic Data Exchange. In the mid 90's we used to have an in-house scripting language (the DDE client) talk to a running Excel session (the DDE server) and request it to input data, run macros, return cell values etc. It wasn't nice but it worked.

There's a Python DDE library as well as several examples. One warning; I remember any DDE coding being a massive pain and relied a lot on a tool called DDESpy. I'm surprised you can't do what you need via COM, so i'd investigate a little more before turning to DDE.

Answer 4

One solution is to write a COM server in Python. The VBA macro could instantiate the COM server, and then send it messages.

Once you've sent the data from VBA to your Python COM server, writing to stdout via a console application would be tricky, but you could write to a file, a web browser (via COM automation), a web server (e.g. via HTTP), a GUI edit window (e.g. via sockets), and so on.

Here's a free chapter to Python Programming on Win32. Check out the section titled "Implementing a COM Server". The whole book is well worth reading if you're interested in Windows programming using Python, although it's now showing its age.