Python crash when trying to generate an Excel worksheet with VBA macros

[dan_roman]

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 :-(

the code of the module that crash is (only in Excel 2003, in 2000 not):

import os
import string
from win32com.client import Dispatch, constants

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")

# store default worksheet object so we can delete it later
defaultWorksheet = workbook.Worksheets(1)

worksheet1 = workbook.Worksheets(1)
worksheet2 = workbook.Worksheets(2)
worksheet3 = workbook.Worksheets(3)

---->>>>> 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

 

[hg]

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 :-(

the code of the module that crash is (only in Excel 2003, in 2000 not):

import os
import string
from win32com.client import Dispatch, constants

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")

# store default worksheet object so we can delete it later
defaultWorksheet = workbook.Worksheets(1)

worksheet1 = workbook.Worksheets(1)
worksheet2 = workbook.Worksheets(2)
worksheet3 = workbook.Worksheets(3)

---->>>>> 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

Crash as in Office or as a Python exception ?

 

[Brian]

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 :-(

The Excel on the box you're testing this, have you set it to trust
programmatic access to the VB Project? If you haven't, I wouldn't be
surprised if that line would likely raise an exception. I'm pretty
sure Excel's security model has changed a bit between 2000 and 2003, so
perhaps this is what is biting you.

 

[John Machin]

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 ...
=================================

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