Excel/VBA - Abort script if network connection doe

Question

Are there any VBA code to look for a present internet connection?

I have a code that will run on a timer. This code will open a file on a local network share drive. I am looking for some type of On Error Goto ErrorMessage code if it tries to open the file when the network isn't connected.

Answer 1

You can check the Len of Dir on the shared drive you're trying to get to:

Option Explicit
Sub TestForNetworkDrive()

'suppose the file we want is at Z:\cool\vba\files\file.txt
If Len(Dir("Z:\cool\vba\files\file.txt")) = 0 Then
    'handle network not available issue
    MsgBox ("Network share not found...")
Else
    'do timer-based code here
    MsgBox ("Let's get to work!")
End If

End Sub

Answer 2

This function works on both Mac and PC, with 32 as well as 64 bit Excel versions.

Declaration:

Option Explicit
#If VBA7 And Win64 Then
    Private Declare PtrSafe Function InternetGetConnectedStateEx Lib "wininet.dll" (ByRef lpdwFlags As Long, ByVal lpszConnectionName As String, ByVal dwNameLen As Integer, ByVal dwReserved As Long) As Long
#Else
    Private Declare Function InternetGetConnectedStateEx Lib "wininet.dll" (ByRef lpdwFlags As Long, ByVal lpszConnectionName As String, ByVal dwNameLen As Integer, ByVal dwReserved As Long) As Long
#End If

Function:

Function IsInternetConnected() As Boolean
    Dim strConnType As String, lngReturnStatus As Long, MyScript As String

    If Application.OperatingSystem Like "*Macintosh*" Then
        MyScript = "repeat with i from 1 to 2" & vbNewLine
        MyScript = MyScript & "try" & vbNewLine
        MyScript = MyScript & "do shell script ""ping -o -t 2 www.apple.com""" & vbNewLine
        MyScript = MyScript & "set mystatus to 1" & vbNewLine
        MyScript = MyScript & "exit repeat" & vbNewLine
        MyScript = MyScript & "on error" & vbNewLine
        MyScript = MyScript & "If i = 2 Then set mystatus to 0" & vbNewLine
        MyScript = MyScript & "end try" & vbNewLine
        MyScript = MyScript & "end repeat" & vbNewLine
        MyScript = MyScript & "return mystatus"
        If MacScript(MyScript) Then IsInternetConnected = True
    Else
        lngReturnStatus = InternetGetConnectedStateEx(lngReturnStatus, strConnType, 254, 0)
        If lngReturnStatus = 1 Then IsInternetConnected = True
    End If
End Function

Using the function in a Sub:

If IsInternetConnected Then
   MsgBox"Network Connection Detected"
Else
   MsgBox"No Network Connection Detected"
End If

Answer 3

I usually use the code below to determine if a network connection exits. The function returns true or false.

Declaration:

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

Function:

Public Function IsInternetConnected() As Boolean
Dim strConnType As String
Dim lngReturnStatus As Long
IsInternetConnected = False
lngReturnStatus = InternetGetConnectedStateEx(lngReturnStatus, strConnType, 254, 0)
If lngReturnStatus = 1 Then IsInternetConnected = True
End Function

Using the function in a Sub:

 If IsInternetConnected = False Then
    output = MsgBox("No Network Connection Detected!", vbExclamation, "No Connection")
Else
    Do stuff that requires the network connection
End If