Is it possible to dynamically change the path of the formula references using Python in Excel?

user_12487769 0
Viewed 90

We are at the point of a file share migration, where we have quite a few excel files that contain formulas, referencing the old file share address.

Is it possible, to dynamically change all formulas containing a reference to the old file share to the new file share?

If possible could you provide me with some python code to achieve this?

Q:\general\2021\[excel_file.xlsm] # Old filepath

I:\general\2021\[excel_file.xlsm] # New filepath
excel xml python
1 Answers

Suppose you need just to replace some text in all cells on the first sheet of the file test.xlsx. It can be done this way:

import win32com.client

app = win32com.client.Dispatch("Excel.Application")
wb = app.Workbooks.Open(r"d:\temp\test.xlsx")

sheet = wb.Sheets(1)
sheet.Cells.Replace("Q:\\general\\2021\\", "I:\\general\\2021\\")

wb.Save()
wb.Close()

If you have many sheets and many xlsx files you can add loops to read all files and all their sheets:

from pathlib import Path
import win32com.client

folder = Path.cwd()                  # current folder
files = list(folder.rglob("*.xlsx")) # all xlsx files from the folder and subfolders

app = win32com.client.Dispatch("Excel.Application")

for f in files:                      # loop through all files
    wb = app.Workbooks.Open(f)

    sheets = wb.Sheets
    for i, sh in enumerate(sheets):  # loop through all sheets
        sh = wb.Sheets(i+1)
        sh.Cells.Replace("Q:\\general\\2021\\", "I:\\general\\2021\\")

    wb.Save()
    wb.Close()
user_142654690
Related