A small utility that solves a specific problem: refreshing Excel files with external data connections through automation.
I initially tried pandas and openpyxl, but neither could trigger Excel's native refresh functionality. The solution was win32com, which provides direct access to Excel through Windows COM interface.
The core logic is simple:
import win32com.client
import pythoncom
filepath = 'some_file.xlsx'
pythoncom.CoInitialize()
excel_app = win32com.client.Dispatch("Excel.Application")
workbook = excel_app.Workbooks.Open(filepath)
workbook.RefreshAll()
This approach opens Excel, refreshes all data connections (Power Query, database links, web queries), and can save or close the file. Useful for scheduled reporting where workbooks need to pull fresh data before distribution.
The full script includes error handling, command-line arguments, and logging. Available on GitHub.