I have to select a fixed ranged ("H4":"AI4") from a list of sheets in a workbook and I have to paste that range in another workbook that has identical sheet names as the first wb. Now here is where it gets tricky (for me - I'm new to this): I have to paste the range in the same place ("Hx":"AIx"), where x is the row number that meets the following criteria: the cell in column "F" of that row has to be yesterday's date AND the cell in column "U" of that row should not be "0" or blank. If the cell in column "U" is "0", I have to iterate through the rows (in reverse order, meaning it has to look in the row above the one in which "Ux" is "0") until I find one that is not 0 and paste the data there. I am using openpyxl for this. So far, I've tried many things, none of which worked and I am currently stuck at this stage, where I get the error - AttributeError: 'tuple' object has no attribute 'value':
def copy_daily_range(file1, file2):
"""copies range h4:ai4 from file1 to file2"""
# define sheets for both file1 and file2
sheet_list = ["a", "b", "c"]
y_day = dt.date.today() + dt.timedelta(days=-1)
yesterday = dt.datetime.strptime(str(y_day), '%Y-%m-%d').strftime('%Y-%m-%d') # this sets yesterday's date in the d-MMM-yy format
# open file1 and get cell range from sheet
file_1 = xl.load_workbook(file1)
# open file2
file_2 = xl.load_workbook(file2)
# copy range from file1 to file2
for sheet in sheet_list:
file1_sheet = file_1[sheet]
cell_range = file1_sheet["H4": "AI4"]
file2_sheet = file_2[sheet]
# find max row
mr = file2_sheet.max_row
for num in range(4, mr):
f_col = file2_sheet[f"F{num}"]
split_cell = str(f_col.value)
good_date, bd = split_cell.split(" ")
if good_date == yesterday:
file2_cell_range = file2_sheet[f"H{num}":f"AI{num}"]
file2_cell_range.value = cell_range.value # AttributeError: 'tuple' object has no attribute 'value'
file_2.save(file2)
Any suggestions?
