CODEWITHSUNDEEP
pythonexcelvbaxlsmxlwings
BPT
BPT

Reputation: 1

"xlwings" : does not support writing of .xlsm files?

I've tried a simple test using "xlwings_0.3.4" to open an excel .xltm file and save it again, to make sure that the VBA modules are kept. I couldn't get it to work.

If I give a file extension in the save step, the file is saved as an .xlsx file. The module is carried along, but with the extension change it isn't recognized as a valid VBA module. If I do not specify a file extension, it is automatically saved as .xlsx:

WB=xlwings.Workbook('template.xltm')
WB.save('outfile')
WB.close()

This gives an xlsx file.

Trying to set the file to xlsm generates an error:

WB.save('outfile.xlsm')
WB.close()
xl_workbook.SaveAs(path)

will generate an error:

xl_workbook.SaveAs(path)
File "<COMObject Open>", line 7, in SaveAs
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft          Excel', u'This extension can not be used with the selected file type. Change the   file extension in the File name text box or select a different file type by changing the Save as type.', u'xlmain11.chm', 0, -2146827284), None)

This seems to be inherent to the application object returned by GetActiveObject('Excel.Application').

Is there a switch (like the 'keep_VBA=True' switch in openpyxl?), or is it not possible to save xlsm files with xlwings?

Upvotes: 0

Views: 5514

Answers (1)

Felix Zumstein
Felix Zumstein

Reputation: 7070

What are you trying to do with xl_workbook.SaveAs(path)?

The following works:

>>> from xlwings import Workbook
>>> wb = Workbook(r'C:\full\path\to\file.xlsm')
>>> wb.save('new_name.xlsm')

Without specifying a full name when saving, it'll currently (v0.3.4) save it in the default Excel directory, which probably should be improved to be the current Python working directory to be in line with what it does with a new file.

Note that after saving a Workbook under a new name, wb above still refers to the old file and so wb.close() will have no effect on the new file. This should also be improved so that Workbook.save() returns the new Workbook object. I'll open issues on the GitHub page to cover these improvements.

Upvotes: 1

Related Questions