如何在Excel 2016中进行图像URL验证? [英] How to do image url validation in excel 2016?

问题描述

如果图像URL出现404错误，是否可以通过excel报告有效或无效?
是否没有打开每张图片?例如检查404标头?

这是我尝试过的一些片段

Is there a way to get reported Valid or invalid in excel if an image URL gives a 404 error?
Without opening each image? eg checking header for 404?

here's some snippets I've tried

Function URLExists(url As String) As Boolean
    Dim Request As Object
    Dim ff As Integer
    Dim rc As Variant

    On Error GoTo EndNow
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")

    With Request
      .Open "GET", url, False
      .Send
      rc = .StatusText
    End With
    Set Request = Nothing
    If rc = "OK" Then URLExists = True

    Exit Function
EndNow:
End Function

Public Function IsURLGood(url As String) As Boolean
    Dim request As New WinHttpRequest

    On Error GoTo IsURLGoodError
    request.Open "HEAD", url
    request.Send
    If request.Status = 200 Then
        IsURLGood = True
    Else
        IsURLGood = False
    End If
    Exit Function

IsURLGoodError:
    IsURLGood = False
End Function

这些报告针对所有URL，对我来说是TRUE.当我检查时，例如

These report on all URLS, for me, TRUE. When I check, for example

http://www.bangallawebservices.com/images/BWA22055.jpg

这些绝对会给返回值带来不希望的结果，上图是404错误的示例，通过这些代码段，该错误被视为excel中的有效URL.

these are definitely giving undesired results for return values, the above image is an example of the 404 error that's being counted as a valid URL in excel via these code snippets.

我还尝试了Office PowerUp加载项的免费演示，使用pwrISBROKENURL可以在某些实际上已损坏的情况下返回所有false(未破坏). Excel被授予通过防火墙对Internet的完全访问权限. https://www.youtube.com/watch?v=HU99-fXNV40

I've also tried the free demo of the Office PowerUp addin, with pwrISBROKENURL which returned all false (not broken) when some are in fact broken. Excel is granted full access to internet through firewall. https://www.youtube.com/watch?v=HU99-fXNV40

推荐答案

这对我有用.它不会返回布尔值，而是返回实际的Status或执行过程中是否存在错误说明:

This works for me. It doesn't return a boolean but the actual Status or the error description if there is one during execution:

Public Function IsURLGood(url As String)
    Dim request As Object
    Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
    On Error GoTo haveError
    With request
        .Open "HEAD", url
        .Send
        IsURLGood = .Status
    End With
    Exit Function
haveError:
    IsURLGood = Err.Description
End Function

快速测试:

您可以执行以下操作，而不是将其作为UDF运行:

Instead of running it as a UDF you can do this:

Sub ProcessUrls()
    Dim c As Range
    For Each c in Activesheet.Range("A1:A20000").Cells
        c.Offset(0, 1).Value = IsURLGood(c.Value) 'put result in ColB
    Next c
End sub

这篇关于如何在Excel 2016中进行图像URL验证?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！