Win32 python and excel macros

[John Coleman]

michael.pearmain at tangozebra.com wrote:
> Hi Experts,
>
> Looking for a very quick bit on of advice on how to make some python
> code run.  I'm a newbie to both VBA and Python, so i apologise if this
> is very easy but i'm about to tear my hair out after googling for the
> last 3 days.
>
> I have written a large python script which inside of it creates an
> Excel table, the name of this file and how many objects can change for
> each project i run.
>
> I have then written a VBA script which takes the info from Excel and
> drops it into a PowerPoint Pres.
>
> Both of these procedures work fine, but i am coming unstuck when i try
> to apply the macro, (or .xla) file to the new tables autmatically.  Can
> anyone give me any guidance on this?
>
> The macro is called sub is CTP and the add-in file is CTP.XLA
>
> Below is the code i've managed to 'Stick' together
>
> Mike
>
> import win32com.client
> xl = win32com.client.Dispatch("Excel.Application")
> ppt = win32com.client.Dispatch("PowerPoint.Application")
> xl.Visible = 1 #open MS Excel
> ppt.Visible = 1 #open MS Powerpoint
> xl.Workbooks.Open('Z:\\projects\\surveys\\SPSS - Generic files\\big
> output.xls') #A table for a project
> xl.Workbooks.Open('Z:\\projects\\surveys\\SPSS - Generic
> files\\CTP.xla') # Stored macro add-in
> ppt.Presentations.Open('Z:\\projects\\surveys\\SPSS - Generic
> files\\Basic Template.ppt')
> xl.Application.ExecuteExcel4macro('CTP!CTP.xla()"[big output.XLS]')

It doesn't really make sense to apply a *file* to a *file*  - you apply
a sub or function in that file to a range in the other file (I'm
assuming that your table is stored as a range of cells). What
ExcecuteExcel4Macro is expecting as input is a string along the lines
of
'CTP!MacroName(Workbooks("big output").Range("A1:C100"))'
(experiment with using "" instead of " since VBA requires embedded " to
be escaped by "" - but since you are writing this in Python it might
not be necessary). Maybe experiment with writing a VBA macro in Excel
which can successfuly launch the macro you need and then translate the
appropriate snippet to your python script. Also - are you sure that the
add-in macro is an old-style Excel4 macro? That would make it about 10
years old or deliberately retro. If not - the run method might be more
appropriate.

You should probably open the workbooks in such a way that big
output.xls is the active workbook (and not ctp.xla) since most add-ins
assume that the calling workbook is the active workbook (although - I
don't know how an old-style pre-VBA Excel4 macro handled things). Thus
you would probably want to open big output.xls last (or use
xl.Application.Workbooks("big output").Activate ) to make sure that it
is the active workbook. Also - do you even have to open ctp.xla
explicitly? If it is an installed add-in then that line might be
redundant.

A final potential problem is that big output.xls might require a
reference to ctp.xla. This sometimes happens when you try to invoke
add-in code from another VBA project - but I would think that the
ExecuteExcel4macro would bypass that.

I can't comment on the python part of the equation - I am a complete
newbie there.

You might consider reposting this in microsoft.public.excel.programming
since many of the regular posters there know a lot about automating
Excel from scripting languages. They could at least help you with the
VBA side of the equation.

I hope that my random thoughts don't misguide you too much.

-John Coleman