Openpyxl: Code fails to write data from one Excel spreadsheet to another

I am trying to use Openpyxl to search for data in one Excel workbook, and write it to another (pre-existing) Excel workbook. The aim is something like this:

1. Search in Workbook1 for rows containing the word "Sales" (there being several such rows)
2. Copy the data from Column E of those rows (a numerical value), into a specific cell in Workbook2 (second worksheet, cell C3).

My code below appears to run without any errors, however, when I open Workbook 2, no data is being written in to it. Does anyone know why/can suggest a fix?

# importing openpyxl module
import openpyxl as xl
import sys

# opening the source excel file
filename ="C:\\Users\\hadam\\Documents\\Testing\\TB1.xlsx"
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]

# opening the destination excel file
filename1 = "C:\\Users\\hadam\\Documents\\Testing\\comp2.xlsx"
wb2 = xl.load_workbook(filename1)
ws2 = wb2.worksheets[1]

for sheet in wb1.worksheets:
    for row in sheet.iter_rows():
        for cell in row:
            try:
                if 'Sale' in cell.value:
                    # reading cell value from source excel file
                    c = ws1.cell(row=cell.row, column=4).value

                    # writing the read value to destination excel file
                    ws2.cell(row=2, column=2).value = c.value
            
            except (AttributeError, TypeError):
                continue

            # saving the destination excel file
            wb2.save(str(filename1))
                
sys.exit()

Other info:

• Specifically, the text string I am searching for ('Sales') is in Column A of Workbook1. It is not an exact match, but e.g. a given cell contains "5301 Sales - Domestic - type4". Therefore I want to sum the numerical values in Column E which contain "Sales" in Column A, into a single cell in Workbook2.
• I am mega new to Python/coding. However, my environment seems to be set up okay, e.g. I have already tested a code (copied from elsewhere in the Web) that can write all the data from one spreadsheet into another pre-existing spreadsheet). I am using Mu editor in Python 3 mode and openpyxl module.

Solution 1:

The line

c = ws1.cell(row=cell.row, column=4).value

copies the value in Col 4 to the variable 'c'. 'column=4' is Column D in the spreadsheet, you want this to be 'column=5' to get the value from Column E. If Column D is empty c would have no value each time.

In your code c is equal to the value in that Column E which matches your search criteria, currently it would be overwritten on each iteration of the loop. To sum the values you must add each new value to the existing value of c for each iteration using '+='.

There is no need to update the 2nd book cell value for each loop, you only want the resulting sum writen so these steps can be completed once the sum has been calculated at the completion of the loop.

See code below that modifies that part of your code;

initialize c

c = 0

change the line so c sums the value in Col E for each loop match

c += ws1.cell(row=cell.row, column=5).value

then place the cell value update and save to excel book at the same indentation as the loop so this is executed once the loop completes.

Note that c is a python integer it has no attribute called value it's just 'c'. Using 'c.value' would give a blank value. Also the cell C3 you want to write this value to is row/col 3

ws2.cell(row=3, column=3).value = c

Finally save the cell to the 2nd excel book

Modded code;

...
c = 0
for sheet in wb1.worksheets:
    for row in sheet.iter_rows():
        for cell in row:
            try:
                if 'Sale' in cell.value:
                    # reading cell value from source excel file
                    c += ws1.cell(row=cell.row, column=5).value

            except (AttributeError, TypeError):
                continue

# writing the read value to destination excel file
ws2.cell(row=3, column=3).value = c
# saving the destination excel file
wb2.save(str(filename1))
...