CODEWITHSUNDEEP
vbaexcel
Nick
Nick

Reputation: 3845

Excel VBA - Check if connected to internet AND if its via Wifi or Ethernet

I need to alert users before running a macro which takes a lot longer if run on Wifi as opposed to the company LAN.

I have found the following code:

Checking for internet connection using VBA

The following code snippet uses API functions to check Internet connectivity and also the type of connection :

Public Declare Function InternetGetConnectedState _
                         Lib "wininet.dll" (lpdwFlags As Long, _
                                            ByVal dwReserved As Long) As Boolean

Private Declare Function InternetGetConnectedStateEx Lib "wininet.dll" Alias "InternetGetConnectedStateExA" ( _
ByRef lpdwFlags As Long, _
ByVal lpszConnectionName As String, _
ByVal dwNameLen As Long, _
ByVal dwReserved As Long) As Long


'Local system uses a modem to connect to the Internet.
Private Const INTERNET_CONNECTION_MODEM As Long = &H1

'Local system uses a LAN to connect to the Internet.
Private Const INTERNET_CONNECTION_LAN As Long = &H2

'Local system uses a proxy server to connect to the Internet.
Private Const INTERNET_CONNECTION_PROXY As Long = &H4

The following API functions are used

Function IsConnected() As Boolean

    Dim Stat As Long

    IsConnected = (InternetGetConnectedState(Stat, 0&) <> 0)

    If IsConnected And INTERNET_CONNECTION_LAN Then
        MsgBox "Lan Connection"
    ElseIf IsConnected And INTERNET_CONNECTION_MODEM Then
        MsgBox "Modem Connection"
    ElseIf IsConnected And INTERNET_CONNECTION_PROXY Then
        MsgBox "Proxy"
    End If
End Function

If you want to know just if it is connected or not you can use the following:

CBool(InternetGetConnectedStateEx(0, vbNullString, 512, 0&))

This tells me I have a LAN connection, but it does so whether I am on WIFI or connected to the LAN. I need to distinguish between them.

Does anyone know how I can do this please?

Cheers

Upvotes: 2

Views: 6965

Answers (2)

rpmathur 12
rpmathur 12

Reputation: 55

Copy and paste this code in VBA and run checkinternet 


Private Declare PtrSafe Function InternetGetConnectedState _
   Lib "wininet.dll" (ByRef dwflags As Long, _
   ByVal dwReserved As Long) As Long
Private Const INTERNET_CONNECTION_MODEM As Long = &H1
Private Const INTERNET_CONNECTION_LAN As Long = &H2
Private Const INTERNET_CONNECTION_PROXY As Long = &H4
Private Const INTERNET_CONNECTION_OFFLINE As Long = &H20



Function IsInternetConnected() As Boolean
    Dim L As Long
    Dim R As Long
    R = InternetGetConnectedState(L, 0&)
    If R = 0 Then
        IsInternetConnected = False
    Else
        If R <= 4 Then
            IsInternetConnected = True
        Else
            IsInternetConnected = False
        End If
    End If
End Function

'You would call this in your code with something like
 Sub checkinternet()
    If IsInternetConnected() = True Then
        MsgBox "connected"
    Else
        MsgBox "not connected"
    End If
 End Sub

Upvotes: 0

KyloRen
KyloRen

Reputation: 2741

Try this, It loops through each connection and gets the ID , AdapterType and ConnectionStatus. Just use the one you need.

Dim oObject
Dim adapter
Dim item

Set oObject = GetObject("WINMGMTS:\\.\ROOT\cimv2")

Set adapter = oObject.InstancesOf("Win32_NetworkAdapter")

    For Each item In adapter

        If item.NetconnectionID <> "null" Then
         Debug.Print item.NetconnectionID
         Debug.Print item.AdapterType
         Debug.Print item.NetConnectionStatus
     End If
    Next

Thanks to to @tom preston, a link to Win32_NetworkAdapter class.

Upvotes: 4

Related Questions