Jacob J. Walker's Blog

Scholarly Thoughts, Research, and Journalism for Informal Peer Review

Python Script to Automate Refreshing an Excel Spreadsheet

with 3 comments

Often 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:

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

# Saves the Workbook

# Closes Excel

Post Revisions:

Written by Jacob Walker

January 24th, 2015 at 9:42 am

3 Responses to 'Python Script to Automate Refreshing an Excel Spreadsheet'

Subscribe to comments with RSS or TrackBack to 'Python Script to Automate Refreshing an Excel Spreadsheet'.

  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


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


    16 Nov 16 at 10:33 am

  2. Thank you for the code!

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


    11 Apr 17 at 1:54 am

  3. If doing a “refresh all” is what is used for the SAP BO, then I believe my script should work for that.

    Jacob Walker

    13 Apr 17 at 10:05 am

Leave a Reply

%d bloggers like this: