Python Script to Automate Refreshing an Excel Spreadsheet

by

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.

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

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

Leave a Comment