How to wait until Excel calculates formulas before

Question

I have a win32com Python script that combines multiple Excel files into a spreadsheet and saves it as a PDF.

How it works now is that the output is almost all #NAME? because the file is output before the Excel file's contents are calculated (which may take up to a minute).

How I force the workbook to calculate the values and wait until its done before continuing?

excel = win32.Dispatch('Excel.Application')

# moving stuff to this spreadsheet
wb1 = excel.Workbooks.Open(filepath1)
ws1 = excel.ActiveSheet

# from this spreadsheet
wb2 = excel.Workbooks.Open(filepath2)
ws2 = excel.ActiveSheet

# supposedly this should do it, but I haven't seen results
ws1.EnableCalculation = True
ws2.EnableCalculation = True

ws1.Calculate
ws2.Calculate

wb1.Cells(2, 4).Value = wb2.Cells(1,1).Value # doing stuff with values

# right here I need it to wait and calculate everything
# so when I export it, I see the values, not the formula or "#NAME?"

wb1.Save()
ws1.ExportAsFixedFormat(0, r'C:\filename.pdf')

wb1.Close(True)
wb2.Close(True)
excel.Application(Quit)

One kind of silly thing I did which actually worked for the cells, but not for the graphs was go through the entire sheet setting the values of the worksheet to themselves, so that the formula is overwritten by the numerical value.

However, the graphs still weren't updated to their values.

range = ws1.UsedRange
num_rows = range.Row + range.Rows.Count - 1
num_cols = range.Column + range.Columns.Count - 1

for i in range(1, num_rows ):
    for j in range(1, num_cols ):
        ws1.Cells(i, j).Value = ws1.Cells(i, j).Value 

Answer 1

You have to be careful to call functions using the () syntax:

ws1.Calculate()
ws2.Calculate()

This will calculate all cells of both worksheets. It appears that each function returns only after it has finished all computations, which is the effect you want, so unless I'm misunderstanding your question that fix should be sufficient.

Answer 2

From my comment above:

The problem ended up being something else, which I answered here: https://stackoverflow.com/a/25495515/2374028

If you use any sort of scripting language, including python's win32com, it doesn't automatically include add-ins, and my calculations used add-ins, so it was just skipping over them.