VBA - Check for active internet connection

Question

Wrote a small app that accesses a bunch of search websites and puts the results in a word document, which gets run a few hundred times a day.

It saves individual search results in a number of local folders so the next time those words are searched, it grabs them locally instead of loading the website again.

This works fine - even though it's not quick. People are impressed because until a few weeks ago they did this manually by literally loading up six different search websites, searching, and then copying and pasting the results in a word document.

However, our Office's internet is unreliable, and has been down the last half a day. This has meant about 400 bad searches have been saved in the local folders, and inserted into the final documents.

When a person was searching they could tell if the internet was broken and they would do their searches later. Obviously, though, this app can't tell, and because I'm not using APIs or anything, and because I am limited to using the VBA environment (I'm not even allowed MZ tools), I need to find some way to check that the internet is working before continuing with the program flow, without relying on too many references, and preferably without screenscraping for the phrase "404 Page Not Found".

I'm not very familiar with VB, and VBA is ruining me in so many ways, so there's probably some easy way to do this, which is why I'm asking here.

Appreciate any help.

Answer 1

Unfortunately, this is a bit of a difficult question to answer for a couple of reasons:

1. How do you define a non-working internet connection? Do you check for a valid IP address? Do you ping out? How do you know that you have permissions to check these things? How do you know that the computer's firewall/antivirus isn't causing wonky behavior?
2. Once you've established that the connection is working, what do you do if the connection drops mid-operation?

There are probably ways to do what you want to do, but a lot of "devil's in the details" type things tend to pop up. Do you have any way to check that the saved search is valid? If so, that would probably be the best way to do this.

Answer 2

Obviously, your problem has many levels. You should start by defining "connected to the internet", and go on with developing fallback strategies that include not writing invalid files on failure.

As for the "am I connected" question, you can try tapping into the Win32 API:

Private Declare Function InternetGetConnectedState Lib "wininet" _
(ByRef dwflags As Long, ByVal dwReserved As Long ) As Long

Public Function GetInternetConnectedState() As Boolean
  GetInternetConnectedState = InternetConnectedState(0&,0&)
End Function

Though depending on your network setup (proxy/NAT/firewall restrictions etc.), Windows might have a different opinion about this than you.

Trying to GET the pages you are interested in, checking on the return status in the HTTP headers (gateway timeout, 404, whatever you expect to happen when it "doen't work) might also be a way to go.

Answer 3

Building on shakalpesh's answer and the comments to it, there are (at least) two ways to get the web page into Word without parsing the XML returned by the XMLHTTP60 object.

(NB the HTTP status code of 200 indicates that "the request has succeeded" - see here)

• write the XMLHTTP60.ResponseText out to a text file and then call Documents.Open on that text file

If (xhr.Status = 200) Then
    hOutFile = FreeFile
    Open "C:\foo.html" For Output As #hOutFile
    Print #hOutFile, xhr.responseText
    Close #hOutFile
End If

// ...

Documents.Open "C:\foo.html"

This has the disadvantage that some linked elements may be lost and you'll get a message box when the file opens

• check the URL status with the XMLHTTP60 object and then use Documents.Open to open the URL as before:

If (xhr.Status = 200) Then
    Documents.Open "http://foo.bar.com/index.html"
End If

There is a slight chance that the XMLHTTP60 request could succeed and the Documents.Open one fail (or vice versa). Hopefully this should be a fairly uncommon event though

Answer 4

Use the following code to check for internet connection first anable XML v6.0 in your references

Function checkInternetConnection() As Integer
'code to check for internet connection
'by Daniel Isoje
On Error Resume Next
 checkInternetConnection = False
 Dim objSvrHTTP As ServerXMLHTTP
 Dim varProjectID, varCatID, strT As String
 Set objSvrHTTP = New ServerXMLHTTP
 objSvrHTTP.Open "GET", "http://www.google.com"
 objSvrHTTP.setRequestHeader "Accept", "application/xml"
 objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
 objSvrHTTP.Send strT
 If err = 0 Then
 checkInternetConnection = True
 Else
  MsgBox "Internet connection not estableshed: " & err.Description & "", 64, "Additt !"
 End If
End Function