Apologies in advance to anyone who gets this twice, but I posted this
using Thunderbird over 11 hours ago and it's not showing up ...
=================================
On 4/10/2006 8:53 PM, dan_roman wrote:
> Hi,
> I developed a script with a nice interface in Tkinter that allows me to
> edit some formulas and to generate an Excel worksheet with VBA macros
> within it. The script runs perfectlly in Office 2000, but in Office
> 2003 crash at line: "wbc = workbook.VBProject.VBComponents.Add(1)"
> Please help me :-(
Please consider helping yourself:
(1) Show what error message you got
(2) Provide the bare minimal code that will reproduce the problem.
Now read on:
> the code of the module that crash is (only in Excel 2003, in 2000 not):
>
> import os
> import string
### not used
> from win32com.client import Dispatch, constants
### do yourself a favour, use makepy
>
> str_code="""
> Dim nrfunc As Integer
> Dim cursor As Integer
> Dim i As Integer
> Dim j As Integer
>
> Sub Fill()
>
>
> 'Aflu numaru de functii din XL
> i = 1
> ..................
> """
> def createExcelReport(projectName,templateName,saveToPath):
> # acquire application object, which may start application
> application = Dispatch("Excel.Application")
>
> # create new file ('Workbook' in Excel-vocabulary) using the specified
> template
> workbook = application.Workbooks.Add("Template1.xls")
Where do you keep this file? I had to supply a full path, otherwise I
was getting a file-not-found error.
>
> # store default worksheet object so we can delete it later
> defaultWorksheet = workbook.Worksheets(1)
### not needed
>
> worksheet1 = workbook.Worksheets(1)
> worksheet2 = workbook.Worksheets(2)
> worksheet3 = workbook.Worksheets(3)
### not needed
>
> ---->>>>> wbc = workbook.VBProject.VBComponents.Add(1) <<<<------ here
> is the problem
>
> wbc.Name="Module1"
>
> wbc.CodeModule.AddFromString(str_code)
>
> path=saveToPath+"\\"+projectName+"_"+templateName+".xls"
>
> workbook.SaveAs(path)
>
> worksheet1 = workbook.Worksheets(1)
>
> # make stuff visible now.
> worksheet1.Activate()
> application.Visible = True
>
and no code to call it.
I wrote some code to call it, created an empty default spreadsheet
called c:\junk\Template1.xls, ran it, got file-not-found, changed code
to have r"c:\junk\Template1.xls" and ran it again.
Sure enough, it crashed on the VBComponents.Add(1) thing. Here is the
result:
C:\junk>\python24\python dan_roman.py
Traceback (most recent call last):
File "dan_roman.py", line 50, in ?
saveToPath=r'c:\junk',
File "dan_roman.py", line 31, in createExcelReport
wbc = workbook.VBProject.VBComponents.Add(1)
File "C:\Python24\Lib\site-packages\win32com\client\__init__.py",
line 496, in
__getattr__
if d is not None: return getattr(d, attr)
File "C:\Python24\Lib\site-packages\win32com\client\__init__.py",
line 455, in
__getattr__
return self._ApplyTypes_(*args)
File "C:\Python24\Lib\site-packages\win32com\client\__init__.py",
line 446, in
_ApplyTypes_
return self._get_good_object_(
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0,
'Microsoft Office
Excel', 'Programmatic access to Visual Basic Project is not
trusted\n', 'C:\\Pr
ogram Files\\Microsoft Office\\OFFICE11\\1033\\xlmain11.chm', 0,
-2146827284), N
one)
====
Interesting: "Programmatic access to Visual Basic Project is not
trusted"
So I opened up Template1.xls and did Macros > Security > Trusted
Publishers and ticked "Trust access to Visual Basic Project" and saved
the file and exited Excel and ensured there were no ghost Excel.exe
processes lurking.
[Confessions (1) I'm not in the habit of mucking about with VBA, so I
didn't get this right first time (2) William Ferschlugginer Gates was
mentioned once or twice, not in a complimentary fashion :-)]
Now the code appears to work. It creates a file, in which your VBA guff
can be seen by opening it with Excel and entering the VBA editor.
So, the $64,000 question is: "What error message did *you* get?"
My versions:
Excel 2003 (11.8033.8036) SP2
pywin32 build 209
Python 2.4.3
HTH,
John
--
http://mail.python.org/mailman/listinfo/python-list
