[python-win32] Opening, Modifying, and Saving an Excel File from Python?

[The Little Guy]

Hi,

 

I apologize for the lengthy post.  

 

I'm using Python 2.7, Win 7, Excel 2003.  When trying to enter a time object
and text data into a, simple, Excel 2003 file.  I've tried different
combinations for saving the file.  I've used workbook.Save(),
workbook.SaveAs(filename), etc.  Sometimes I receive a replace file dialog
box, but other times, I receive following message, when it tries to save: 

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------

Traceback (most recent call last):

File "C:\Users\tech\Documents\Python\row_end.py", line 63, in <module>
workbook.Save()

File
"C:\Python27\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-00000
0000046x0x1x5\_Workbook.py", 

line 207, in Save return self._oleobj_.InvokeTypes(283, LCID, 1, (24, 0),
(),) com_error: (-2147352567, 'Exception occurred.', 

(0, u'Microsoft Office Excel', u"Your changes could not be saved to
'simple.xls' because of a sharing violation. Try saving to a different
file.", u'C:\\Program Files (x86)\\Microsoft
Office\\OFFICE11\\1033\\xlmain11.chm', 0, -2146827284), None)

 

What I'd like to do is: 

 

Open an excel file, stored either locally or on a net share,

Look for the last row in a particular sheet,  say Sheet1,

Append a time object to last row + 1, first column,

Append text data to last row + 1, second column,

Ask to save the file (1 = save, 2 = discard changes)

Save the Excel workbook, 

Close and Quit without confirmation dialogs.

 

Basically, there is a sharing violation, sometimes excel.exe is in task
manager and sometimes it is not, but the sharing 

violation message appears to cause the script to crash.  I've tried saving
the file to a different file name, which works, 

and then closing the current file, then removing it, but still get a sharing
violation as it appears to still be in use by some 

unknown process, probabaly the OS, though can't track which process it is. 

 

Is there a simple way to save an opened Excel file without getting errors?
As win32com documentation (on python side) 

is scarce, I'm using xlrd to get the last row on the excel sheet. 

 

I'm using this simple test code, which, due to, trial and error
experimentation, has grown a bit unwieldy:

 

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------

#!/usr/bin/env python

 

import shutil                                          # shell utility
module to work with os

from xlrd import open_workbook           # Third party library allows us to
open Excel files, find last row.

import pythoncom                                 # Allows us to use
MakeTime(date_object)

import win32com.client                          # Allows to connect to
Windows object using COM objects.

import os, sys, time                              # Allows us to work with
time and os functions.

 

# Set the Excel file name, working directory and path

old_file_name = 'simple.xls'

working_dir = r"C:\Users\some_user\Documents\Python" + os.sep

old_file_path = os.path.join(working_dir, old_file_name)

 

#-----------------------------xlrd
section-----------------------------------------------

# Open the Excel file for reading

book = open_workbook(old_file_path, on_demand=True)  

sheet = book.sheet_by_name('Current')

lst_row = sheet.nrows

print lst_row

#-------------------------Today's date
section-------------------------------------

# Returns date object (yyyy-mm-dd)

today = date.today()    print today

date_today = pythoncom.MakeTime(today)  # Return date/time object (mm/dd/yy
hh:mm:ss)

print date_today

#---------------------------------------------------------------------------
---------

 

# The win32com function to open Excel.

xlApp = win32com.client.Dispatch("Excel.Application")

xlApp.Visible = True

 

# Open the file we want in Excel

workbook = xlApp.Workbooks.Open(old_file_path)

 

# Extract some of the file's components we may need.

workbook = xlApp.ActiveWorkbook

 

xlApp.Sheets('Current').Select()

activesheet = xlApp.ActiveSheet

xlSheet.Cells(lst_row + 1, 1).Value = "Time Object"  # Text data for now

xlSheet.Cells(lst_row + 1, 2).Value = date_today

 

save_file = int(raw_input("Save the data Yes = 1, No = 2? "))

try:

    if save_file == 1:

        workbook.Saved = 0

        workbook.Save()

        workbook.Close(SaveChanges=True)

    else:

        print 'File not saved!'

        workbook.Close()

    xlApp.Quit()

    del activesheet

except KeyboardInterrupt:

    print 'Error: '

 

xlApp.Visible = 0

xlApp = None

del xlApp

del workbook

pythoncom.CoUninitialize()

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------

 

Regards,

Little Guy

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-win32/attachments/20110820/68481238/attachment-0001.html>