Check for internet connection, and return True/False?????

K

krazykaj

Board Regular
Joined
Jul 20, 2005
Messages
143
Hello,

I was curious if there was a way to do the following: :)

On a workbook open/initialize, check to see if Excel (the PC) can successfully connect to the internet.

If, or when* internet connection is possible, cell A1 will equal "TRUE"
If, or when* internet connection is not possible, A1 will equal "FALSE"
*And from then on, every 5 minutes after the open of the file, the internet connection test will run again, updating A1 as necessary. The test will obviously run every five minutes until the file is closed, on which cell A1 will be reset to “False”
(Hope that made sense)

I would also like, if possible, to have cell B1 equal the actual status of the internet connection test. So while the connection test is running, seeing if the PC can connect to the internet (some PC’s may take a little longer due to a modem etc.), B1 will equal "Checking". And while the connection test is not running, during the 5 minutes intervals, B1 will equal "Idle".

I have no idea of how to check the actual internet connectivity, and how compatible it is across platforms, or if it is even easily possible, so any help, expertise, suggestions, comments would be most welcome. :biggrin:

Thankyou for you time.

Kind Regards,
KJ
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Check the below functions, if they help you.

In a module place the following;

Code: 
Declare Function InternetGetConnectedStateEx Lib "wininet.dll" _
       (ByRef lpdwFlags As Long, ByVal lpszConnectionName As String, _
        ByVal dwNameLen As Integer, ByVal dwReserved As Long) As Long

Dim strConn As String * 255
'
Function GetInternetState() As Boolean
    Dim RetVal As Long
    RetVal = InternetGetConnectedStateEx(RetVal, strConn, 254, 0)
    If RetVal = 1 Then
        GetInternetState = True
    Else
        GetInternetState = False
    End If
End Function
'
Function GetStatus() As String
    GetStatus = strConn
End Function

Then in cell A1, enter this:

=GetInternetState()

And in cell B1, enter this:

=GetStatus()

Also, see the link below;

http://www.mrexcel.com/board2/viewtopic.php?t=142849
 
Upvote 0
Hi again;

So, related with the link I've given at the end of my message above .... I've revised the code.

You'll need to copy the code below in a new module, save the workbook and re-open it again.

If there is an active internet connection, in cell A1 a "TRUE", otherwise a "FALSE" message will appear.

In case of internet connection is not possible, i.e. there is a "FALSE" message in cell A1, the code will re-try to check the internet connection till a "TRUE" message returns, for every 5 minutes. Then, it will stop to check the availability of the internet connection.

Also, the status of the process will be shown to the user in cell B1.

The related code is as below;

Rich (BB code): 
Declare Function InternetCheckConnection Lib "wininet.dll" _
        Alias "InternetCheckConnectionA" (ByVal lpszUrl As String, _
        ByVal dwFlags As Long, ByVal dwReserved As Long) As Long
        
Declare Function InternetGetConnectedStateEx Lib "wininet.dll" _
       (ByRef lpdwFlags As Long, ByVal lpszConnectionName As String, _
        ByVal dwNameLen As Integer, ByVal dwReserved As Long) As Long
'
Dim RunWhen As Double
Const RunWhat = "CheckInternetState"
'
Sub Auto_Open()
    Call CheckInternetState
End Sub
'
Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 5, 0)
    Application.OnTime earliesttime:=RunWhen, Procedure:=RunWhat, Schedule:=True
End Sub
'
Sub StopTimer()
    On Error Resume Next
    Application.OnTime earliesttime:=RunWhen, Procedure:=RunWhat, Schedule:=False
End Sub
'
Sub Auto_Close()
    Call StopTimer
End Sub
'
Sub CheckInternetState()
Dim RetVal As Long
Dim strConn As String * 255
    Range("A1") = Empty
    Range("B1") = "Checking ...."
    If TestInternetConnection = True Then
        Range("A1") = TestInternetConnection
        RetVal = InternetGetConnectedStateEx(RetVal, strConn, 254, 0)
        Range("B1") = strConn
        Call StopTimer
        Exit Sub
    End If
    Range("A1") = TestInternetConnection
    Range("B1") = "No connection !"
    Call StartTimer
End Sub
'
Function TestInternetConnection() As Boolean
    'Adapted from :
    'KPD-Team 2001
    'URL: http://www.allapi.net/
    If (InternetCheckConnection("http://www.allapi.net/", &H1, 0&) = 0) Then
        TestInternetConnection = False
    Else
        TestInternetConnection = True
    End If
End Function
 
Upvote 0
Wow, :biggrin:

Thankyou very much for that.
It's great!



Thanks again,

cheers
KJ

(i'm playing around/testing it out now, so if i have any questions, i'll be sure to ask :wink:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA Slowing Down as Macro Loops
Replies
4
Views
5K
6StringJazzer
6StringJazzer
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
399
AndyEd
A
A
  • Question
Issue Connecting to SQL DataBase with VBA
Replies
1
Views
3K
pjmorris
P
A
  • Question
First VBA Project - is there a better way to write this code?
Replies
0
Views
2K
akshay27
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A

Forum statistics

Threads
1,214,989
Messages
6,122,622
Members
449,093
Latest member
catterz66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top