[SOLVED] Trying to check if file exists dynamically based on cell value

Trying to check if file exists dynamically based on cell value

The code I'm sharing does work and validates if a file exists or not. However, I'm trying to separate the URL path from the file to make this run when the cell value changes.

In my example, I want to separate the filename and extension from the URL and have it update based on cell value.

Call checkFile("https://www.mywebsite.com/vdc/") & Range("M17").value

Any guidance is appreciated.

Sub doesFileExist()

    Call checkFile("https://www.mywebsite.com/vdc/VDC-003029.jpg")

End Sub


Function checkFile(sURL As String)

Dim oXHTTP As Object
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "HEAD", sURL, False
oXHTTP.Send

Dim fileExists As Boolean

    If oXHTTP.Status = 200 Then
        'MsgBox "exists"
        Range("N18").Value = "TRUE"
        fileExists = True
    Else
        'MsgBox "doesn't exist"
        Range("N18").Value = "FALSE"
        fileExists = False
    End If

End Function

I tried using an ampersand and a + sign but get errors.

Solution

The structure of your code makes the function very limited. It can only return the result to N18, for instance. It might be better to set it up this way, which gives you flexibility going forward:

Sub doesFileExist()

    Range("N18").Value = fileExists("https://www.mywebsite.com/vdc/" & Range("M17").Value)

End Sub


Function fileExists(sURL As String) As Boolean
    
    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "HEAD", sURL, False
    oXHTTP.Send
    fileExists = (oXHTTP.Status = 200)
    Set oXHTTP = Nothing

End Function