VBA – Check if site is online

And here is one more gift from our forum. I love the forum (Did I already say that?).

It was a simple question: How do I check if a site is online? Ok, after a rapid internet search, I’ve found a interesting post on MrExcel forum (by the way, it’s excellent!):

http://www.mrexcel.com/forum/excel-questions/707305-excel-visual-basic-applications-check-if-certain-website-online.html

It brought to us the function below:

Function getHtmlFromUrl(pURL As String) As String
     Dim resText As String
     Dim objHttp As Object
     Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
     objHttp.Open "GET", pURL, False
     objHttp.Send ""
     getHtmlFromUrl = Mid(objHttp.ResponseText, 1, 255) 
End Function

The function uses the MSXML2.ServerXMLHTTP library to create a request to a valid URL and returns the HTML of response. It is useful, but, it’s to much if you only want to check if the site is online. The only thing you need to check is the HTTP Code. If the code is 200, everything is ok!

So, I’ve changed the code a bit to this (my apologies for the portuguese names, but, you know, this is a brazilian blog – 🙂 ):

Sub EstaOnline()
 Debug.Print IsSiteOnline("http://www.tomasvasquez.com.br")
End Sub
 
Function IsSiteOnline(pURL As String) As Boolean
On Error GoTo TrataErro
 Dim resText As String
 Dim objHttp As Object
 Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
 objHttp.Open "GET", pURL, False
 objHttp.Send ""
 IsSiteOnline = objHttp.Status = 200
 
TrataSaida:
 Exit Function
TrataErro:
 IsSiteOnline = False
 GoTo TrataSaida
End Function

I’ve created the EstaOnline Sub as an example of how to call the IsSiteOnline function, which returns a boolean value. If it’s True, the site is online. Otherwise, it’s offline, broken, down, missed…. whatever.

More about ServerXMLHTTP:

http://msdn.microsoft.com/en-us/library/ms766431(v=vs.85).aspx

File Sample:

http://www.tomasvasquez.com.br/forum/download/file.php?id=2246

Enjoy!

Comentários

comentários