Using win32com/python to recalculate excel sheet that uses an add-in

Question

Description of problem: I have been manually using a spreadsheet that works with an excel add-in. I am trying to automate it with win32com & python. I can input a value into an input cell, but the sheet does not recalculate. When I try to read the output cell, I get an error as described below. Also, after running the below code, when I open up the sheet manually, I see that the calculated cells all say #VALUE!. Manually, I can press CTRL+ALT+F9, and the sheet recalculates, leaving no #VALUE! cells. Note, when I say "calculated cells", these cells rely on functions that are part of a VBA macro/the add-in.

Main question: How can I force a recalculation of the sheet from python?

Code:

import win32com.client
import win32api

def open_util():
    excel = win32com.client.Dispatch("Excel.Application")
    wb1 = excel.Workbooks.Open(r'C:\...the add-in.xla') 
    wb = excel.Workbooks.Open(r'C:\...the file name.xlsm')
    ws = wb.Worksheets('the sheet name')

    # get the values from two cells.
    myInput = ws.Cells(1,1).Value # this is an input cell, not calculated
    myOutput = ws.Cells(1,2).Value # this cell is calculated
    print("The original value of myOutput is : ", myOutput)


    # put a new value into the input cell. 
    newInput = 42
    ws.Cells(1,1).Value = newInput

    # Now I want the sheet to recalculate and give me a new output.
    # (when manually operating this spreadsheet, I just input a new value,
    # and the whole sheet automatically recalculates)
    # Note: these two lines of code appear not to have any effect.
    ws.EnableCalculation = True
    ws.Calculate()

    # get the new output
    newOutput = ws.Cells(1,2).Value 

    wb.Close(True)

    print("newOutput is : ", newOutput)
    return True # I haven't finished writing the function... no return value yet.

Output:

The original value of myOutput is : 10 # this is fine.
newOutput is :  -2146826273 # when I open the spreadsheet, this cell now says #VALUE!

About the add-in: I'm using this chemistry-related software which is basically a complicated spreadsheet and an execel add-in. The output cells in my code that say "#VALUE!": they use named formulas, which are in a macro that I can't look at (I suppose to not give away the code of the chemistry-related software).

Similar question: I found this similar question. In my case, I'm not sure the add-in is involved in calculating the cells. Anyway, I tried adding the code suggested in the answer:

def open_util():
    excel = win32com.client.Dispatch("Excel.Application")
    excel.AddIns.Add("rC:\...\add-in.xla").Installed = True # <-- This is the line I added.
    ...

However, this only generated an error message:

Traceback (most recent call last):
  File "C:\...\my_program_name.py", line 246, in <module> 
open_util()
  File "C:\...\my_program_name.py", line 216, in open_util
excel.AddIns.Add("C:\...\add-in.xla").Installed = True
  File "C:\Users\00168070\AppData\Local\Programs\Python\Python36-32\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x9\AddIns.py", line 35, in Add
, CopyFile)
pywintypes.com_error: (-2147352567, '例外が発生しました。', (0, 'Microsoft Excel', 'Add method of AddIns class failed', 'xlmain11.chm', 0, -2146827284), None)

(Note: the bit of Japanese means, I think, 'An exception occurred'.)

Side question: Is there documentation about what python/win32com functions are available for excel, and how to write a program with them? (for example, I never would have known that I needed the line of code "ws.EnableCalculation = True" until I saw it in a fragemet of someone else' code.) I've found only bits of tutorials.

Answer 1

A little late, but I ran into a similar issue. I am also using a third-party add-in. I believe this was causing the issue.

1. Test case to see if

   ws.EnableCalculation = True

   ws.Calculate()

is working

import time
import win32com.client

xlApp = win32com.client.Dispatch("Excel.Application")
xlApp.Visible = True  # so we can see what is happening
wb = xlApp.Workbooks.Open(excel_wb)  # make excel wb a saved file where A1 = A2 + A3
ws = wb.ActiveSheet


for x in range(9):
     ws.EnableCalculation = False
     ws.Range('A2').Value = x
     ws.Range('A3').Value = 2 * x

     print(ws.Range('A1').Value)  # value in A1 should remain the same
     time.sleep(10)
     ws.EnableCalculation = True
     ws.Calculate()
     # value in A1 should update
     print(ws.Range('A1').Value)  # value in A1 should be updated

This works just fine for me. Our expected output is 0, 3, 3, 6, 6, 9, etc. as it remains the same before updating.

2. If this code works for you, the issue is likely the plugin. For my plugin, the issue was a "phantom" excel instance that would hang out after closing the excel workbook and interfere with the plug-in.

To fix this, 2a. use a finally statement that closes excel at the end of the program and 2b. explicitly add a command line excel kill at the beginning of the main function

2a.

def quit_xl(xlApp, xlWB=None, save=True):
    if xlWB is not None:
        xlWB.Close(save)
    xlApp.Quit()
    del xlApp

2b.

import subprocess  # be careful - make sure that an outside user can't pass anything in

def kill_xl():
    try:
        kill_excel = """"“taskkill / f / im 
        excel.exe”"""  # command prompt to kill all excel instances
        subprocess.run(kill_excel, shell=True)
        return 0
    except Exception as e:
        return 1

put together:

def main():
    kill_xl()
    xlApp = win32com.client.Dispatch("Excel.Application")
    try:
        pass # main part of function
    except Exception as e:
        pass # if something goes wrong
    finally:
        quit_xl(xlApp)