Refresh multiple Excel files with different file paths

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 ?

CodePudding user response：

you want to make a re-useable function that takes in a filepath and Xlsx object. You can then make a simple loop over all the paths in a list

def refresh_excel(Xlsx, filepath):
    #everything under this indented code block is run every time you call refresh_excel
    workbook = Xlsx.Workbooks.Open(filepath)
    workbook.RefreshAll()
    workbook.CalculateUntilAsyncQueriesDone()
    workbook.Save()
    workbook.Close() #this might not be needed
    return True

#this unindented block sets up you Excel application and stores your list of excel filepaths you want to refresh
Xlsx = win32.DispatchEx('Excel.Application')
Xlsx.DisplayAlerts = False
Xlsx.Visible = False

file_list = ['path1.xlsx', 'path2.xlsx', 'path3.xlsx']
for f in file_list:
   refresh_excel(xl, f)

Xlsx.Quit()

CodePudding user response：

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