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 <[EMAIL PROTECTED]>
Date: Oct 13, 2006 6:22 PMSubject: Re: [python-win32] VBA MACRO and PythonTo: Michael Pearmain <[EMAIL PROTECTED]>
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 usexl = 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 <[EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]] Sent:
13 October 2006 13:21To: Michael PearmainSubject: 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/ ) PRB: Programmatic Access to Office XP VBA Project Is Denied
Maybe that is causing the problem?
Floris
On 10/13/06, Michael Pearmain <[EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]] Sent:
13 October 2006 12:46To: Michael PearmainSubject: 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')>>> 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 <[EMAIL PROTECTED]
> 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 _
