Mac: update wb reference when saving as

[ahmedelz]

Hi,

I'm trying to save the sheet as .xlsb on Mac OS X.. I have this piece of code:

wb = xw.Book(fullname=fname)
.....
# lots of code that increases the workbook file size significantly
....
if sys.platform.startswith('darwin'):
    wb.save()
    wb.api.save_workbook_as(filename=xw._xlmac.posix_to_hfs_path(fname[:-5] + '.xlsb'), file_format=xw.constants.FileFormat.xlExcel12, overwrite=True)
wb.close()

However, this seems to fail in appscript on the save_workbook_as line with the following piece of the traceback:

    wb.api.save_workbook_as(filename=xw._xlmac.posix_to_hfs_path(fname[:-5] + '.xlsb'), file_format=xw.constants.FileFormat.xlExcel12, overwrite=True)
  File "/Library/Python/2.7/site-packages/aeosa/appscript/reference.py", line 498, in __call__
    raise CommandError(self, (args, kargs), e, self.AS_appdata)
appscript.reference.CommandError: Command failed:
          OSERROR: -50
          MESSAGE: Parameter error.
          COMMAND: app(pid=29029).workbooks[....].save_workbook_as(file_format=50, overwrite=True, filename=u'.....xlsb')

The executed command seems to be what I was expecting but not sure why it is failing. Any suggestions or hints?

[ahmedelz]

I think I found the explanation here: http://www.rondebruin.nl/mac/mac020.htm. It seems that the 'constants' are not the same on Mac vs. Windows and looks like I need to add 1.

So, the offending line needs to be updated to:
wb.api.save_workbook_as(filename=xw._xlmac.posix_to_hfs_path(fname[:-5] + '.xlsb'), file_format=(xw.constants.FileFormat.xlExcel12 + 1), overwrite=True)

But then it fails on the wb.close:

  File "/Library/Python/2.7/site-packages/aeosa/appscript/reference.py", line 498, in __call__
    raise CommandError(self, (args, kargs), e, self.AS_appdata)
appscript.reference.CommandError: Command failed:
		OSERROR: -1728
		MESSAGE: The object you are trying to access does not exist
		COMMAND: app(pid=48472).workbooks['....xlsx'].close(saving=k.no)

wb is still referring to the original workbook that was opened (.xlsx) so I'm not sure why the error says it doesn't exist.

[fzumstein]

Although the behaviour might be improved, it's kind of expected: your old workbook isn't open anymore after a save_workbook_as, so it cannot be closed.
Currently you'd need to reinstantiate a Book object with the new workbook.

[fzumstein]

Actually you're right we should fix this.