How to do image url validation in excel 2016?

Question

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

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

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

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.

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

Well, the snippets work fine for me on the examples you've provided. — , Jun 28 '17 at 22:08
"I tried some stuff but nothing worked" is typically not a good approach when posting here. Including the actual code you tried, along with a description of what happened when you ran it will get you more suggestions and help. — Tim Williams, Jun 28 '17 at 22:13
That wasn't me, but that edit's not going to improve your chances... There's a pretty good answer here: https://www.mrexcel.com/forum/excel-questions/567315-check-if-url-exists-so-then-return-true.html Maybe change the GET to HEAD, since you don't really need to actually download the image. — Tim Williams, Jun 28 '17 at 22:17
...or look here https://stackoverflow.com/questions/36064315/excel-vba-script-to-find-404-errors-in-a-list-of-urls/36065167#36065167 — Tim Williams, Jun 28 '17 at 22:20
Post is edited now to share details, and code examples used, including 404 image link. That is actually one of the tutorials and code snippets I've attempted already @TimWilliams I just finished a college class in excel, we didn't cover very much in VBA, just the basics for seeing macro codes after they are recorded. — Rocket Spaceman, Jun 28 '17 at 22:27
I just plugged your sample url into [this](https://stackoverflow.com/questions/1542790/msxml2-xmlhttp-request-to-validate-entered-url-in-asp-classic) and it reported a 404 error. — , Jun 28 '17 at 22:40

Tim Williams · Accepted Answer · 2017-06-29T04:28:33.083

4

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

Quick test:

EDIT:

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

edited Jun 29 '17 at 04:28

answered Jun 28 '17 at 22:41

Tim Williams

154,628
8
97
125

That worked! Thank you so much @TimWilliams! [screenshot](http://i.imgur.com/eSUn4ta.png) – Rocket Spaceman Jun 28 '17 at 23:03
1

@LevyImage - If you have 30K rows (per your original post) then you are going to want to make that WinHttp.WinHttpRequest.5.1 **static** like [this](https://stackoverflow.com/documentation/vba/877/declaring-variables/16978/when-to-use-a-static-variable#t=20170628234448361758). – Jun 28 '17 at 23:45
In addition/alternatively to suggestion from @Jeeped, consider not using a UDF but a Sub instead, to ensure you're only running each check once. – Tim Williams Jun 29 '17 at 00:32
Do you have any suggestions on how to basically rasterize the column used with this formula to just leave behind the values? When I copy/paste values only, it freezes excel (over 20K rows). If I try to save as a text based file type like CSV, excel also freezes. I'll read up on what the difference between UDF and Sub, again I'm new to VB, thank you. – Rocket Spaceman Jun 29 '17 at 03:15

How to do image url validation in excel 2016?

1 Answers1

Linked

Related