[SOLVED] Open, save and close an Excel file from the command prompt without interaction

Open, save and close an Excel file from the command prompt without interaction

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

Solution

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()