vba check URL if it is active or not

NYQuicksale
NYQuicksale used Ask the Experts™
on
i've an excel sheet with too many URLs,what i want is to check that if these urls are active or not. meaning the url is working or not? this can be done with java, if there's anything in vba, that this can be done? without using web query.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try this
http://forums.techguy.org/business-applications/42085-excel-vba-sub-find-if.html

I found out that I can use the 'Open' method of a workbook in the following way:

On Error Resume Next
Set wb = Workbooks.Open(FileName:="xxx")

' Checking if the file exists.
If wb Is Nothing Then

' The file does not exist.
...

Else

' The file exists.
...

End If

and that the file name can be a URL. Of course, in this way the file will be opened in another Excel workbook, so if it's not needed later it should be closed (the files I want to check are always text files, so opening them in an Excel workbook will cause no problems).
This thread may help you:
http://www.mrexcel.com/forum/showthread.php?t=567315

The function given there uses an object to check the status of the URL rather than a web query. It doesn't actually open the web page or file either.

Commented:
I personally use this code all the time. Works great:

Public Function IsURLGood(url As String) As Boolean
    Dim request As New WinHttpRequest
    
    On Error GoTo IsURLGoodError
    request.Open "HEAD", url
    request.Send
    If request.Status = 200 Then
        IsURLGood = True
    Else
        IsURLGood = False
    End If
    Exit Function
    
IsURLGoodError:
    IsURLGood = False
End Function

Open in new window


it's simple to use, for example:
If IsURLGood("http://www.google.com") Then
    ' Do web query
Else
    ' error message, url is not good
End If

Open in new window


Note that to run from VBA you need to enable reference for "Microsoft WinHTTP Services, version 5.1"

Author

Commented:
hi, how to run a function in vba?

Commented:
same way as a subroutine... for example, consider the following code.

You have a sub called "Check_A_URL"  that will see if google's address is valid or not.
On line 6, it calls the function to actually do the check. The function will return either true (if the URL is good) or false (if the url is not good)

make sense?

Sub Check_A_URL()
    Dim urlToCheck As String
    
    urlToCheck = "http://www.google.com"

    If IsURLGood(urlToCheck) Then
        ' Do something with the url
        MsgBox "The url is valid"
    Else
        ' error message, url is not good
        MsgBox "The url is NOT valid"
    End If
End Sub

Public Function IsURLGood(url As String) As Boolean
    Dim request As New WinHttpRequest
    
    On Error GoTo IsURLGoodError
    request.Open "HEAD", url
    request.Send
    If request.Status = 200 Then
        IsURLGood = True
    Else
        IsURLGood = False
    End If
    Exit Function
    
IsURLGoodError:
    IsURLGood = False
End Function

Open in new window

Author

Commented:
hi, whenever i copy some code in my vba module, it turns into one single line, can u guide me on this?

Commented:
All I do is select the text, copy it, and paste it here. Make sure you put it within code tags ( "[ CODE ]" without quotes or spaces before the VBA code, and then "[ /CODE ]" without quotes or spaces after it

If it's still going into one line, maybe try pasting it into notepad, and then copy it from there? I know it sounds silly but I've seen it work before.

Commented:
or wait, are you talking about taking the code *I* posted and pasting it into your VBA module? In that case, yes, try pasting it into notepad or some plain text editor first, then copy it from there. Also, make sure you use the "Select All" button under the code to actually select it

Author

Commented:
hey sorry for being late, i copied it, and still gave me one line, i guess some setting has changed on my side and i dun know abt it. will find it laterr, back on code
the scenerio is in a column A, i've a list of urls, what i want, when i run the code, it must give in column B Pass or Fail for the urls that worked or not respectively. in your code there's a fixed url and also when i ran even that, it highlighted this line
Dim request As New WinHttpRequest

Open in new window

Commented:
The reason that line was highlighted is because you were in the VBA editor and don't have the correct references set. To do that, in the VBA Editor, go to Tools, then click References, then look through the list for "Microsoft WinHTTP Services, version 5.1" and select it like this:
VBA ReferencesThen click OK and the macro should work.

As for your scenario, you have two options.
1) You could call the VBA function directly from the worksheet (you cannot do that with Subs). Consider this example:
 EE-Example1.xlsm

2) Or, you could do as you described, and run a macro that will check all the URL's for you. Consider the following example (Click the button):
 EE-Example2.xlsm

Author

Commented:
just checking and will let u know
You shouldn't need to add the reference if you use late binding, which is what the code I linked to does. It creates a generic object variable and then defines it as the WinHTTP type, rather than defining a WinHTTP variable.

I actually tested both versions, the one I linked to, and the one m4trix gave, and both thought for about two minutes and then timed out without being able to successfully verify an URL. This is possibly due to my network setup here though.

Author

Commented:
i'll check all, my system is quiet slow and testing another process right now, just give me sometime, i'll let u guys know

Commented:
telyni, must be your setup. I use it on multiple computers on multiple networks and it works on all of them...

Author

Commented:
m4tric how to run this function, the other codes i can run via developer tab,how to run it, screenshot is attached, as i'm novice to this aspect of vba
paint.png

Commented:
I'm sorry, I'm not sure what you're asking. That function gets run automatically when the sheet is recalculated so if you change cell A5, then cell B5 should update too.

Did you look at my second example? it uses a button to run the macro, perhaps that is what you are looking for?

Author

Commented:
if that's the case, then it didn't work, i entered a wrong url, and it said true

Commented:
It should work. I just tried it here and it does.

By the way, http://www.yhoo.com is a valid URL... so if that's what you used to test it, then it IS actually working.

If you continue to have issues with it, try the other example I posted. that will only update if you click the button

Author

Commented:
yhoo.com is a working url???????? plz check it again, its opening yahoo.com

Author

Commented:
check one more, i added ww.yhoo.com, still giving true image

Commented:
Yhoo.com does exist. If you click the link and it takes you to yahoo.com then that can be for two reasons: 1) yhoo.com is actually owned by yahoo.com and they have it set up to automatically redirect, or 2) your link is set up to go to yahoo.com even though it says yhoo.com (a link can say anything, no matter where it goes when you click it)
 
Eíther way, try something that definitely doesn't work... I dunno maybe http://www.fjsbgugjsjfielx.com. :)

Author

Commented:
thats amazing, i checked both file, thank u so much m4trix, u have been great

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial