How to apply an excel formula into one worksheet in a specific range of cells over multiple workbook-CodePudding

I've got a folder with .xls files and I want to apply a specific formula into each one of them.

I get an IndexError:

Replacement index 1200 out of range for positional args tuple

This is my code.

import openpyxl as op
import os, glob
from openpyxl import load_workbook
from openpyxl.utils import FORMULAE
files = *somefiles*

wb = load_workbook(filename = file ) 
sheet = wb.worksheets[0]
sheet.insert_cols(62) # add a column next to column 61 to input the formula


for f in files :
# I would like to iterate over the rows in column N until the last non-blank cell 
  for row in sheet.iter_rows('BK{0}:BK{*1200*}') 
    for cell in row :
      cell.value = f"= VLOOKUP(A2,'{f}"Sheet1"!A:C,3,0)

CodePudding user response：

There are few changes that I have done, but hopefully this is what is required. This will open each file in the files list, go to the first sheet, add column at 61. FYI, I have kept it as 61st column, but that is not column BK or N - so please see where you want to add the column and adjust accordingly. Also, be aware that the VLOOKUP formula is always checking against A2, that cell is not changing.

files = ["File1.xlsx", "File2.xlsx"] #Files should be in this format

for f in files :
    wb = load_workbook(filename = f) 
    sheet = wb.worksheets[0]
    sheet.insert_cols(61) # add a column next to column 61 to input the formula

    # I would like to iterate over the rows in column 61 until last non-blank cell 
    for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=61, max_col=61):
        for cell in row :
            print(f'= VLOOKUP(A2,[{f}]Sheet1!A:C,3,0)')
            cell.value = f'= VLOOKUP(A2,[{f}]Sheet1!A:C,3,0)'
    wb.save(f)