I have a spreadsheet with 1000+ sharepoint URLs that I'll need to regularly test for validity, so that action can be taken to fix any broken links. I found a user defined function via google, but it just returns the 401 error code on each URL. I assume this is because it's a sharepoint location rather than an internet location (sorry if this is the wrong terminology).
The function I'm using is as follows:
Public Function CheckURL(url As String) As String Dim request As New WinHttpRequest request.Open "GET", url request.Send CheckURL = request.Status End FunctionCan you provide any assistance?
Thanks
message edited by ScottV
Correction, SetAutoLogonPolicy is a function, not a property. Need to remove the equals sign. request.SetAutoLogonPolicy 0There's a SetCredentials method you could use. Its arguments are userName, userPassword, 0. In that order.
How To Ask Questions The Smart Way
message edited by Razor2.3
ScottV: I'd like to move this question to the Web Development forum. I think you'll get some better http related info there.
Before I do that, I'll mention that I tested the function and it does indeed work for internet locations, such as computing.net, Google, etc. I don't have a sharepoint URL available for testing.
VBA needs a reference to Microsoft WinHTTP Services, version 5.1, but once that's done the function works fine - for standard internet locations.
message edited by DerbyDad03
Well, first step would be to figure out what the status code means. PS C:\> [Net.HttpStatusCode]401 UnauthorizedThere you go. Your Sharepoint's not configured to allow anonymous access, so you'll have to provide credentials. This also means not all legitimate Sharepoint links are valid for all people. Since you haven't considered the need to sign in to Sharepoint, you're probably relying on your Active Directory authorization.
Completely untested, so let's see how this goes:
Public Function TestURL(url As String) As Boolean TestURL = False Dim request As Object : Set request = CreateObject("WinHttp.WinHttpRequest.5.1") request.Open "HEAD", url 'WINHTTP_OPTION_AUTOLOGON_POLICY = WINHTTP_AUTOLOGON_SECURITY_LEVEL_LOW request.Option(77) = 1 request.Send TestURL = request.Status = 200 End Function
Hi Razor2.3 So I inserted a new module and pasted the code in. I also checked that Microsoft WinHTTP Services, version 5.1 was enabled.
The hyperlinks are in column M from row 2 onwards. In cell O2 I entered "=TestURL(M2)" which returned "#VALUE!"
Have I missed anything?
message edited by ScottV
One of us probably is. Try replacing the Option line with request.SetAutoLogonPolicy = 0
No change unfortunately. Still getting "#VALUE!". Is there a way to ask for the user's windows credentials as part of the function? Would that help with the anonymity aspect? (clearly I don't know what I'm talking about. Just chucking out ideas).
message edited by ScottV
Correction, SetAutoLogonPolicy is a function, not a property. Need to remove the equals sign. request.SetAutoLogonPolicy 0There's a SetCredentials method you could use. Its arguments are userName, userPassword, 0. In that order.
How To Ask Questions The Smart Way
message edited by Razor2.3
Yes Razor! It worked. :-) Thank you. The only trouble I'll have is that it's slow. It took about 12 minutes to check a sample of 232 URLs. The full list will be over 1000 long.
Is there a way to run this without actually opening the workbook and just emailing the finished article to a recipient? Or is that a whole other story?
What, like running it to completion once, copying the column, and paste special -> paste values? ¯\_(ツ)_/¯ Maybe DD03 has some ideas. How To Ask Questions The Smart Way
message edited by Razor2.3
Yes. Except copying the URL column as well as the result column. The idea will be that the user can then filter the FALSE results and go the page owners to find out where a document or page has been moved to, then update the master list with the new link.
Thinking about it I'm going to mark as resolved and post a new question as the solution will be of interest to others not interested in my original problem.
Ad Choices