Python: Saving an Excel file with the current date

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

2 Answers

  1. Phoebe- Reply

    2019-11-14

    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

  2. Quentin- Reply

    2019-11-14

    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)

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>