"show formulas" in excel using python & win32com

Question

How can display/show all of the formulas in an Excel workbook using Python3 and win32com.client, like I can do interactively with Cntrl-`

I believe I have to use the Windows DisplayFormulas Property but I don't know how to access the ActiveWindow to do this in Python.

Here is the code I have up to open a spreadsheet and the first workbook and save it:

import win32com.client

excel = win32com.client.Dispatch("Excel.Application")
wb = excel.Workbooks.Open(wb_path)
ws_index_list = [1] 
wb.WorkSheets(ws_index_list).Select()

# would like to toggle to show the formulas here before saving

wb.ActiveSheet.ExportAsFixedFormat(0, pdf_path)
wb.Close(False)

From the keyboard I'd simply enter Cntrl-`, I'd like to automate this as I'm processing about 30 excel spreadsheets.

Answer 1

You will first need to access the Window object of the Excel application. You can then call the DisplayFormulas method:

import win32com.client

excel = win32com.client.Dispatch("Excel.Application")
wb = excel.Workbooks.Open(wb_path)
ws_index_list = [1] 
wb.WorkSheets(ws_index_list).Select()

#toggle DisplayFormulas
for w in excel.Windows:
   w.DisplayFormulas = True

wb.ActiveSheet.ExportAsFixedFormat(0, pdf_path)
wb.Close(False)