0

This is my first post, and I've been searching diligently for an answer, so please bear with me!

I ultimately want to be able to modify an existing .xslm file to fill it with motion data I've extracted within Cinema-4D via python.

I did try the keep_vba=True as suggested here, but still got the error described below. My macro-enable workbook is from Excel 2013, with a functioning macro I also wrote. My working code is:

import os
from openpyxl import load_workbook

homeDir = os.path.expanduser('~')

openName = 'Timing_CHART.xlsm'
openPath = os.path.dirname(__file__) + "\\" + openName
saveName = raw_input('Please enter a save name: ')
savePath = homeDir+'\\Desktop\\'+saveName+'.xlsm'

wb = load_workbook(openPath, keep_vba = True)
ws = wb.active

ws['B10'].value = 'Loader'  #Just as a test
ws['D10'].value = 25

wb.save(savePath)

When I run this code via the Enthought Canopy environment (Python 2.7.6 64-bit) it works just I expected--fantastic. When I try to run this same code in the same python version as is used in my Cinema-4D installation (Python 2.6.4 64-bit) it works perfectly up until the wb.save() command. Then I get a whole pile of errors:

Traceback (most recent call last):
  File "<pyshell#14>", line 1, in <module>
    wb.save(savePath)
  File "C:\Python26\lib\site-packages\openpyxl-2.3.2-py2.6.egg\openpyxl\workbook\workbook.py", line 263, in save
    save_workbook(self, filename)
  File "C:\Python26\lib\site-packages\openpyxl-2.3.2-py2.6.egg\openpyxl\writer\excel.py", line 239, in save_workbook
    writer.save(filename, as_template=as_template)
  File "C:\Python26\lib\site-packages\openpyxl-2.3.2-py2.6.egg\openpyxl\writer\excel.py", line 222, in save
    self.write_data(archive, as_template=as_template)
  File "C:\Python26\lib\site-packages\openpyxl-2.3.2-py2.6.egg\openpyxl\writer\excel.py", line 68, in write_data
    archive.writestr(ARC_ROOT_RELS, write_root_rels(self.workbook))
  File "C:\Python26\lib\site-packages\openpyxl-2.3.2-py2.6.egg\openpyxl\writer\workbook.py", line 88, in write_root_rels
    arc = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS))
  File "C:\Python26\lib\zipfile.py", line 831, in read
    return self.open(name, "r", pwd).read()
  File "C:\Python26\lib\zipfile.py", line 594, in read
    bytes = self.fileobj.read(bytesToRead)
TypeError: integer argument expected, got 'long'

It wasn't until I was writing this post (and testing out my statements as I go along to make sure what I'm saying is accurate), that I discovered the difference between it working/failing with python v2.7.6 in Enthought Canopy vs. python v2.6.4 in Windows and Cinema-4D.

Originally I thought it was specific to C4D, but since C4D is running Python 2.6.4 as well, I'm hopeful that this can be just narrowed down to a fixable issue with the python version... or learn for sure that it's impossible with python 2.6.4.

Any help/advice is much appreciated!

Community
  • 1
  • 1
Marcus Virtus
  • 1
  • 3

1 Answers1

0

Okay, did not realize the difference in python version would have an effect... but a little more digging in that arena revealed that openpyxl stopped supporting python 2.5 after version 1.7. I'm assuming something similar happened with python 2.6 between openpyxl 1.8 to the current 2.4.

I installed openpyxl v1.8.6 to the C4D python 2.6.4 library location (along with setuptools-0.9.6-py2.6, because it didn't like the openpyxl-1.8.6-py2.6.egg otherwise) and modified one line of my code in C4D. With this update, my code worked in the C4D python v2.6.4 install like a charm!

import os
from openpyxl import load_workbook

homeDir = os.path.expanduser('~')

openName = 'Timing_CHART.xlsm'
openPath = os.path.dirname(__file__) + "\\" + openName
saveName = raw_input('Please enter a save name: ')
savePath = homeDir+'\\Desktop\\'+saveName+'.xlsm'

wb = load_workbook(openPath, keep_vba = True)
ws = wb.active

ws['B10'].value = 'Loader'  #Just as a test
ws['D10'].value = 25

wb.save(savePath)

When writing values to the cells of the new .xlsm file, with openpyxl v.2.4 I originally had the line:

ws.cell(column = curCol, row = curRow, value = eachKey)

Which had to be changed to:

ws.cell(column = curCol, row = curRow).value = eachKey

I hope that can help someone in the future!

Marcus Virtus
  • 1
  • 3