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 |
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("https://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 |
Sub EstaOnline()
Debug.Print IsSiteOnline("https://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:
https://www.tomasvasquez.com.br/forum/download/file.php?id=2246
Enjoy!