473,574 Members | 2,593 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.VBProj ect.VBComponent s.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 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")

# store default worksheet object so we can delete it later
defaultWorkshee t = workbook.Worksh eets(1)

worksheet1 = workbook.Worksh eets(1)
worksheet2 = workbook.Worksh eets(2)
worksheet3 = workbook.Worksh eets(3)

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

Oct 4 '06 #1
3 3993
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.VBProj ect.VBComponent s.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 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")

# store default worksheet object so we can delete it later
defaultWorkshee t = workbook.Worksh eets(1)

worksheet1 = workbook.Worksh eets(1)
worksheet2 = workbook.Worksh eets(2)
worksheet3 = workbook.Worksh eets(3)

---->>>>> 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
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.VBProj ect.VBComponent s.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.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

Oct 4 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
35515
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I would like to open an existing MS Excel spreadsheet and extract information from specific worksheets and cells. I'm not really sure how to get started with this process. I ran the COM Makepy...
699
33616
by: mike420 | last post by:
I think everyone who used Python will agree that its syntax is the best thing going for it. It is very readable and easy for everyone to learn. But, Python does not a have very good macro capabilities, unfortunately. I'd like to know if it may be possible to add a powerful macro system to Python, while keeping its amazing syntax, and if it...
4
5669
by: adrian | last post by:
hello! i am using visual studio 2000 and am trying to insert a formúla into an excel cell. the code goes like this: with excel .workbooks.open (datei(i)) .activesheet.name ="statistik 2004" .activesheet.range("b7").select() .selection.insert()
6
15983
by: Anand | last post by:
Hello, Can I get some help on how to read the excel files using python? from win32com.client import Dispatch xlApp = Dispatch("Excel.Application") xlWb = xlApp.Workbooks.Open("Read.xls") xlSht = xlWb.WorkSheets(1) But sadly, I am unable to proceed further about how to read the cells of the
1
3574
by: Muskito | last post by:
HELP!!! Hello All, I'm using VB.net 2003 and trying to update data in Excel worksheet. The program selects data from the excel, updates something in the MSSQL DB and then tries to update something back to the excel worksheet. My problem is that i'm having this annoying exception: "Operation must
852
28160
by: Mark Tarver | last post by:
How do you compare Python to Lisp? What specific advantages do you think that one has over the other? Note I'm not a Python person and I have no axes to grind here. This is just a question for my general education. Mark
1
4767
by: Girish | last post by:
Hi, I want to embed a txt document into an excel using python. Here is my code, but i get an error message =================================================== Traceback (most recent call last): File "C:\Documents and Settings\kusumap\Desktop\Girish.py", line 7, in ? worksheet.OLEObjects.Add(Filename="C:\Documents and Settings
0
1813
by: Smish | last post by:
I have created a class library which has user defined functions for excel. One of functions need reference to current excel so as to insert value to many cell. I have given the code below showing how i am getting active worksheet. The problem occurs when i am trying to put value in to cell or range. //Code Excel.Application oXL = null; oXL...
0
1683
by: serhio | last post by:
Hello, I have a problem with Microsoft.Office.Interop.Excel.Range.Group() method. The problem is that I can call this method only 7 times, before it throws me an exception. I don't understand quite well why this happens, and for witch documents this affirmation is valid, but the problem exists, probably,
0
7736
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8067
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8249
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7826
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8107
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6464
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5631
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3748
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
1071
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.