xlwings: pass a variable into formula bar

user_6790817 0
Viewed 48

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

xlwings python
1 Answers

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()
user_136641370
Related