I am using openpyxl to generate multiple similar workbooks from a template. One of the sheets in these newly generated workbooks should have cells that refer to another, locally stored, workbook. I need to code the value of this formula based the name of the specific workbook.
The required cell formula should read eg "=[coe.xlsx]COE!A1" but what I find in the cells when I open the newly generated workbook is =coe.xlsx!A1. The brackets and sheet title are dropped. This happens when I open the workbook with and without Updating the links on startup.
Sample code that re-create this issue for me is as follows:
from os import path
from openpyxl import load_workbook
root = "path/to/file"
workbook = load_workbook(path.join(root, "test.xlsx"))
sheet = workbook["COE"]
sheet["A1"].value = "=[coe.xlsx]COE!A1"
workbook.save(path.join(root, test.xlsx"))
Writing normal (non-external referencing formulas), seem to work OK. I cannot seem to find anything specific in the docs about referencing external workbooks from formulas nor any useful references to similar questions (if this has been covered on stackoverflow, please link me to the relevant post).
