i'm creating an excel spreadsheet with data from my aws account. That's working fine, but i'm trying to insert a vlookup via the script. the formula gets written to the cell but excel doesnt evaluate it. Excel is set to calculate formulas automatically. When I open the file if I activate the formula bar and click the green tick the formula runs and I get the expected result. I'm wondering if i'm missing anything in my python code. Also using openpyxl to create the workbook. Any help would be really appreciated!
for row in range(2, wrkbook.active.max_row + 1):
wrkbook.active[f'B{row}'].value = f'=VLOOKUP(A{row},Table1,2,FALSE)'
When I open the xlsx file, #NAME error is displayed.
Contents of formula bar: =VLOOKUP(A2,Table1,2,FALSE)
When I open the file if I activate the formula bar and click the green tick the formula runs and I get the expected result.
Openpyxl edits the xml files that make up an Excel xlsx file. It cannot calculate formulas or influence how or what Excel then does with the formulas when it opens the file. Therefore if the result when opening the file is the formulas show #NAME? there is nothing Openpyxl can do about that.
You have the option to refresh the cells when the workbook/worksheet is open.
Again Openpyxl cannot do anything here since it has no interaction with Excel. It cannot tell Excel to refresh the range on open.
However you can open the workbook using a module that utilises Excel and refresh the cells and that will fix your issue. That is something a module like Xlwings could do for you. It should be noted at the same time that Xlwings can also add the data, create the table and enter the vlookup formulas. If you completed these steps in Xlwings (or at least used it to enter the formulas), when you open the workbook in Excel the formulas should be calculated and so refreshing is not required, i.e. if you were to use Xlwings to refresh you may as well use it to write the all data and avoid the issue in the first place.