Python returns Excel formula rather than cell value or the cell value isn't changing

Question

import openpyxl

# Path to the Excel file
file_path = r'D:\1 - DOWNLOADS\TEST1.xlsx'

# Load the workbook
workbook = openpyxl.load_workbook(file_path)

# Select the active sheet (assuming you want to work with the first sheet)
sheet = workbook.active

# Iterate over numbers 0-9
for i in range(10):
    # Enter the number in cell B1
    sheet['B1'].value = i

    # Get the value from cell A1
    a1_value = sheet['A1'].value

    # Print the combination
    print(f'Combination: A1={a1_value}, B1={i}')

This code should simply enter numbers 0-9 in cell B1 and return the value from cell A1 from Excel. However, the only output from cell A1 that's showing is either the formula in cell A1 or the value in cell A1 never changes with each changed number in cell B1.

If you create an Excel spreadsheet and type a formula in cell A1 such as =B1*2 (as an example), then the calculation of cell A1 should change and be printed along with the number entered into cell B1. But this isn't the case.

Take note: I have Excel 2007 and Python 3. Please help, thank you! :-)

Answer 1

You can replace excel formulae with the values they output by copying your whole sheet and pasting back over in the same position with "Paste Values" Paste values screenshot