Link to home
Start Free TrialLog in
Avatar of jjxia2001
jjxia2001

asked on

VBA in Excel for the Network checking

I'm working on a project to export data from an Excel workbook to SAS Database.  Before exporting, I need check if there is internet connection.  I got the following VBA code from a co-worker, which is checking if our company's server for the company inside web page is available.  We found occasionally we got fails for the internet connection even from a PC that is wired to company's network.  Does that mean the company internet is not stable or the code below has some issues?  Any advices are very appreciated.  



Option Explicit
Public Const SERVER As String = "thstapw2-inside.prod.travp.net" '-- the current insidepage host (http://inside.here.travp.net/intraHome/index.aspx)


Public Function UserHasAccess() As Boolean
    Dim lngPing As Long
    Dim blnAnswer As Boolean

    On Error GoTo PROC_ERR
    '* check if user connected to Travelers Network
    lngPing = InNetwork(SERVER)
    If lngPing = 0 Then
        blnAnswer = True
        GoTo PROC_EXIT
    Else
        blnAnswer = False
    End If
    GoTo PROC_EXIT

PROC_ERR:
    If (Err.Number <> 0) Then
        Err.Clear
    End If
    blnAnswer = False

PROC_EXIT:
    UserHasAccess = blnAnswer
End Function


Private Function InNetwork(ByVal strServer As String, Optional ByVal lngPings As Long, Optional ByVal lngTimeOut As Long) As Long
    Dim lngResult As Long
    Dim objWS As Object

    On Error GoTo PROC_ERR
    If lngPings = 0 Then lngPings = 1
    If lngTimeOut = 0 Then lngTimeOut = 250
    Set objWS = CreateObject("WScript.Shell")
    With objWS
      lngResult = .Run("%comspec% /c ping.exe -n " & lngPings & " -w " & lngTimeOut _
           & " " & strServer & " | find ""TTL="" > nul 2>&1", 0, True)
    End With
    GoTo PROC_EXIT

PROC_ERR:
    If (Err.Number <> 0) Then
        Err.Clear
        lngResult = -1
    End If

PROC_EXIT:
    Set objWS = Nothing
    InNetwork = lngResult
End Function
ASKER CERTIFIED SOLUTION
Avatar of regmigrant
regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial