CODEWITHSUNDEEP
vbams-access
Kaja
Kaja

Reputation: 3057

Checking network connection using vba

Is there any way to check NEtwork connection in vba?

I am using this command:

If Dir("O:\") = "" Then
    MsgBox "you have network connection"
Else
    MsgBox "No Connection"
End If

but it doesnt work and I am getting a run time error

Upvotes: 4

Views: 23106

Answers (6)

Sascha
Sascha

Reputation: 1

I want to give an another spin on this as it is the issue.
I use Scripting.FileSystemObject!
The will check is a folder exist and do not require an error handling.
It works with network drive mapped and network folder.
Just will not detect if a network computer is their for example :"\server"
But "\server\folder" or "Y:" is working.

Dim Serverfolder As String
Serverfolder = "O:\"
Dim fdObj As Object
Set fdObj = CreateObject("Scripting.FileSystemObject")
If fdObj.FolderExists(Serverfolder) = False Then '= False is not needed!
'folder do not exists
else
'Folder exists
End if

Upvotes: 0

Brian
Brian

Reputation: 66

This is similar to St3ve's answer, but in function form that returns True if the drive is connected, and False if not.

Function TestNetwork() As Boolean
On Error Resume Next 'ignore errors

If Len(Dir("O:\", vbDirectory)) = 0 Then
    TestNetwork = False
Else
    TestNetwork = True
End If

On Error GoTo 0 'reset error handling
End Function

I found that the Len(Dir("O:\")) method works for external drives, like a flash disk, but didn't work for a mapped network drive. The function works around this with On Error Resume Next, so if O:\ is a disconnected mapped drive, the system hides the Error 52 and goes to the TestNetwork = False line.

Call the function in your code like this:

If TestNetwork() = True Then
    'Code if connected
Else
    'Code if not connected
End If

You can generalize this code to test different directories by naming the function Function TestNetwork(DirAddress As String) As Boolean and replace "O:\" with DirAddress. Then use TestNetwork("O:\"), or any other directory address in quotes when you call it.

Upvotes: 2

St3ve
St3ve

Reputation: 135

I know this is a very old question and that my answer is not super clean (cause it uses a label), but I find it really simple and reliable.

Instead of using DLL files I just wanted to let the code run past the 52 runtime error, so I used a 'on error goto' and a label.

This way if the folder is not available, you don't get the error message (which was unacceptable for me, since I needed others to use the macros comfortably), the code just falls into the IF statement, where I thought it would go if the len function returned a 0.

On Error GoTo len_didnt_work 'this on error handler allow you to get past the 52 runtime error and treat the situation the way you want, I decided to go with a msgbox and to stop the whole sub

If Len(Dir("O:\Test\**", vbDirectory)) = 0 Then 'this is the test others have proposed, which works great as long as the folder _is_ available. If it is not I'd always just get the 52 runtime error
len_didnt_work: 'this is the label I decided to use to move forward in the code without the runtime 52 error, but it is placed inside the IF statement, it just aids it to work 'properly' (as I'd expect it to)
    MsgBox "Sorry, your folder is not available",vbcritical 'msgbox to notify the user
    On Error GoTo 0 'reset error handling
    Exit Sub 'end sub, since I wanted to use files from my "O:\Test\" folder
End If 
On Error GoTo 0`'reset error handling in case the folder _was_ available

Upvotes: 1

grego211
grego211

Reputation: 11

I don't know if your problem is solved. Anyway I had a similar issue using Excel VBA. Having a diskstation on my network, I mapped a shared folder of this station as a network folder in Windows, using letter M. Generally, after starting my Windows, and of course diskstation is up and running, the network drive shows in Windows Explorer, but it has a red cross (not connected) instead of the icon with some green color (connected). Only after I manually click this network location in Explorer it becomes green. I first expected the connection could also be established via my Excel VBA programs, when issuing the first time a statement like Dir("M:\abc"). However there is no result, and the icon remains red. I always needed first to click manually in Explorer. Finally I found a solution in VBA, using prior to the Dir a dummy "shellexecute ... explore M: ...", making the network drive automatically connected.

Declare Function ShellExecute Lib "shell32.dll" Alias _
  "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation _
  As String, ByVal lpFile As String, ByVal lpParameters _
  As String, ByVal lpDirectory As String, ByVal nShowCmd _
  As Long) As Long
'...
Dim RetVal As Long
Dim hwnd As Long
RetVal = ShellExecute (hwnd, "explore", "M:", vbNullString, vbNullString, SW_SHOWNOACTIVATE)
'...
a = Dir("M:\abc")

Upvotes: 1

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

What you are doing is almost correct except flip the if and else parts,

i.e. when Dir("O:\") = "" = You are not connected

and when it returns something means you have a connection.

The Dir function is used to return the first filename from a specified directory, and list of attributes.

Sub Test_Connection()

 If (Len(Dir("O:\"))) Then
  MsgBox "Connected"
 Else
  MsgBox "No Connection"
 End If

End Sub

Upvotes: 5

CoveGeek
CoveGeek

Reputation: 435

I tested the solution from this link in Access 2007 VBA.

http://www.vbaexpress.com/forum/showthread.php?42466-Pinging-IP-addresses-in-Access-2007

It works as a function call that can be used anywhere in your VBA code to detect the availibility of a network resource by name or IP and reuturn a boolean value as the result.

Upvotes: 1

Related Questions