Python Script to Automate Refreshing an Excel Spreadsheet

Excel Logo Plus Python LogoOften I run into situations where it makes sense to do analysis of a lot of database data in an Excel spreadsheet, but due to the amount of processing the spreadsheet requires when updating, it takes a long time for the spreadsheet to “Refresh All”.

One solution to this problem is to automate the spreadsheet so it refreshes every night.  The following is a small Python script that can do this using the Python for Windows Extension:

[code language=”python”]
import win32com.client
import shutil

#Based upon Code Sample from http://nbviewer.ipython.org/github/sanand0/ipython-notebooks/blob/master/Office.ipynb
#                   and from http://stackoverflow.com/questions/11832628/python-excel-macro-refresh

# Set Pathnames & Filename (Use forward slashes / instead of backslashes \ in the paths)
SourcePathName = ‘C:/WhateverYourPathIs’
FileName = ‘SpreadsheetFilename.xlsx’

# Open Excel
Application = win32com.client.Dispatch(“Excel.Application”)

# Show Excel. While this is not required, it can help with debugging
Application.Visible = 1

# Open Your Workbook
Workbook = Application.Workbooks.open(SourcePathName + ‘/’ + FileName)

# Refesh All
Workbook.RefreshAll()

# Saves the Workbook
Workbook.Save()

# Closes Excel
Application.Quit()
[/code]

3 Comments

  1. Thank you! this script works to refresh an odbc connection I have in my excel workbook. One thing I want to mention is that if you are using this script to run a SQL query it might be helpful to import the time module and then pause the python script to wait until SQL finishes otherwise your workbook will not refresh. i.e.

    import time

    Workbook.RefreshAll()

    time.sleep(600) # delays for 10 mins (allows enough time for the query to run)

  2. Thank you for the code!

    Do you know if this script will update SAP BO Analysis for excel?

Leave a Comment

Your email address will not be published. Required fields are marked *