Can PowerGREP change expressions in Excel cells?

[JenniferMurphy]

For some time I have been using FileLocator Pro to search for text strings in all kinds of files including Excel workbooks.

Filelocator Pro – Mythicsoft

Its search facility is excellent, but it does not have a replace feature. When I asked the developer, he said they have no plans for a replace feature and suggested I check PowerGREP.

PowerGREP: Windows grep Software to Search (and Replace) through Files and Folders on Your PC and Network

I just did and it claims to be able to execute both find and replace in all kinds of files including Excel workbooks. Has anyone used this to change code inside Excel cells, such as the name of a function called in Excel files?

Thanks

 

[rlv01]

You should just email Jan Goyvaerts at Just Great Software. He's pretty responsive for questions like this.

Also, there's this page:

Search Through Excel Spreadsheets to Find Text, Keyword, Phrase or Regex Match

www.powergrep.com

 

[JenniferMurphy]

You should just email Jan Goyvaerts at Just Great Software. He's pretty responsive for questions like this.

Also, there's this page:

Search Through Excel Spreadsheets to Find Text, Keyword, Phrase or Regex Match

www.powergrep.com

I did just that a day ago. So far, no word. But it's a holiday weekend here. Not sure what it is in Thailand, which is where the company says it is located.

I installed the trial version and tried to have it find all workbooks containing calls to my fmttime UDF. It found 10. FileLocator Pro using the same search found 52. I probably sdet it up wrong, but the PowerGREP options are kinda complicated and the help text is difficult for me to follow.

 

[Sequoyah]

Hello everybody
as far as i understand PowerGREP provides an option to search through the raw XML content of XLSX files. With a little patience you can do it manually without using special software, here are the necessary steps (try on a copy of your file!)
- change file extension from xlsm to zip so you can open it with a standard zip program (i.e. WinZip)
- extract the xml file corresponding to your sheet (i.e. sheet1.xml) from the folder xl --> worksheets in the archive
- open this file with notepad or similar and run find and replace to change the function name, then save it
- replace the xml file in the archive with the updated file then change the archive extension from zip to xlsm
- open the file with excel and you should find the formulas with the updated name. If the name is replaced but the result remains unchanged, use the following code to force the recalculation

Public Sub ForceFullCalculation()
'https://www.coragi.com/tips-and-tricks/excel/excel-vba-force-full-calculation-of-formulas/
    On Error Resume Next
    ' Normal calculation
    Call Application.Calculate
    
    ' Extended calculation
    Call Application.CalculateFull
    ' Full Calculation
    Call Application.CalculateFullRebuild
End Sub