[python-win32] VBA MACRO and Python

[Floris van Nee]

Here's the convo I had with Michael about VBA and Python. I mailed them
directly to him instead of to Python-win32 mailing list. So here they are
for the mailing list. I just keep forgetting that I can't just hit 'reply'
in Gmail :P

Floris

---------- Forwarded message ----------
From: Floris van Nee <floris.vannee at gmail.com>
Date: Oct 13, 2006 6:22 PM
Subject: Re: [python-win32] VBA MACRO and Python
To: Michael Pearmain <Michael.Pearmain at tangozebra.com>


I don't know if that would be a problem. I'm not very used to using macro's.

After some searching on the web, I found that you can use
xl = Dispatch("Excel.Application")
xl.VBE.ActiveVBProject.VBComponents.Import("C:/somescript.bas")
xl.Run("Name of module in somescript.bas")

to run a script. However, it seems you need to have the script in plain text
format, instead of whatever format you're using now and you need to call the
name of the module you want to run. For example, if somescript.bas contains
something like:

sub MyMacro()
       msgbox "Inside generated macro!!!"
end sub

Then you do xl.Run("MyMacro")

Maybe you should try something like that and see if that works.

Floris

On 10/13/06, Michael Pearmain <Michael.Pearmain at tangozebra.com> wrote:
>
>  just a thought on this?
>
> I currently activate the macro in weighted tables by a click button, would
> this be a potential problem?
>
>  ------------------------------
> *From:* Floris van Nee [mailto:floris.vannee at gmail.com]
> *Sent:* 13 October 2006 13:21
> *To:* Michael Pearmain
> *Subject:* Re: [python-win32] VBA MACRO and Python
>
>
>  I will take a look at it, but could you please post the exact error
> messages you are getting when you are trying to run the macro?
>
> Additionally, the problem may be caused because of security settings in
> Microsoft Office:
>
> Additional Notes for Office XP
> Office XP applications have a security option to allow programmatic access
> to the VBA object model. If this setting is "off" (the default), you may
> receive an error running the sample code. For more information about this
> setting and how you can correct the error, see the following article in the
> Microsoft Knowledge Base:
> 282830 <http://support.microsoft.com/kb/282830/EN-US/> (http://support.microsoft.com/kb/282830/EN-US/
> ) PRB: Programmatic Access to Office XP VBA Project Is Denied
>
> Maybe that is causing the problem?
> Floris
>
>
> On 10/13/06, Michael Pearmain <Michael.Pearmain at tangozebra.com > wrote:
> >
> >  Thanks for the advice,
> > Unfortunately it still doesn't work? I just can't seem to get the macro
> > to run,
> >
> > Attached are 3 files the big output is the file with all the tables,
> > weighted tables, was where I created the original VBA code and
> > Charts2presentation is (hopefully where i created a stored version of the
> > macro)
> >
> > i completely understand if you are to busy to take a look and help with
> > my problem, but i'm at that stage where i know it's going to be 1 or 2 lines
> > of code to change and it's driving me mad!!!
> >
> > Many thanks in advance if you have any spare time
> >
> > Mike
> >
> >
> >
> >  ------------------------------
> > *From:* Floris van Nee [mailto:floris.vannee at gmail.com]
> > *Sent:* 13 October 2006 12:46
> > *To:* Michael Pearmain
> > *Subject:* Re: [python-win32] VBA MACRO and Python
> >
> >
> >  Hi,
> >
> > I have tried a few things myself in Python and Excel, and for me a
> > normal macro seems to execute fine when I use this code:
> >
> > >>> from win32com.client import Dispatch
> > >>> a = Dispatch("Excel.Application")
> > >>> a.Visible = 1
> > >>> a.Workbooks.Open('test.xls')
> > <COMObject Open>
> > >>> a.Run("Macro1")
> >
> > If you need some more information about calling macro's from another
> > program, here is a Microsoft help page: http://support.microsoft.com/default.aspx?scid=kb;EN-US;194611
> >
> >
> > I hope that's what you need :)
> >
> > Floris
> >
> > On 10/13/06, Michael Pearmain <Michael.Pearmain at tangozebra.com > wrote:
> > >
> > >
> > > Hi I'm a beginner to python and VBA,
> > >
> > > However I have made a VBA macro which takes tables in excel and
> > > creates an PowerPoint slideshow,
> > >
> > > I want to automate this process using python
> > >
> > > The trouble I have is that the name of the file I'm running this on
> > > changes all the time, but I have saved the macro , so I want to apply the
> > > macro from one file onto the file with all the tables in? Make sense?
> > >
> > > Below is the code I've written but I keep getting errors, can anyone
> > > offer advice please?
> > >
> > > Much appreciated
> > >
> > > 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
> > >
> > > #Open the work book big output (file I want ot run the macro on
> > >  xl.Workbooks.Open('Z:\\projects\\surveys\\SPSS - Generic files\\big
> > > output.xls ')
> > > # Load the macro to use
> > >  xl.Workbooks.Open('Z:\\projects\\surveys\\SPSS - Generic
> > > files\\ChartsToPresentation.xla')
> > > # now try and run macro on big output, this is where it fails
> > >  xl.Application.ExecuteExcel4Macro("weightedtables!ChartsToPresentation()""[big
> > > output.XLS]Sheet1")
> > >
> > >
> > > ______________________________________________________________________
> > > This email has been scanned by the MessageLabs Email Security System.
> > > For more information please visit http://www.messagelabs.com/email
> > > ______________________________________________________________________
> > >
> > >
> > >
> > > _______________________________________________
> > > Python-win32 mailing list
> > > Python-win32 at python.org
> > > http://mail.python.org/mailman/listinfo/python-win32
> > >
> > >
> > >
> >
> > ________________________________________________________________________
> > This e-mail has been scanned for all viruses by Star. The
> > service is powered by MessageLabs. For more information on a proactive
> > anti-virus service working around the clock, around the globe, visit:
> > http://www.star.net.uk
> > ________________________________________________________________________
> >
> >
> > ______________________________________________________________________
> > This email has been scanned by the MessageLabs Email Security System.
> > For more information please visit http://www.messagelabs.com/email
> > ______________________________________________________________________
> >
> >
>
> ________________________________________________________________________
> This e-mail has been scanned for all viruses by Star. The
> service is powered by MessageLabs. For more information on a proactive
> anti-virus service working around the clock, around the globe, visit:
> http://www.star.net.uk
> ________________________________________________________________________
>
> ______________________________________________________________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> ______________________________________________________________________
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/python-win32/attachments/20061013/17cccd1d/attachment-0001.htm