excel macro to capture the output image file from website

[kiwiques]

Hi,
I have an excel with a column listed with URLs. These URLs would direct me to image files. What I need to do is only verifying if such image file exists. The problem I have now is: Whenever the image does not exist, the website would replace it with "imagenotfound.png", making me really difficult to check for that image existence.

In that case, would it be better to check its output filename instead? How?

Pls kindly help. Thanks.

 

[kiwiques]

Not sure if anyone can kindly help? I am desperate to get this task completed by today. Many thanks in advanced.

 

[John_w]

Try this, however without example URLs I'm unable to test it with your data. Put this code in a VBA module and call it as a cell formula like this:

=Image_Exists(A2)

where A2 contains a URL. It returns TRUE as the cell value if the image exists, or FALSE if the image src contains "imagenotfound.png".

Public Function Image_Exists(URL As String) As Boolean

    Static httpReq As Object
    Dim HTMLdoc As Object
    Dim images As Object
    
    Image_Exists = False
    
    If httpReq Is Nothing Then Set httpReq = CreateObject("MSXML2.XMLHTTP")
    
    With httpReq
        .Open "GET", URL, False
        .send
        Set HTMLdoc = CreateObject("HTMLfile")
        HTMLdoc.body.innerHTML = .responseText
    End With
    
    Set images = HTMLdoc.getElementsByTagName("IMG")
    If images.Length > 0 Then
        Image_Exists = InStr(1, images(0).src, "imagenotfound.png", vbTextCompare) = 0
    End If
    
End Function

 

[kiwiques]

Thanks John - it seems the output is always FALSE even though the image is found. Can you help further? thx.

 

[John_w]

As I hinted, post some example URLs: some which direct to a valid image and some which don't.

 

[kiwiques]

Thanks John - I've PM you. Pls help

 

[John_w]

Try this UDF:

Public Function Image_Exists(URL As String) As Boolean

    Static httpReq As Object
    
    If httpReq Is Nothing Then Set httpReq = CreateObject("MSXML2.XMLHTTP")
    With httpReq
        .Open "GET", URL, False
        .send
        Image_Exists = InStr(.getResponseHeader("Content-Disposition"), "imagenotfound.png") = 0
    End With
        
End Function

 

[kiwiques]

Thanks very much John. You solved my problem!