Need a macro to test all URL Hyperlinks in an entire workbook?

[DB]

Looking for a macro or script that will test all hyperlinks in an entire
workbook and mark all cells with broken links?

Thanks
Doug

[John Walkenbach]

I'm pretty sure that Excel cannot do that, and I can't think of anyway to do
it with a macro.

Have you considered copying the worksheet to FrontPage? Then you can use
FrontPage's hyperlink test feature.

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"DB" <dbgr...@yahoo.com> wrote in message
news:#SThMZJ3AHA.2192@tkmsftngp07...

[DB]

I had considered using Frontpage or another HTML editor but would prefer to
not do it that way. The spreadsheets are at least 10,000 lines long to as
big as 250,000 lines long.....that would really be painful in Frontpage...

I have seen several Visual Basic macros that will allow you to change or
delete Hyperlinks...just have not found one to just test and mark broken
links.

Doug

"John Walkenbach" <jo...@j-walk.com> wrote in message
news:ulJ593J3AHA.1976@tkmsftngp07...

[Jake Marx]

Hi Doug,

You can try Automating the Inet (Internet Transfer Control) object. I
haven't tested this code much, but it may work for you. Basically, it goes
through each Hyperlink object and tries to get the page - if it is
unsuccessful, it will color the cell red. This could take some time to
execute if you have a lot of links to check, as the timeout period is set to
5 seconds. You can lower the timeout, but you may find that slower servers
will start showing up as "broken" links.

For this code to work, you must set a reference to the Microsoft Internet
Transfer Control. Go to Tools | References, click Browse, change the
filetype to .OCX, then look in your System or System32 folder for
MSInet.ocx.

One note: this code will only work for URLs - other types of Hyperlinks (to
ranges or other files) will be marked as "broken".

Regards,
Jake Marx

Sub CheckHyperlinks()
Dim itc As Inet
Dim ws As Worksheet
Dim hyp As Hyperlink

Set itc = New Inet
With itc
.Protocol = icHTTP
.RequestTimeout = 5
End With
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each hyp In ws.Hyperlinks
If Len(itc.OpenURL(hyp.Address, icString)) Then
hyp.Parent.Interior.ColorIndex = _
xlColorIndexNone
Else
hyp.Parent.Interior.ColorIndex = 3
End If
Next hyp
Next ws
On Error GoTo 0
Set itc = Nothing
End Sub

"DB" <dbgr...@yahoo.com> wrote in message

news:#5kFGLK3AHA.2032@tkmsftngp05...

[Stephen Bullen]

Hi Db,

> Looking for a macro or script that will test all hyperlinks in an entire
> workbook and mark all cells with broken links?

There's nothing built in to Excel directly, but you can use the Internet Transfer Control to do the
checking, i.e.:

- Loop through all the hyperlinks on all the sheets
- Read the URL and tell the ITC to open it
- Read the header from the opened URL, which will either be blank, OK or 404 for not found.

You'll need a reference to the Microsoft Internet Transfer Control (msinet.ocx).

The code is:

Sub TestAllLinks()

Dim oHL As Hyperlink
Dim oITC As InetCtlsObjects.Inet
Dim s As String

'Create a new Internet Transfer Control
Set oITC = New InetCtlsObjects.Inet

'Loop through all the hyperlinks in the active workbook
For Each oSht In ActiveWorkbook.Worksheets
For Each oHL In oSht.Hyperlinks

'Inter-sheet hyperlinks don't have an Address
If oHL.Address <> "" Then
On Error Resume Next

'Allow a 2-second timeout
oITC.RequestTimeout = 10

'Open the URL
oITC.OpenURL oHL.Address

'Allow to finish (just in case)
Do While oITC.StillExecuting
DoEvents
Loop

'Read the header from the page
s = ""
s = oITC.GetHeader
oITC.Cancel

'If no header, or a 404, it's a bad link
If s = "" Or InStr(1, s, "404") > 0 Then

oHL.Range.Interior.ColorIndex = 3
Debug.Print oHL.Address & " : BAD"
Else
oHL.Range.Interior.ColorIndex = xlColorIndexNone
Debug.Print oHL.Address & " : GOOD"
End If
End If
Next
Next

End Sub

Regards

Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk or http://208.49.24.208

[DB]

Thanks Stephen: When I run this macro I am getting an error under windows
2000/exel 2000 of: "The instructions at "0x6f43204c" referenced memory at
"0x6f43204c". The memory could not be "read".

Any ideas??

Doug

"Stephen Bullen" <Ste...@BMSLtd.ie> wrote in message
news:VA.0000084...@bmsltd.ie...

[DB]

Stephen: Actually the error code from you suggestion was
"0x75646e6f".......Jakes code suggestion had the error code I quoted to you.

Does this make any difference?? Any suggestions?

Thanks..I appreciate your help...

Doug

"Stephen Bullen" <Ste...@BMSLtd.ie> wrote in message
news:VA.0000084...@bmsltd.ie...

[DB]

Jake: I appreciate the help......

I am getting an error when I run this macro under windows

2000/exel 2000 of: "The instructions at "0x6f43204c" referenced memory at
"0x6f43204c". The memory could not be "read".

Any ideas??

Doug

"Jake Marx" <ja...@longhead.com> wrote in message
news:uQmNoiK3AHA.1744@tkmsftngp05...

[DB]

Stephen: I tried it on a new spreadsheet with just 10 urls I typed in and I
created a non-existent url as well to see what happens. It worked!

I'm not sure why it is not working on my other workbook......any ideas on
either yours or Jake's memory error message??

Thanks,
Doug

"Stephen Bullen" <Ste...@BMSLtd.ie> wrote in message
news:VA.0000084...@bmsltd.ie...

[DB]

Jake: I tried it on a new spreadsheet with 10 lines of URLs with only one
fake url...it still had a memory error of: The instructions at "0x77c77b2d"
referenced memory at "0x72636951". The memory could not be "written".

Any ideas??? I tried Stephens suggestion and it worked on the 10 lines but
still had an error with a much larger workbook.

Thanks,
Doug

"Jake Marx" <ja...@longhead.com> wrote in message
news:uQmNoiK3AHA.1744@tkmsftngp05...

[DB]

I have done some additional testing and keep getting different error
messages that force me to close Excel and loose the changes made by the
macro.

Any suggestions...

Doug

"DB" <dbgr...@yahoo.com> wrote in message

news:u1dkFzL3AHA.2012@tkmsftngp05...

[Jake Marx]

Hi Doug,

I'm not sure why you're having problems with our code. I am able to run the
code on my machine (Win 2K Server, XL 2K SR-1, 600 MHz PIII, 512 MB RAM) on
100 hyperlinks with no problems. If your version of Excel 2000 is not SR-1,
download and install that Service Release and try again.

If you are already running SR-1, or if you upgraded and are still having
problems, it may be a timing issue. Many times, adding DoEvents here or
there in the code will solve those types of problems.

Regards,
Jake Marx

"DB" <dbgr...@yahoo.com> wrote in message

news:eYAfN1L3AHA.320@tkmsftngp02...

[Stephen Bullen]

Hi Doug,

> I have done some additional testing and keep getting different error
> messages that force me to close Excel and loose the changes made by the
> macro.

Sorry Doug, I've no idea, though it seems likely that the Internet
Transfer Control is misbehaving. What versions of Windows and IE are you
using?