Link to home
Start Free TrialLog in
Avatar of mamelas
mamelasFlag for Greece

asked on

Edit hyperlinks massively in an Excel 2007 workbook

Hello Experts,

I would like to change the extension (not the name) of the hyperlinked files from .xls to xlsx.

So every hyperlinked excel file should change link from example.xls to example.xlsx.

Does anybody know any massive way to edit hyperlinks on an Excel 2007 Sheet using VBA , Macro or Application?
Avatar of pagosche
pagosche
Flag of United States of America image

If you hit ctrl+F it will bring up a find window.  Click on the replace tab.  In the find what, choose .xls and in the replace with box put in .xlsx.  Click the find all button and then the replace all button.
ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
mamelas,

Not for points...

What kind of hyperlinks are they? Chris's solution looks great for "pure" hyperlinks, but if yours are formulas using the HyperLink function, then his macro won't see them and, for a single sheet, pagosche solution is fine.

Regards,
Brian.
Avatar of mamelas

ASKER


Some cell are linked like this  =network path\filename.xls!cell A1
The above type takes automatically data from cell A1 of filename.xls

Using "Find & Replace" for this case, the Excel successfully  founds the
.xls files but it asks for every file to open the new one.

If it could read the above links as a text and just add the "x" letter at the
end of every .xls file it would be by far the easiest method for the above type of link.

While others are linked with hyperlinks like this: foldername\subfolder\filename.xls
The above type you have to click on the link to open  the filename.xls in a new Excel window.

Regarding these type of links, are they suitable for the posted macro?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mamelas

ASKER

@ chris

I will test them and I will revert!
Avatar of mamelas

ASKER

It worked. Thanks!