Open and save excel workbooks to repair them

Question

I have some broken workbooks in a folder that can be read by openpyxl, but if I open them and save they are fixed. Do you have any recomendations as to how this could be done efficiently.

At the moment my solution is to use pyautogui, to click each individual file to open and save it. The solution is slow and I cant select files by name.

I hope you can point me in a better direction.

#Update: Solution 1: I find and open the files with the following code:

filename = ["C_72_00_a.xlsx", "C_73_00_a.xlsx", "C_74_00_a.xlsx", "C_76_00_a.xlsx"]
path = "D:\\Python\\Intradag_opdater\\04\\2022-04-04\\LCR skema"

def clean(file_in):
    with open(os.path.join(path, file_in), 'w+') as f:
        pyautogui.hotkey('ctrl', 's')

for x in filename:
    clean(x)

When a file is opened I press "ctrl" + "s".

Solution 2:

Basically the same but here I useto open the file:

file_t = os.path.join("D:\\Python\\Intradag_opdater\\04\\2022-04-04\\LCR skema", "C_76_00_a.xlsx")
os.startfile(file_t,'edit')

I hope this helps someone else in the future.

Answer 1

This solves the issue:

filename = ["C_72_00_a.xlsx", "C_73_00_a.xlsx", "C_74_00_a.xlsx", "C_76_00_a.xlsx"]
path = "D:\\Python\\Intradag_opdater\\04\\2022-04-04\\LCR skema"

def clean2(file_in):
    #solution 2
    open = os.path.join(path, file_in)
    os.startfile(open,'edit')
    #tilføj time sleep start og luk excel
    time.sleep(5)
    pyautogui.hotkey('ctrl', 's')
    pyautogui.hotkey('alt', 'f4')


for x in filename:
    clean2(x)
    print(x)

If I use os open like below the files get corrupted I don't know why, but maybe it will work for someone:

def clean(file_in):
    with open(os.path.join(path, file_in), 'w+') as f:
        pyautogui.hotkey('ctrl', 's')

for x in filename:
    clean(x)
    print(x)