I want to loop over multiple files, creating new formulas for each file as I go. To do that I want to concatenate a string with a variable, then use excel wings create a new formula. To that end I've written this code:
file_name=r'[Element_IA_Gross.xlsx]'
equation=r'=SUM(\''+file_name+'Total Immediates\'!$R6:$T6)'
ws.range("O19").value= [equation]
If I print the variable equation
I see what I want in the excel formula bar. However using the above code doesn't work? Any help much appreciated.
Thanks
The formula looks for the workbook in the default location so opens a file search window and waits for the input.
The following works based on why it worked for me by opening both workbooks, adding the formula to 'book1', save 'book1' and then close both books.
import xlwings as xw
file_name = 'Element_IA_Gross.xlsx'
workbook = 'Book1.xlsx'
with xw.App() as app:
wb1 = xw.Book(workbook)
wb2 = xw.Book(file_name)
ws = wb1.sheets('Sheet1')
equation = "=SUM('[" + file_name + "]Total Immediates'!$R6:$T6)"
ws["O19"].value = equation
wb1.save(workbook)
wb1.close()
wb2.close()
-------------Option 2 ----------
This option also worked including the whole path and filename in the formula.
import os
import xlwings as xw
path = r'F:\Projects\xlwings_test'
file_name = 'Element_IA_Gross.xlsx'
link = os.join(path, file_name)
workbook = 'Book1.xlsx'
with xw.App() as app:
wb1 = xw.Book(workbook)
ws = wb1.sheets('Sheet1')
equation = "SUM('[" + link + "]Total Immediates'!$R6:$T6)"
ws["O19"].value = equation
wb1.save(workbook)
wb1.close()