Searching for Excel add-in module references

Question

I've only had FileLocator Pro for a few weeks. It has already saved me a lot of time searching for function calls inside Excel sheets. Great product.

But I just ran into a situation that doesn't seem to be working.

I made a change to the name of an Excel add-in module. That caused all calls to any code in that module to fail. Excel changed all calls like

=xyz()

to

='C:\Users\PogoP\AppData\Roaming\Microsoft\AddIns\Old Add-In Name.xlam'!xyz()

I typed ".xls" in the Filename: field and this in the Containing text: field:

'C:\Users\PogoP\AppData\Roaming\Microsoft\AddIns\Old Add-In Name.xlam'!

I put the name of a folder containing a few workbooks that I know contain that text. FLP reported 0 hits.

Am I doing something wrong?

Thanks

Answer 1

When searching the source for Excel files you need to first find how the text is represented within the file. First try searching for something like 'PogoP' or 'xlam' in a file you know the reference exists in, e.g.

File name:
Containing Text: xlam
Look In:         C:\MyFiles\KnownReference.xlsx

Then look in the Hits or Text tab to see how the reference has been encoded in the source.

Comments

Thanks, that was helpful.

This is the formula that displays in the Excel Formula Bar:

='C:\Users\Pogo P\AppData\Roaming\Microsoft\AddIns\Pogo Add-Ins.xlam'!getformula(K18)

When I put that string in the Containing text: field, there were no hits. Taking your suggestion, I then searched for just "Roaming". FLP found the files. When I clicked on the Hits tab, I see this text string:

<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath" Target="/Users/Pogo%20P/AppData/Roaming/Microsoft/AddIns/Pogo%20Add-Ins.xlam" TargetMode="External"/></Relationships>

It looks like Excel changed the back slashes to forward slashes and replaced the spaces with @20, which is probably the ASCII code.

The function name (getformula) is not included. I did another search on that string. FLP found the same hits.

It looks like Excel encodes these calls, but simplifying the search and checking the actual text makes it work.