Use win32com to Open Excel file with formulas, add some delay, & save as CSV

Question

I have an Excel file that has a bunch of formulas in them. It takes time for the formulas to properly load (~1-2 mins), so I'm trying to add some delay between opening the file and saving the loaded file. After this is done, I'm hoping to save a copy of the fully loaded Excel file as a new CSV with a different name, so that I have both the fully loaded Excel file and new CSV file.

To be clear, I'm trying to do the following tasks in this order:

Open the Excel file
Add some delay (~1-2 mins long)
Save the fully loaded Excel file
Save a copy of the fully loaded Excel file as a CSV with a different name

I've managed to do Steps 1 and 3 as seen below, but am having trouble with Steps 2 and 4. I've noticed Step 2 is quite important, since the way my code is written right now does not allow for enough time for the formulas to load, leading to an incompletely-loaded file.

What is the best way to do Steps 2 and 4, i.e., add delay and then save the full file as a CSV?

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')

#opens file denoted by PATH
workbook = excel.Workbooks.Open(PATH)

#saves file
workbook.Save()

#closes and quits Excel
workbook.Close()
excel.Quit()

Note this code is based on this post.

I'm quite new to this package. How would one go about using the wait command? I would really appreciate any links to relevant documentation as well. — lithium123, Nov 23 '21 at 07:17
Quick search on here for "delay or wait in vba" gives https://stackoverflow.com/a/24510978/4961700 and that is just one of many... — Solar Mike, Nov 23 '21 at 07:19

DS_London · Accepted Answer · 2021-11-23T12:27:17.897

You can kick off the Excel calculation, then have your Python code wait:

import time

#...
workbook = excel.Workbooks.Open(PATH)
excel.CalculateFull() #If Calculation is set to Manual
#Wait 10 seconds
time.sleep(10)
workbook.Save()
#...

Of course, you don't know if 10 seconds is too long or too short a time to wait.

A more precise way is to start the calculation and then wait for Excel to tell you it has finished. The Excel Application object signals a AfterCalculation event when it has completed the calculation tree. You can set up your Python code to wait for this event to be signalled before proceeding.

Full code:

import win32com.client as wc
import pythoncom #From package pywin32
import win32api
import win32con
from os.path import join

#Class to handle the SyncObject events from Outlook
class CalcHandler(object): 
    def OnAfterCalculate(self):
        #Calculation has finished so send WM_QUIT to message loop
        win32api.PostThreadMessage(win32api.GetCurrentThreadId(), win32con.WM_QUIT, 0, 0)       
        print('... calculation complete')

#Get the application Dispatch interface
xl = wc.gencache.EnsureDispatch('Excel.Application')

path = 'path_to_directory'

#opens a file
workbook = xl.Workbooks.Open(join(path,'test.xlsx'))

if workbook is not None:
    #Create an event handler
    wc.WithEvents(xl,CalcHandler)

    print('Starting calculation ...')
    #Force a calcuation
    xl.CalculateFull()

    #This will block until a WM_QUIT message is sent to the message queue
    pythoncom.PumpMessages()

    #saves file as a CSV
    xl.DisplayAlerts=False
    workbook.SaveAs(join(path,'test.csv'),wc.constants.xlCSV)

    #closes and quits Excel
    workbook.Close()

if xl.Workbooks.Count == 0:
    xl.Quit()
    xl=None

Use win32com to Open Excel file with formulas, add some delay, & save as CSV

1 Answers1