Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 30th 06, 01:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 3
Default 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   Report Post  
Old December 1st 06, 10:53 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 3
Default 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   Report Post  
Old September 21st 17, 05:04 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
checking hyperlinks are valid Rafael Guerreiro Osorio Excel Discussion (Misc queries) 1 February 17th 11 10:00 PM
checking hyperlinks are valid Garbagh Excel Discussion (Misc queries) 0 November 30th 06 02:16 PM
checking hyperlinks are valid Rafael Guerreiro Osorio Excel Discussion (Misc queries) 0 November 30th 06 01:34 PM
hyperlinks - macro not working to take them off the sheet Lorelei New Users to Excel 1 September 23rd 06 02:18 AM
Relative Hyperlinks to local files CR Optiker Links and Linking in Excel 3 April 5th 06 06:05 AM


All times are GMT +1. The time now is 02:00 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017