Excel - mismatch between Excel and ReplaceMagic

Previous Top Next

As most likely in your Excel documents you have Link Source, there might be  questions like:

  1. Why is that ReplaceMagic reported link sources do not look like that what we see Excel in Link Sources (menu: Data -> Edit Links)?
    For example:

    ReplaceMagic reports:

    But when you go to Excel you see:

    Or over menu option Data  -> Edit links:

    Reason is simple - to be user friendly Excel will show absolute links with full path like what you can see in images above with c:\... but that is misleading as real Link Sources is only that what ReplaceMagic reports.
    Due to this user-friendliness you might try to fix what you see in Excel and that will not work as ReplaceMagic cannot find that what you entered as a search string.

    Basically, Excel is keeping Link Sources in externalConnections section (if you have 4-letter extension Excel file you can append .zip at the end of file name and then go inside down to \xl\externalLinks\_rels)

    where link sources are. If you open .rels files you can find exactly that what ReplaceMagic is reporting:

    Now, what Excel does, it takes Link Source value (screenshot above) and combines it with field called AbsPath or even just checks what is the location of file now and use that to convert relative link to absolute link (btw. AbsPath is in \xl\worksbook.xml):

    Important remark is that replacements have to be based on what ReplaceMagic reports and not on what you see in Excel as this is just construct that is only for display reasons. Of course, if you fix link source based on ReplaceMagic, your formulas will be updated accordingly and, if replacements is properly done, link sources will be again valid. Ignore what you see in Excel and rely only on ReplaceMagic findings as otherwise you will not be able to fix link sources.