检查 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屋！