Find and replace file name in Excel file using Python

Question

based on Find and Replace text in xlsx file with python openpyxl in whichever cell it appear in within sheet I tried to do the following:

The file "example.xlsx" contains cells where I want to replace "'path[file.xlsx]tab1'!A5" by "'path[file.xlsm]tab1'!A5". I tried:

#! python3
import openpyxl

wb = openpyxl.load_workbook("example.xlsx")
ws = wb["Sheet1"]

i = 0
for r in range(1,ws.max_row+1):
for c in range(1,ws.max_column+1):
    s = str(ws.cell(r,c).value)
    if s != None and "xlsx" in s: 
        ws.cell(r,c).value = s.replace("xlsx","xlsm") 

        print("row {} col {} : {}".format(r,c,s))
        i += 1

wb.save('targetfile.xlsx')
print("{} cells updated".format(i))

But it did not replace anything. What shall I do?

Side note: after ``s = str( ... )`` ``s`` will be a string, so ``s != None`` will always be ``True``, so you might as well remove this check. — Mike Scotty, Feb 08 '21 at 13:51
`for r in range(1,ws.max_row+1): for c in range(1,ws.max_column+1):` is looping through every cell in the sheet - you may be able to define a more efficient loop than this — JohnnieL, Feb 08 '21 at 14:07

JohnnieL · Answer 1 · 2021-02-08T14:38:37.913

0

I am using windows and it only works to overwrite the originally opened workbook when i specify a file path on the save, so simply modifying penultimate line to this works if you want to overwrite the opened file

wb.save(".\\example.xlsx")

I can save a new file (as you are doing, opening example.xlsx and saving targetfile.xlsx) without specifying a path but for some reason to overwrite an existing file you need to have a path

edited Feb 08 '21 at 14:38

answered Feb 08 '21 at 13:56

JohnnieL

1,192
1
9
15

I changed the code to: "#! python3 import openpyxl wb = openpyxl.load_workbook("example.xlsx") ws = wb["Sheet1"] i = 0 for r in range(1,ws.max_row+1): for c in range(1,ws.max_column+1): s = str(ws.cell(r,c).value) if s != None and "xlsx" in s: s=s.replace("xlsx","xlsm") ws.cell(r,c).value = s print("row {} col {} : {}".format(r,c,s)) i += 1 wb.save(".\\example.xlsx") print("{} cells updated".format(i))" which gives: "0 cells updated." – py-excel Feb 09 '21 at 06:51

score 0 · Answer 2 · answered Feb 08 '21 at 14:16

I did the following:

#! python3
import openpyxl

wb = openpyxl.load_workbook("example.xlsx")
ws = wb["Sheet1"]

i = 0
for r in range(1,ws.max_row+1):
    for c in range(1,ws.max_column+1):
        s = str(ws.cell(r,c).value)
        if s != None and "xlsx" in s:
        s=s.replace("xlsx","xlsm") 
        ws.cell(r,c).value = s
        print("row {} col {} : {}".format(r,c,s))
        i += 1

wb.save('targetfile.xlsx')
print("{} cells updated".format(i))

But it did not replace anything.

its something to do with file permissions on save, if you save to a different file name it works — JohnnieL, Feb 08 '21 at 14:29

score 0 · Answer 3 · answered Feb 08 '21 at 14:35

Are you sure you're looking in the correct file? You have two files example.xlsx and targetfile.xlsx. The changes will be seen in targetfile.xlsx not in example.xlsx.

Working Example

import openpyxl

wb = openpyxl.load_workbook("mydoc.xlsx")
ws = wb['Sheet1']
i = 0
for r in range(1,ws.max_row+1):
    for c in range(1,ws.max_column+1):
        s = str(ws.cell(r,c).value)
        if "xlsx" in s: 
            ws.cell(r,c).value = s.replace("xlsx","xlsm") 

            print("row {} col {} : {}".format(r,c,s))
            i += 1

wb.save('mycode.xlsx')
print("{} cells updated".format(i))

Yes, I had a look in targetfile.xlsx and nothing was replaced. Python shell output is: "0 cells updated" — py-excel, Feb 08 '21 at 15:23

Find and replace file name in Excel file using Python

3 Answers3