I have been working on a project where I am supposed to use Python for data analysis. I am stuck on one part in the project. Actually, I want to copy all the data from a table in SQL server to an Excel file with a .xlsm extension. I have written most of the code using pandas and pyodbc libraries. However, I learned that pandas doesn't support .xlsm extension so switched to openpyxl, which supposedly does. However, my code doesn't work.

Here are the relevant lines of code:

sql_table = 'table1'

sql_query = f'SELECT * FROM {sql_table};'

        # Read the processed data in SQL Server
processed_data = pd.read_sql_query(sql_query, conn_sql)

data = pd.DataFrame(processed_data)

wb = load_workbook('new.xlsm')

ws = wb['Internal']

for r in dataframe_to_rows(data, index=False, header=True):
    ws.append(r)

wb.save('new.xlsm')

I am sure about the modules I have imported, the SQL connection and other stuff. This block of code is the only problem. Do note that I want to access an already existing Excel file with a .xlsm extension and not create a new one.

Expectation:

The script runs without an error and I see the changes in the relevant Excel file.

Reality:

I receive an error which states:
ValueError: Unable to read Workbook: could not read worksheets from new.xlsm. This is most probably because the workbook source files contain some invalid XML.