How can I test a built file path within Excel VBA?

Question

Column A contains part numbers such as 499305 and 488212

Sub ProductLoopChemSafe()
Dim X As Integer
Dim lRow As Long
Image1 = 250
Image2 = 500
Image3 = 5000

lRow = Cells(Rows.Count, 1).End(xlUp).Row


For X = 3 To lRow

    
    Range("B" & X).Value = "http://www.chemsafeint.com/images/products/" & Image1 & "/" & Range("A" & X).Value & ".gif"
    Range("C" & X).Value = "http://www.chemsafeint.com/images/products/" & Image2 & "/" & Range("A" & X).Value & ".gif"
    Range("D" & X).Value = "http://www.chemsafeint.com/images/products/" & Image3 & "/" & Range("A" & X).Value & ".gif"
    Range("E" & X).Value = "http://www.chemsafeint.com/files/sds/" & Range("A" & X).Value & ".pdf"
    Range("F" & X).Value = "http://www.chemsafeint.com/files/pds/" & Range("A" & X).Value & ".pdf"
    Range("G" & X).Value = "http://www.chemsafeint.com/files/idf/" & Range("A" & X).Value & ".pdf"
    If Range("A" & X) = "17255" Then
        Range("H" & X).Value = "http://www.chemsafeint.com/files/eds/" & Range("A" & X).Value & ".pdf"
    End If
    If Range("A" & X) = "17418" Then
        Range("I" & X).Value = "http://www.chemsafeint.com/files/epp/" & Range("A" & X).Value & ".pdf"
    End If
    If Range("A" & X) = "17750" Then
        Range("I" & X).Value = "http://www.chemsafeint.com/files/epp/" & Range("A" & X).Value & ".pdf"
    End If
    If Range("A" & X) = "17822" Then
        Range("I" & X).Value = "http://www.chemsafeint.com/files/epp/" & Range("A" & X).Value & ".pdf"
    End If
    
    Next X
End Sub

How can I test to see if a path is valid before I write it?

I'd like to do something like:

DIM Bvalue as string

Bvalue = "http://www.chemsafeint.com/images/products/" & Image1 & "/" & Range("A" & X).Value & ".gif"

If Bvalue is valid then
   Range("B" & X).value = Bvalue
Else
   Range("B" & X).value = ""
END IF

https://stackoverflow.com/questions/44813295/how-to-do-image-url-validation-in-excel-2016/44813655#44813655 — Tim Williams, Jun 21 '21 at 19:52

score 0 · Answer 1 · edited Dec 04 '21 at 14:13

You can write a function that receive the URL as parameter and validate it with some web-scraping code then returns true or false if the object of the link is valid. something like isObject( html_element )

The HTML element could be an element that loads everytime when the stuff that you are searching exists. For example the following URL https://chemsafeint.com/images/products/500/11999-DM55.gif has the object img inside the HTML code ( if you are not familiar please search Web Scraping with VBA ), then you can try this:

Function isValid ( byVal URL as string )as boolean
 'URL is the link that you have just mounted concatenating strings.

 Dim IE As InternetExplorer:  Set IE = New InternetExplorer
        IE.navigate URL
        IE.AddressBar = False
        IE.Visible = True

 Set IEDOC = IE.document
 
 Dim ELE as HTMLImage
 Set ELE = IEDOC.getElementsByTagName("img")

 if isObject(ELE) then
   isValid = true 
 else
   isValid = false
 end if 

IE.Quit
Set IE = NOTHING

Exit Function

How can I test a built file path within Excel VBA?

1 Answers1