Creating URL Validation Function

Hello all I'm trying to get this function to work.

I created a function to check the validity of URL in VBA.

I try to run it and get a compile error. Any help thanks!

Public Function CheckURL(url As String)
 Dim request As Object
    Set request = CreateObject("winhttp.winhttprequest.5.1")
    On Error GoTo haveError
    With request
    .Open "head", url
    .send CheckURL = .Status
    End With
End Function
haveError:
CheckURL -Err.Description
End Function

Hello, I've adjusted code to bellow. Is there a way to get the infor from the URLs with out opening the browser? Thank you for the look !

Public Function CheckURL(inlink As String) As Boolean
On Error GoTo Handler

ThisWorkbook.FollowHyperlink (inlink)

CheckURL = True
Exit Function
Handler:
CheckURL = False
End Function

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html


I believe this would have worked?

Public Function CheckURL(url As String)
Dim request As Object
Set request = CreateObject("winhttp.winhttprequest.5.1")
On Error GoTo haveError
With request
    .Open "head", url
    .send
    CheckURL = .Status
End With
Exit Function
haveError:
CheckURL = Err.Description
End Function

Edit: just FYI I had http://google.com/ in A1, and in another cell I put = CheckURL(A1)
It returned 200

The button part is easy. Insert a button on the sheet, right-click, choose assign macro, and pick the macro you want... but that suggests not really needing a UDF (user defined function). The reason the second example does not work is that the text of A2 is not a real hyperlink, it is missing the http:// part (which your browser takes as implied, but the code does not)
My guess is that A1 does work most of the time?, but it depends on internet speed perhaps?

you could try adding one extra line and see if that fixes the timed out issue:

With request
    .Open "head", url
    .send
    .WaitForResponse
    CheckURL = .Status
End With

If we do this via a button then we can modify the code a little, so that it picks up the hyperlink address instead of the text of the cell. That may solve issue of A2. That is, assuming that there are hyperlinks and not just plain text?

The "freezing" could be partially caused by any urls that are slow responding since we added .WaitForResponse, if you take that out you will get some "timed out" errors.

If all the cells had the url in the complete format ("http://.....") then I think you would have no issues but if they are not all in that format then we can try to fix it. Could you provide a sample workbook with a list of urls that we can use for testing?

Here is a starting point for a macro that you could assign to a button. Check for the worksheet name, columns I am using in the code as well as rows, so you can adjust those if needed:

Sub CheckURLs()

Dim request As Object
Dim ws As Worksheet
Dim lr As Long, i As Long
Dim url As String

Set request = CreateObject("winhttp.winhttprequest.5.1")
Set ws = ThisWorkbook.Worksheets("Sheet1")
'last row with data in column A:
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
On Error GoTo haveError

For i = 2 To lr 'starts on row 2 assuming there are headers, if not then adjust
next_row:
    With request
        url = ws.Range("A" & i).Value
        'if the url in the cell doesn't start with http:// then we add it, hoping that resolves any issues.
        If Not url Like "http*" Then url = "http://" & url
        .Open "head", url
        .send
        .WaitForResponse
        ws.Range("B" & i).Value = .Status
    End With
Next i
Exit Sub
haveError:
ws.Range("B" & i).Value = Err.Description
'if there are still rows to process then go back up:
If i < lr Then GoTo next_row

End Sub

The file you provided has very few links... which is fine, but when we spoke yesterday you mentioned some urls were just www.google.com for example, without "http://"
It is always best to give some sample data that covers all the possible situations.

However based on what you provided, you would just need to change the "for i = 2 to lr" to start at 2 instead of 3... and ONE button anywhere on the sheet, with the macro I posted in #11 should work. However, it outputs results in B so you may want to change

ws.Range("B" & i).Value = .Status

to

ws.Range("C" & i).Value = .Status

and in the error catch:

ws.Range("B" & i).Value = Err.Description

to

ws.Range("C" & i).Value = Err.Description

Unless you REALLY want to use a button for each url (which seems painful to me), instead of one button to process the whole list?

Hmm... That code worked perfectly for the sample data... I even added a bunch of addresses some with and some without http://
Does it get stuck on one particular url?

I've attached screenshots of the errors.

I have used


http://google.com "The operation timed out"
http://yahoo.com
http://cnn.com

hmm... what is the value of url at that point?

Using exactly the code from post #11 this is what I get (with no errors)... so I'm a bit confused as to what is wrong:

result.jpg

Opened the file... ran the code, and got 200 for all three. Any chance there is a network/internet problem on your end?

ugh... well.. I can access the links in browsers or clicking on the hyperlink itself. it does take longer then usual but that what I thought .wait for response corrected.

Not sure what would cause the return value to time out. I am getting 440 Automation error however.

Also the loop breaks when it times out.

would "on error resume next" be something to add?

Yes it should have corrected that... though I don't know how long it waits before deeming it timed out. The unresolved url on yahoo confuses me more. On error resume next would work for getting through the list, but disables your error checking so then you would not get the error description.

but to use iit