Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

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?
0
mamelas
Asked:
mamelas
2 Solutions
 
pagoscheCommented:
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.
0
 
Chris BottomleyCommented:
Assuming every link in each sheet of the file where the code is placed then ...

Chris
Sub HyperMod()
Dim str As String
Dim hyp As Hyperlink
Dim sh As Worksheet

    For Each sh In ThisWorkbook.Worksheets
       For Each hyp In sh.Hyperlinks
        If Right(hyp.Address, 3) = "xls" Then
            hyp.Address = hyp.Address & "x"
                If Right(hyp.TextToDisplay, 3) = "xls" Then
                    hyp.TextToDisplay = hyp.TextToDisplay & "x"
                End If
        End If
       Next
    Next
End Sub

Open in new window

0
 
redmondbCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mamelasAuthor Commented:

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?
0
 
Chris BottomleyCommented:
To allow for the cell addresses then try as expanded below.

Chris
Sub HyperMod()
Dim str As String
Dim hyp As Hyperlink
Dim sh As Worksheet

    For Each sh In ThisWorkbook.Worksheets
       For Each hyp In sh.Hyperlinks
        If Right(hyp.Address, 3) = "xls" Then
            hyp.Address = hyp.Address & "x"
                If Right(hyp.TextToDisplay, 3) = "xls" Then
                    hyp.TextToDisplay = hyp.TextToDisplay & "x"
                End If
        End If
        If instr(hyp.Address, ".xls!") > 0 Then
            hyp.Address = replace(hyp.Address, ".xls!", ".xlsx!") 
            If instr(hyp.Address, ".xls!") > 0 Then
                hyp.TextToDisplay = replace(hyp.TextToDisplay, ".xls!", ".xlsx!") 
            End If
        End If
       Next
    Next
End Sub

Open in new window

0
 
mamelasAuthor Commented:
@ chris

I will test them and I will revert!
0
 
mamelasAuthor Commented:
It worked. Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now