I have an Excel file that needs to be automatically modified (with a Python script) and also be available for manual modifications. It has several sheets, each containing a single table and containing formulas.
My script opens the Excel file with openpyxl
, to avoid breaking the structure inside the tables and automatically modify some rows in some specific sheets. I use openpyxl
because I need the Excel file to be manually modified the same way, meaning that users can open it, add for instance a new line, which will extend the table and fill the formulas automatically, normal behavior of tables in Excel.
After saving in Python, I need to read it in Tableau Prep, a program for data preparation. This program cannot read my formulas after I save the Excel file with openpyxl
. It only reads if I manually open the modified Excel, save it without any manual modification and close it. I immediately see that the size of the file changes, meaning that openpyxl
saves in a different way than Excel, so Excel re-organizes itself.
I need this script to do everything automatically, without manually opening the file at the end.
I am searching for a way to open an Excel file via command prompt, save it and close it. I found a simple VBA macro:
Sub SaveAndCloseWithoutModifications()
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
I did not succeed to make it work from the command line. It opens the Excel and does nothing automatically.
I tried:
start excel.exe excelfile.xlsm /mSaveAndCloseWithoutModifications
Thanks for your comments. I didn't try you vbs approach, but thanks @z32a7ul. I managed to solve the problem by doing the following: My xlsm file has a module with the code:
Sub SaveAndCloseWithoutModifications()
Application.DisplayAlerts = False
ThisWorkbook.Save
End Sub
Then, in python, after having modified and saved the file with openpyxl, I do:
import win32com.client as win32
excel = win32.Dispatch('Excel.Application')
workbook = excel.Workbooks.Open(filepath)
excel.Run('SaveAndCloseWithoutModifications')
excel.Quit()