HTTP request to sharepoint site

Question

I'm trying to see if a file exists on sharepoint. For this is use below code. If I use "MSXML2.XMLHTTP.6.0" then I get an access denied error.

With "MSXML2.ServerXMLHTTP.6.0" the oHttpRequest.Status returns always 200 even if the file is not there. The URL gets passsed with URLStr and looks something like this:

"https://company.sharepoint.com/sites/abc/def/xyz.xlsx"

If I open the file with other VBA code the first example runs fine (maybe because authentication was done somehow?). Online many posts said to add "s" to "http" -> "https", but it changed nothing.

The idea is that the workbook checks online if it is the latest version and if not informs the user. Maybe there is a better way to do this?

Public Function checkversionmethod2(URLStr As String) As Boolean

Dim oHttpRequest As Object

'Set oHttpRequest = CreateObject("MSXML2.XMLHTTP.6.0") 'authentication issue
Set oHttpRequest = CreateObject("MSXML2.ServerXMLHTTP.6.0") 'always return 200
   
With oHttpRequest
    .Open "HEAD", URLStr, False ', [UserName], [Password]
    .setRequestHeader "Cache-Control", "no-cache"
    .setRequestHeader "Pragma", "no-cache"
    .send
End With

If oHttpRequest.Status = 200 Then
    fileexists = 1
Else
    fileexists = 3
End If
    
Set oHttpRequest = Nothing

End Function