2

For a process I am maintaining, I have a script that creates a csv file and then I copy the csv file into an Excel workbook with buttons that activate macros. This process works just fine.

I am trying to improve that process by writing a script that builds the workbook directly, thus eliminating a step. I thought the best way to do that was to create a template workbook where the first worksheet has the macro button. Then I would simply copy the template workbook, add in my data and save the new workbook under a new custom name. My test code is below:

import csv, os, sys, xlrd, xlwt, xlutils, shutil
from copy import deepcopy
from xlutils import save
from xlutils.copy import copy

templatefile = 'N:\Tools\Scripts-DEV\Testing_Template.xls'
Destfile = 'N:\Tools\Scripts-DEV\Testing_Dest.xls'

shutil.copy(templatefile,Destfile)

# Works fine up to here.  
# If you look at the new file, it has the button that is in the template file.

rb = xlrd.open_workbook(Destfile)

rs = rb.sheet_by_index(0)

wb = copy(rb)  

wb.get_sheet(0).write(3, 0, 'Due Date')
wb.get_sheet(0).write(3, 1, 'Name')
wb.get_sheet(0).write(3, 3, 'Category')
wb.get_sheet(0).write(3, 4, 'Number')

wb.save(Destfile)

Here is where the problem shows up. After you save, the macro button disappears. I've been looking for a couple days but I haven't (yet) found a way to save the updated Excel file without losing the macro button.

I've been looking at Preserving styles using python's xlrd,xlwt, and xlutils.copy but that doesn't quite meet my needs as I'm trying to preserve a button, not a style.

Does anyone know a way to do this?

I'm about to start looking at alternatives to xlutils, xlrd and xlwt as well, but I thought I'd ask here first.

Community
  • 1
  • 1
user1070061
  • 473
  • 2
  • 5
  • 11
  • Thinking that maybe copy didn't copy over the macro button, I thought I'd try using deepcopy() by putting in these lines: wb1 = deepcopy(rb) wb = xlrd.open_workbook(wb1) but that gets me the following error: > File "N:\Tools\Scripts-DEV\Scan_Analysis_DEV\tcompliance8.py", line 25, i n wb.get_sheet(0).write(3, 0, 'Due Date') File "C:\Python27\lib\site-packages\xlrd\__init__.py", line 984, in get_sheet raise XLRDError("Can't load sheets after releasing resources.") xlrd.biffh.XLRDError: Can't load sheets after releasing resources. – user1070061 Aug 08 '12 at 19:56

1 Answers1

1

From you comment part C:\Python27\ I deduce that you are on Windows. In that case you are probably better off with using pywin32 and a template .xls or .xlsm file.

Open the file using os.startfile(filename) then connect using workbook = win32com.client.GetObject(filename). The resulting workbook can be filled with the data and written to a new file with `workbook.SaveAs(newfilename).

Anything you do not touch explicitly is preserved. Excel is, of course, somewhat better at that than xlrd, xlwt and xlutils.

Anthon
  • 69,918
  • 32
  • 186
  • 246