Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking hyperlinks are valid
I have a large spreadsheet with hyperlinks to files on my system.
I need a way to check that all the links work without going through and clicking them one at a time (for 3000+ hyperlinks this would be extremely impractical) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking hyperlinks are valid
thats's fantastic.
thank you so much (this has saved me about 2 hours a week) Kind regards, Richard "Rafael Guerreiro Osorio" wrote: Actually, you don't need to open a file to check if it exists... Run the macro below whenever your hyperlinks or their targets change: Sub ChkHypLnks() Dim wksHypLnks, wksBadHypLnks As Worksheet Dim curHypLnk As Hyperlink Dim curFile As String Dim iBadHypLnks As Integer Set wksHypLnks = ActiveSheet Set wksBadHypLnks = ThisWorkbook.Worksheets.Add wksBadHypLnks.Name = "BadHypLnks" _ & Right(wksBadHypLnks.Name, Len(wksBadHypLnks.Name) - 5) For Each curHypLnk In wksHypLnks.Hyperlinks If Dir(curHypLnk.Address) = "" Then iBadHypLnks = iBadHypLnks + 1 wksBadHypLnks.Cells(iBadHypLnks, 1) = curHypLnk.Address End If Next curHypLnk Application.DisplayAlerts = False If iBadHypLnks < 1 Then wksBadHypLnks.Delete Application.DisplayAlerts = True End Sub Just copy the code and paste it in the Thisworkbook module on the VBA editor, then run the macro ChkHypLnks from the sheet where the hyperlinks are. It will generate a list of bad targets in a new sheet. You will find out that it is blazing fast. Best, Rafael "Garbagh" wrote: I have had trouble getting the macro to recognise the opening of the hyperlink as this is a mouse function. I appreciate this may be difficult/impossible but i would really like a way of checking the links without fully opening the file attached .Excel carries out a limited version of this when warning of opening hyperlinks (the error message for asking whether you want to open the hyperlink is different to the one telling you that the address is invalid) Opening all of the 3000+ files at once would probably kill my PC. the files the hyperlink refers to are dynamic and change week to week (from a library of over 40,000) "Rafael Guerreiro Osorio" wrote: I think the only way to do this is to write a VBA macro to cycle through the hyperlinks and check whether the each reference file exist. Best, Rafael "Garbagh" wrote: I have a large spreadsheet with hyperlinks to files on my system. I need a way to check that all the links work without going through and clicking them one at a time (for 3000+ hyperlinks this would be extremely impractical) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
checking hyperlinks are valid
On Friday, December 1, 2006 at 4:53:01 AM UTC-6, Garbagh wrote:
thats's fantastic. thank you so much (this has saved me about 2 hours a week) Kind regards, Richard "Rafael Guerreiro Osorio" wrote: Actually, you don't need to open a file to check if it exists... Run the macro below whenever your hyperlinks or their targets change: Sub ChkHypLnks() Dim wksHypLnks, wksBadHypLnks As Worksheet Dim curHypLnk As Hyperlink Dim curFile As String Dim iBadHypLnks As Integer Set wksHypLnks = ActiveSheet Set wksBadHypLnks = ThisWorkbook.Worksheets.Add wksBadHypLnks.Name = "BadHypLnks" _ & Right(wksBadHypLnks.Name, Len(wksBadHypLnks.Name) - 5) For Each curHypLnk In wksHypLnks.Hyperlinks If Dir(curHypLnk.Address) = "" Then iBadHypLnks = iBadHypLnks + 1 wksBadHypLnks.Cells(iBadHypLnks, 1) = curHypLnk.Address End If Next curHypLnk Application.DisplayAlerts = False If iBadHypLnks < 1 Then wksBadHypLnks.Delete Application.DisplayAlerts = True End Sub Just copy the code and paste it in the Thisworkbook module on the VBA editor, then run the macro ChkHypLnks from the sheet where the hyperlinks are. It will generate a list of bad targets in a new sheet. You will find out that it is blazing fast. Best, Rafael "Garbagh" wrote: I have had trouble getting the macro to recognise the opening of the hyperlink as this is a mouse function. I appreciate this may be difficult/impossible but i would really like a way of checking the links without fully opening the file attached .Excel carries out a limited version of this when warning of opening hyperlinks (the error message for asking whether you want to open the hyperlink is different to the one telling you that the address is invalid) Opening all of the 3000+ files at once would probably kill my PC. the files the hyperlink refers to are dynamic and change week to week (from a library of over 40,000) "Rafael Guerreiro Osorio" wrote: I think the only way to do this is to write a VBA macro to cycle through the hyperlinks and check whether the each reference file exist. Best, Rafael "Garbagh" wrote: I have a large spreadsheet with hyperlinks to files on my system. I need a way to check that all the links work without going through and clicking them one at a time (for 3000+ hyperlinks this would be extremely impractical) I Tried using your code however it listed all the hyperlinks as bad when I know that the vast majority are actually good. Any ideas why that might be? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking hyperlinks are valid | Excel Discussion (Misc queries) | |||
checking hyperlinks are valid | Excel Discussion (Misc queries) | |||
checking hyperlinks are valid | Excel Discussion (Misc queries) | |||
hyperlinks - macro not working to take them off the sheet | New Users to Excel | |||
Relative Hyperlinks to local files | Links and Linking in Excel |