Refresh multiple Excel files with different file paths - Using Python

Question

Hi I have just started learning python/programming and have the following problem:

I want to refresh several excel files with python. However, all these excel files have a completely different file path, so they are not all stored in one folder.

Using pypiwin32 it was no problem to write a code that refreshes one excel file. To refresh several excel files with different file paths, currently I solved it this way:

import win32com.client as win32

Xlsx = win32.DispatchEx('Excel.Application')
Xlsx.DisplayAlerts = False
Xlsx.Visible = False

book1 = Xlsx.Workbooks.Open('<path_to_excel_workbook1>')
book2 = Xlsx.Workbooks.Open('<path_to_excel_workbook2>')


book1.RefreshAll()
book2.RefreshAll()

Xlsx.CalculateUntilAsyncQueriesDone()

book1.Save()
book2.Save()

book1.Close(SaveChanges=True)
book2.Close(SaveChanges=True)

Xlsx.Quit()

del book1
del book2
del Xlsx

However, the entire code becomes unnecessarily long when there are 50 or more Excel files to be updated. Is there a way to iterate through all Excel files, without writing a line of code for every Excel file to execute RefreshAll(),Save() and Close()? Maybe by using a loop or similiar solutions ?

Answer 1

I would do something like that.

import win32com.client as win32

file_paths = ['path_one', 'path_two']

Xlsx = win32.DispatchEx('Excel.Application')
Xlsx.DisplayAlerts = False
Xlsx.Visible = False

for path in file_paths:
    book = Xlsx.Workbooks.Open(path)
    book.RefreshAll()
    Xlsx.CalculateUntilAsyncQueriesDone()
    book.Close(SaveChanges=True)

Xlsx.Quit()