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

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