Solved vba to test sharepoint url validity

April 24, 2019 at 04:05:48
Specs: Windows 10
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 Function

Can you provide any assistance?

Thanks

message edited by ScottV


See More: vba to test sharepoint url validity

Report •

✔ Best Answer
April 26, 2019 at 00:51:25
Correction, SetAutoLogonPolicy is a function, not a property. Need to remove the equals sign.
request.SetAutoLogonPolicy 0

There'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



#1
April 24, 2019 at 06:28:33
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


Report •

#2
April 24, 2019 at 12:45:58
Well, first step would be to figure out what the status code means.
PS C:\> [Net.HttpStatusCode]401
Unauthorized

There 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

How To Ask Questions The Smart Way


Report •

#3
April 25, 2019 at 00:39:18
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


Report •

Related Solutions

#4
April 26, 2019 at 00:03:52
One of us probably is. Try replacing the Option line with
request.SetAutoLogonPolicy = 0

How To Ask Questions The Smart Way


Report •

#5
April 26, 2019 at 00:42:50
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


Report •

#6
April 26, 2019 at 00:51:25
✔ Best Answer
Correction, SetAutoLogonPolicy is a function, not a property. Need to remove the equals sign.
request.SetAutoLogonPolicy 0

There'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


Report •

#7
April 26, 2019 at 01:06:11
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?


Report •

#8
April 26, 2019 at 01:29:44
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


Report •

#9
April 26, 2019 at 01:33:46
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.

Report •

#10
April 26, 2019 at 06:29:17
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.

Report •

Ask Question