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

Question

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?

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.

Comments

[Alex K.]

Is the link you have accessible directly? i.e. with no logon/saved logon in the browser?

[Sigar Dave]

that's C# code , you can have idea about the logic or convert it.

[Kuba]

I tried that. It looked promising because WebClient() is available in C++/C# and VB (in .NET). But apparently it's not recognized in VB for Applications. Thanks anyway for your contribution.