Is it possible to check via VBA if file exist on a sharepoint site?

excel, hyperlink, sharepoint, vba

Solution

Give this a shot:

Function checkFile(URLStr As String) As Boolean
    Dim oHttpRequest As Object
    Set oHttpRequest = New MSXML2.XMLHTTP60
    With oHttpRequest
        .Open "GET", URLStr, False, [Username], [Password]
        .setRequestHeader "Cache-Control", "no-cache"
        .setRequestHeader "Pragma", "no-cache"
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send
    End With
    If oHttpRequest.Status = 200 Then
        checkFile = True
    Else
        checkFile = False
    End If
End Function

`URLStr` should be something like "http://sharepoint/site/user.xlsx". Enter your Username/Password in the `.Open` line to pass them to the site, and this should work for any URI (I was testing it against .xlsx files for example). I should point out that on my internal SharePoint sites, I don't need to pass the UN/PW in order to run this function, so if that ends up being the case for you, just remove those parameters from the `.Open` call. Also, all the header stuff probably isn't necessary, but I always have them in my requests so I left them in.

Problem

I'm trying to write an Excel (2010) macro that at some point would have to confirm existence of a certain file (doc/pdf) on a corporate sharepoint site. The file is accessible through Internet Explorer (all rights are granted to the user). I have a direct link to that file. I don't need to open it, just check if it's there. If this was a local file, I'd use Dir() to check if the file exists. However, this won't work with URIs. I tried a method based on GET via objHttp but the only response I recieve is a wepage stating that "I am not authorized to view this page" [in tag]. Is this doable in any way?

Original source