I have a .py process than opens an existing .xlsb file, reads existing data, undertakes my required analysis and writes results to various sheets on the same .xlsb. This works very effectively, however I cannot see how to add code to automate the Save and Close of the .xlsb at the end of the .py. My current solution is to do this using File/Save/Close manually in Excel after the .py completes.
I use open_xlsb with fairly well documented code to access and read the existing xlsb data, excerpt:
xlsbfilename = "full path to Myfile.xlsb"
with open_xlsb(xlsbfilename) as wb:
with wb.get_sheet(xlsheet) as sheet:
for row in sheet.rows():
# do stuff #
The problem is there seems to be no Save and/or Close capability in this otherwise useful function, is there a way? I am able to process the data with other Excel/python tools, e.g. I can use dataframes, numpy with this original .xlsb data, but whenever I try to use any Save or SaveAs with tools such as xlwings or pandas, I get error messages advising 'not available for my file type', excerpt such as:
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', '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.', 'xlmain11.chm', 0, -2146827284), None)
When I change the same code to use instead a test .xls rather than .xlsb, the code will work - it is simply that using .xlsb fails. The obvious reply is - 'just change the file extension' but that is not the requirment here. I am looking for any form of code that can add to a .py to Save and Close the open .xlsb file. It seems a simple requirement, maybe I am missing something simple and would be happy to learn that was the case?