0

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.

Guillaume Jacquenot
  • 11,217
  • 6
  • 43
  • 49
Levon
  • 138,105
  • 33
  • 200
  • 191

1 Answers1

0

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)
drec4s
  • 7,946
  • 8
  • 33
  • 54