467,074 Members | 905 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,074 developers. It's quick & easy.

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

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

# 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

Oct 4 '06 #1
  • viewed: 3437
Share:
3 Replies
hg
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 :-(

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

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

Oct 4 '06 #2
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 :-(
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.

Oct 4 '06 #3

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

Oct 4 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
699 posts views Thread by mike420@ziplip.com | last post: by
6 posts views Thread by Anand | last post: by
852 posts views Thread by Mark Tarver | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.