Eawyne
Board Regular
- Joined
- Jun 28, 2021
- Messages
- 53
- Office Version
- 2021
- Platform
- Windows
Hi all ![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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 :
and
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 ?
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 :
VBA Code:
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
VBA Code:
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 ?