Let us see how to automate refreshing an Excel Spreadsheet with Python
We’ll utilize the pywin32 module to open the Excel application and workbooks.
pywin32 :
The pywin32 package has been around for a long time. In fact, Mark Hammond and Andy Robinson published a book on this subject in 2000. Despite being 18 years old (which makes me feel really old:), the underlying technology and concepts are still functional today. Pywin32 is essentially a very thin Python wrapper that allows us to interact with COM objects and automate Windows applications with Python. The strength of this approach is that you can use Python to do almost anything that a Microsoft application can do. The disadvantage is that you must run this on a Windows system with Microsoft Office installed.
- Python Program to Convert Excel to PDF
- Python Google Sheets Excel Prerequisites
- Convert CSV to Excel using Pandas in Python
Installation of the pywin32 module:
pip install pywin32
Syntax:
File.Workbooks.open(Filepath)
Parameters
Filepath: It is the path of the specified file
Then, using RefreshAll() function to refresh the file.
Workbook.RefreshAll
Let us take an example of demo.xlsx excel spreadsheet as shown below:
Python Script to Automate Refreshing an Excel Spreadsheet
Approach:
- Import the client function from win32com module using the import keyword
- Pass “Excel.Application” as an argument to the win32com.client.Dispatch() function to open the Excel software.
- Store it in a variable.
- We set visibility of excel software on(optional) using visible.
- Open the workbook by passing the Excel file name as an argument to the Workbooks.open() function and store it in another variable.
- Refresh all the sheets of the workbook using the RefreshAll() function.
- Save the above Workbook using the save() function.
- Close the Excel file using the Quit() function.
- The Exit of the Program.
Below is the implementation:
# Import the client function from win32 module using the import keyword import win32com.client # Pass "Excel.Application" as an argument to the win32com.client.Dispatch() # function to open the Excel software # Store it in a variable. excelfile = win32com.client.Dispatch("Excel.Application") # We set visibility of excel software on(optional) using visible excelfile.Visible = 1 # Open the workbook by passing the Excelfile name as an argument to the # Workbooks.open() function and store it in another variable. Work_book = excelfile.Workbooks.open("demo.xlsx") # Refresh all the sheets of the workbook using the RefreshAll() function Work_book.RefreshAll() # Save the above Workbook using the save() function Work_book.Save() # Close the Excel file using the Quit() function excelfile.Quit()
Output:
Here it refreshes the excel page