How to test hyperlinks validity?

[Eawyne]

Hi all

I have a workbook that's used as a dashboard : there are macros, buttons, icons, organized in a fancy way to appeal to the average user. It happens to have a lot of links for convenience to various files on the servers (C:, W:, etc.).

It also happens that most of those files get edited by random people, and so I wanted to try and tinker something that could test out the validity of hyperlinks from the dashboard to have a quick glance at any broken link that may need assistance.

I've come up with a few codes :

Sub TestHLinkValidity()

 
Dim rRng As Range
 Dim fsoFSO As Object
 Dim strFullPath As String
 Dim cCell As Range

 
Range("A1:B30").Select

 
Set fsoFSO = CreateObject("Scripting.FileSystemObject")
 Set rRng = Selection

 
For Each cCell In rRng.Cells
 If cCell.Hyperlinks.Count > 0 Then
 
 strFullPath = ActiveWorkbook.Path & "\" & cCell.Hyperlinks(1).address
 If fsoFSO.FileExists(strFullPath) = False Then
 cCell.Interior.ColorIndex = 3
 Else
 cCell.Interior.ColorIndex = 0
 
 End If
 End If
 
 Next cCell

 
End Sub

and

Function HyperTest(c As Range)

 
If Dir(c) <> "" Then
 HyperTest = "File exists."

 
Else
 HyperTest = "File doesn't exist."
 
 End If

 
End Function

But the problem with those is that they require a well-formated list to be of any use. I don't have that luxury - unless I create a table that collects all links - which would require the manual intervention of any user that modifies a file ! Realistically, it wouldn't even happen in an utopia...

Is there a solution to my problem ? Does something exist that sorta scans the whole workbook for links and tests them out ? Isn't it too outlandish ?

 

[CeeB1]

All members of all existing hyperlinks of the workbook in which this code is running are collected and displayed on a separate sheet.

Thank you so very much, I am certainly going to try using it. I managed to use the Link function in Kutools to copy and past the content of the cell and then have at least the ability to do a visual check.

Maybe you can help with another problem with the Hyperlinks - I need to change a value within the formulae to reflect another worksheet. These hyperlinks are jumping to different worksheets within the SAME workbook and I need to be able to change the worksheet name to which it need to jump to (for multiple hyperlinks). The find and replace function for that specific test within the Hyperlink formulae does not work when it refers to another worksheet within the workbook - I have seen solutions to change an external web address which is not what I need.

Much appreciated

 

[GWteB]

Glad to help and thanks for letting me know

I need to change a value within the formulae to reflect another worksheet.

As this is a completely different question I think it's fair to ask you to start your own thread for this.

 

[CeeB1]

Glad to help and thanks for letting me know



As this is a completely different question I think it's fair to ask you to start your own thread for this.

Will do thanks