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

You are viewing an old revision of this post, from January 24, 2015 @ 10:15:33. See below for differences between this version and the current revision.

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
Workbook.RefreshAll()

# Saves the Workbook
Workbook.Save()

# Closes Excel
Application.Quit()

Post Revisions:

Changes:

January 24, 2015 @ 10:15:33Current Revision
Content
Unchanged: <img class="alignleft" src="http://www.dev- explorer.com/ images/articles/ excel-spreadsheets-and-python/ python_excel_logos.png" alt="" width="129" height="112" />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".Unchanged: <img class="alignleft" src="http://www.dev- explorer.com/ images/articles/ excel-spreadsheets-and-python/ python_excel_logos.png" alt="" width="129" height="112" />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".
Unchanged: 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 <a href="http:// sourceforge.net/projects/ pywin32/files/ pywin32/">Python for Windows Extension</a>:Unchanged: 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 <a href="http:// sourceforge.net/projects/ pywin32/files/ pywin32/">Python for Windows Extension</a>:
 Added: <!--more-->
Unchanged: Unchanged: [/code]

Note: Spaces may be added to comparison text to allow better line wrapping.

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

    Workbook.RefreshAll()

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

    Jen

    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?

    Php

    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: