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.VBProj ect.VBComponent s.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 createExcelRepo rt(projectName, templateName,sa veToPath):
# acquire application object, which may start application
application = Dispatch("Excel .Application")
# create new file ('Workbook' in Excel-vocabulary) using the specified
template
workbook = application.Wor kbooks.Add("Tem plate1.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
defaultWorkshee t = workbook.Worksh eets(1)
### not needed
>
worksheet1 = workbook.Worksh eets(1)
worksheet2 = workbook.Worksh eets(2)
worksheet3 = workbook.Worksh eets(3)
### not needed
>
---->>>> wbc = workbook.VBProj ect.VBComponent s.Add(1) <<<<------ here
is the problem
wbc.Name="Modul e1"
wbc.CodeModule. AddFromString(s tr_code)
path=saveToPath +"\\"+projectNa me+"_"+template Name+".xls"
workbook.SaveAs (path)
worksheet1 = workbook.Worksh eets(1)
# make stuff visible now.
worksheet1.Acti vate()
application.Vis ible = True
and no code to call it.
I wrote some code to call it, created an empty default spreadsheet
called c:\junk\Templat e1.xls, ran it, got file-not-found, changed code
to have r"c:\junk\Templ ate1.xls" and ran it again.
Sure enough, it crashed on the VBComponents.Ad d(1) thing. Here is the
result:
C:\junk>\python 24\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 createExcelRepo rt
wbc = workbook.VBProj ect.VBComponent s.Add(1)
File "C:\Python24\Li b\site-packages\win32c om\client\__ini t__.py",
line 496, in
__getattr__
if d is not None: return getattr(d, attr)
File "C:\Python24\Li b\site-packages\win32c om\client\__ini t__.py",
line 455, in
__getattr__
return self._ApplyType s_(*args)
File "C:\Python24\Li b\site-packages\win32c om\client\__ini t__.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\\Microsof t Office\\OFFICE1 1\\1033\\xlmain 11.chm', 0,
-2146827284), N
one)
====
Interesting: "Programmat ic 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
