Re: [python-win32] VBA MACRO and Python

Fri, 13 Oct 2006 10:30:39 -0700

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 PM
Subject: Re: [python-win32] VBA MACRO and Python
To: 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 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 <[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: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/ ) 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: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 <[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
______________________________________________________________________
 

_______________________________________________
Python-win32 mailing list
Python-win32@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
______________________________________________________________________ 

_______________________________________________
Python-win32 mailing list
Python-win32@python.org
http://mail.python.org/mailman/listinfo/python-win32

Reply via email to