Python - Replace Values in Excel Sheets

Question

I have an excel file with 18 sheets, 75 columns and 50000 rows.

I am trying a code like this to replace values and it is saying syntax error.

from win32com.client import Dispatch #to work with excel files
.
.
.
.
fnd = "REPLACINGTHENAS"
rplc = ""

#18sheets
for i in range(1, (xlwb1.sheets.count +1)):
    #Syntax error on the line below
    xlwb1.Worksheets(i).Cells.Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

xlwb1.Save()

I am not sure why there is a syntax error. Kindly help me with this.

Alexander Rossmanith · Accepted Answer · 2019-04-04 10:31:03Z

1

Parentheses are missing around the .Replace() function call. Also, as Dylan correctly pointed out, you should use # to comment lines.

xlwb1.Worksheets(i).Cells.Replace(what=fnd, Replacement=rplc, LookAt=xlPart, SearchOrder=xlByRows, MatchCase=False, SearchFormat=False, ReplaceFormat=False)

answered Apr 4, 2019 at 10:31

Alexander Rossmanith

3342 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dylan Logan · Accepted Answer · 2019-04-04 10:25:56Z

1

Tip: Use # to comment lines!

I use an module called XLWT in conjunction with XLRD to manipulate spreadsheets, it is simple and flexible. You can try installing the module via pip: https://pypi.org/project/xlwt/

I may be able to help you out a bit more with that!

answered Apr 4, 2019 at 10:25

Dylan Logan

4158 silver badges18 bronze badges

2 Comments

Student of the Digital World Over a year ago

Thanks for the tip. No, I can't install an external library need a native method. But thanks.

Dylan Logan Over a year ago

Ah I understand, I am sure Alexander will be able to help!

Collectives™ on Stack Overflow

Python - Replace Values in Excel Sheets

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related