Excel VBA: Connection Testing

One of the problems I have to deal with on a regular basis is that of connecting, whether to SQL DWs, SFTP servers or whatever. The biggest problem with this is the fact that it takes Excel VBA coding 30 seconds to fail and only a few to work.Nevertheless, I guess the trick is getting a test in the first place, so coding for that is relevant. As a standard for connecting to a SQL Server ODBC set-up I use the following:

Sub TestConn()

Dim cnn as ADODB.Connection

Dim CanConnect as Boolean

Set cnn = new ADODB.Connection

On Error Resume Next

 

Applications.StatusBar = “Attempting to connect…”

cnn.ConnectionTimeout = 3

Cnn.Open “DSN=myDSN;UID=myUID;PWD=myPassword;”

If cnn.State = adStateOpen Then

CanConnect = True

cnn.Close

Else:

CanConnect = False

End If

Application.StatusBar = “”

MsgBox (“Connection: “ & CanConnect)

End Sub

It does the job, fast for a valid connection, slow for invalid. The only other thing to remember is to reference the Microsoft ActiveX Data Objects 2.x Library, ensuring it is the most recent (2.8 at time of writing this).

For those who want to test even the most basic connection I.e. internet connectivity then it requires something both more and less: a pair of functions for the test and a routine to call them e.g.

Option Explicit

Private Declare Function InternetGetConnectedState Lib “wininet” _

(ByRef dwflags as Long, ByVal, dwReserved as Long) As Long

 

Function IsNetConnected() As Boolean

IsNetConnected = InternetGetConnectedState(0&, 0&)

End Function

 

Sub NetCheckConnection()

Dim Msg As Variant

If IsNetConnected() Then

Call myRoutines ‘well, you surely want it to do something after you’ve successfully connected

Else:

Msg = msgbox(“No internet connection found.”, ,”Internet Connection Check”)

End If

End Sub

Applies to: Excel 2003, 2007. Not tested: Excel 2010

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s