Feb-23-2024, 08:53 AM
Hello,
I'm working on automatizing one report using Python.
In general I have around 20 excel files (.xlsx and .xlms) that I filter base on some criteria and then copy&paste to final report. At the end I dropdown/autofill formulas in the final report (from first row till end) - I assume it will save some time in preparing report.
Is below code best way of dropdown/autofill formula)? I need of course keep reference updates (meaning AO11, AO12, AO13 ect).
I'm working on automatizing one report using Python.
In general I have around 20 excel files (.xlsx and .xlms) that I filter base on some criteria and then copy&paste to final report. At the end I dropdown/autofill formulas in the final report (from first row till end) - I assume it will save some time in preparing report.
Is below code best way of dropdown/autofill formula)? I need of course keep reference updates (meaning AO11, AO12, AO13 ect).
# Loop over each row in the range for row in range(start_row, end_row + 1): # Adjust the formula for each row adjusted_formula = formula_AP10.replace("10", str(row)) # Update the row reference adjusted_formula = adjusted_formula.replace("AO10", f"AO{row}") # Update the AO10 reference adjusted_formula = adjusted_formula.replace("AN10", f"AN{row}") # Update the AN10 reference # Set the adjusted formula to the current cell in column AP MPV1[f"AP{row}"].value = adjusted_formula