mamelas
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?
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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\filen
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@ chris
I will test them and I will revert!
I will test them and I will revert!
ASKER
It worked. Thanks!