Python – Copy most recent file in folder with today’s date and execute macro found in file [closed]

Every morning I have to copy yesterday’s file, paste it as today’s file, refresh a PowerQuery via a macro and save the file with today’s date.

I’m beginning to learn Python and I have found a way to copy and paste the file with today’s date. Because I want it to run with no actual file name constraints, I want the code to be able to select the most recently modified file, which would be always be yesterday’s file, copy it with today’s date in the file name, refresh the PowerQuery and run the macro.

My code so far is a simple copy paste of the folders but I’d like to insert functions to have:

import shutil

original = r"C:UsersnameDesktop9.13.2021 - Daily Item Record.xlsm"
target = r"C:UsersnameDesktop9.14.2021 - Daily Item Record.xlsm"

shutil.copyfile(original, target)

The above involves changing the dates every day. Instead, I’d like to have the original file path be the most recent file in the directory and the target file be the copy with today’s date.


This should work:

import os
import shutil
import time

my_dir = "C:\Users\nameDesktop\"

# A dictionary for storing the files
files_dict = {}

# Put the creation time and the path for each file in the directory into files_dict
for f in os.listdir(my_dir):
    file = my_dir + f
    file_createdtime = os.path.getctime(file)
    files_dict[file_createdtime] = file

# Get the most recently created file
most_recent_file = files_dict[max(files_dict)]

# Copy and rename the file
shutil.copy(file, os.path.dirname(file) + time.strftime("\%m.%d.%Y - Daily Item Record.xlsm"))

In the for loop, I create a dictionary of each file’s creation time and path. The max() function returns the highest value in an iterable sequence (in this case, the dictionary). In the code below, it returns the creation date for the most recently created file, which I can use to get the file path from the dictionary and put it in most_recent_file.

In the last line, I copy the file with a new name in the format 09.13.2021 - Daily Item Record.xlsm using the time.strftime() function. See the python documentation for more information on time.strftime. Basically what it does is replace certain patterns in the given string with certain units of time. Here, it replaces "%m" with the current month number, "%d" with the current day of the month, and "%Y" with the current year.

If you have any questions or something wasn’t right, let me know!