检查Excel中的断链接 [英] Checking for broken hyperlinks in Excel
问题描述
我有一大堆超链接(加上几个废话单元),我需要检查。我需要知道哪些链接仍然有效,哪些不再存在或返回404(或其他)错误。我一直在使用此条目中的建议:使用VBA对Excel中的超级链接进行排序? ,它在一小段链接中工作得很好,其中一些是我故意打破了自己。然而,现在我尝试在我的实际超链接列表中使用相同的宏,它根本不会工作!我已经手动检查了几个,并找到404错误的链接。再次,当我故意地输入其中一个地址时,它会选择它,但它不会拾起已经破碎的列表中的任何一个。
I have a large list of hyperlinks (plus a few cells of nonsense) that I need to check. I need to know which links are still active and which no longer exist or return a 404 (or other) Error. I have been using the advice in this entry: Sort dead hyperlinks in Excel with VBA? and it worked great in a small selection of links, some of which I deliberately broke myself. However, now that I try to use the same macro on my actual list of hyperlinks it won't work at all! I've manually checked a few and have found links with 404 errors. Again, when I deliberately mistype one of the addresses it will pick that up but it won't pick up any in the list that were broken already.
我完全新的宏,我真的在黑暗中绊倒在这里。任何帮助/建议将非常感谢!
I'm totally new to macros and am really stumbling about in the dark here. Any help/advice would be very much appreciated!
推荐答案
我已经使用了一段时间,它已经工作我
I've been using this for a while and it has been working for me.
Sub Audit_WorkSheet_For_Broken_Links()
If MsgBox("Is the Active Sheet a Sheet with Hyperlinks You Would Like to Check?", vbOKCancel) = vbCancel Then
Exit Sub
End If
On Error Resume Next
For Each alink In Cells.Hyperlinks
strURL = alink.Address
If Left(strURL, 4) <> "http" Then
strURL = ThisWorkbook.BuiltinDocumentProperties("Hyperlink Base") & strURL
End If
Application.StatusBar = "Testing Link: " & strURL
Set objhttp = CreateObject("MSXML2.XMLHTTP")
objhttp.Open "HEAD", strURL, False
objhttp.Send
If objhttp.statustext <> "OK" Then
alink.Parent.Interior.Color = 255
End If
Next alink
Application.StatusBar = False
On Error GoTo 0
MsgBox ("Checking Complete!" & vbCrLf & vbCrLf & "Cells With Broken or Suspect Links are Highlighted in RED.")
End Sub
这篇关于检查Excel中的断链接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!