How to accommodate document opening confirmations in a UDF

May 7, 2019 at 05:51:14
Specs: Windows 10
This question relates to previous thread 21577 in the Office forum (sorry can't make the link to the thread work), as well as the final function code posted in #7 of a follow-up thread 21578 in the Office forum
Public Function TestURL(url As String, Optional VolatileParameter As Variant) As Boolean

Static request As Object
  If request Is Nothing Then
    Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
    'Not sure if this has to be set for each Open request
    request.SetAutoLogonPolicy 0
  End If
  TestURL = False
  request.Open "HEAD", url
  TestURL = request.Status = 200
End Function

My question is whether it's possible to adapt the code of the function so that if a URL points to a sharepoint 'document' where a harmful files warning pop up is triggered, the 200 status is assumed because of the presence of the pop up?

To explain, when testing the sharepoint links I'm getting a lot of unexpected FALSE responses. When I delved into it I realised that most of the the links with FALSE results point to documents. When I manually click on these links, the harmful files warning pop up requires the user to click OK for the document to be opened.

I'm assuming that the FALSE results are occurring because the function is testing for the 'successfully retrieved' 200 status and if a click on OK isn't made it can't successfully retrieve the contents of the URL.

The end goal is for a TRUE result to be returned in these scenarios because the document exists at the URL location.

message edited by ScottV

See More: How to accommodate document opening confirmations in a UDF

Report •

May 8, 2019 at 10:25:42
Moving this to Office, since A) we're talking about VBA on an Excel spreadsheet, which is the domain of Office, and B) web dev is more about making websites, not accessing them. After all, we don't move any Chrome / Firefox questions to web dev.

On to the question, donno. What status code are you getting for those links? I feel that you need at least a surface level understanding of HTTP to understand what's going on in the script, but I'll save the lecture for now.

How To Ask Questions The Smart Way

Report •
Related Solutions

Ask Question