Python: Saving an Excel file with the current date

Question

I'm trying to save a new Excel File in the same folder with the original file name and current date, using Python 3.6 and the Datetime module.

The file is saved, but when I open the created file it says Excel cannot open the file 'AllData 2017_08_04.xlsm' because the file format or file extension is not valid.

How can I get the file to save correctly with the datestring included?

from datetime import datetime

os.chdir(r'\\URL\All Data Folder')
wb = openpyxl.load_workbook('AllData.xlsm')
datestring = datetime.strftime(datetime.now(), ' %Y_%m_%d')
wb.save('AllData' + datestring + '.xlsm')

what happens if you use a `.xlsx` extension? The Macros may be affecting your desired output. — MattR, Aug 04 '17 at 13:27
I'm thinking that's the problem. I just tried on my own `.xlsx` file and it worked just fine. — TheDetective, Aug 04 '17 at 13:31
Read About [Note: The following will fail:](http://openpyxl.readthedocs.io/en/default/tutorial.html#loading-from-a-file) — stovfl, Aug 04 '17 at 18:48

2Obe · Accepted Answer · 2017-08-05T20:32:21.053

2

This should work for your issue. Use pandas instead

import pandas as pd
from datetime import datetime

#Use pandas to adress the issue

os.chdir(r'\\URL\All Data Folder')
wb=pd.read_excel(io=r"YOUR PATH\AllData.xlsm")#Fill in your path
datestring = datetime.strftime(datetime.now(), ' %Y_%m_%d')

wb.to_excel(excel_writer=r"YOUR PATH\{0}".format('AllData' + datestring + '.xlsm'))#Fill in your path

If this does not work, look at this Question which should answer your problem because it is about how to save a excel file with xlsm ending

edited Aug 05 '17 at 20:32

answered Aug 05 '17 at 20:21

2Obe

3,570
6
30
54

Your welcome! If it helped you please mark it as answer – 2Obe Aug 15 '17 at 19:25

score 0 · Answer 2 · answered Aug 04 '17 at 13:40

As I mentioned in the comments, there may be some VBA code that is embedded in the workbook - hence the .xlsm extension.

If you do not need the macro/VBA code try: wb.save('AllData' + datestring + '.xlsx')

Also, If you check the load_workbook() documentation there is a keep_vba argument. try:

wb = openpyxl.load_workbook('AllData.xlsm', keep_vba=True)

Python: Saving an Excel file with the current date

2 Answers2