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,saveToP ath):
# 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
