Excel VBA - link by LAN problem

[gerok]

I have a macro running a loop every one minute updating links fro
another workbook which is saved on another network disk. This work
just fine, except from certain instances (2-3 times pr day?). Then
get an error message that the macro can't find the other file on th
network disk. I suspect that this is due to some temporary (and brief
outage of the network (LAN).

To solve this, I want the macro to do a test whether the network i
available before the updating runs. If its not available, the macr
should just wait another minute - and the network would probably b
available again (if not: just wait another minute, and so on).
Any suggestions for a network test?

Thanks in advance!

Georg Rokn

 

[Nikos Yannacopoulos]

Georg,

You could try something like:

Sub check_network()
On Error GoTo network_down
ChDir "K:\"
On Error GoTo 0
If ActiveWorkbook.Path <> "" Then
ChDir ActiveWorkbook.Path
Else
ChDir Application.Path
End If
'CODE TO DO WHAT YOU WANT
Exit Sub

network_down:
MsgBox "the network is down"
'Replace MsgBox with CODE TO REPEAT CHECK

End Sub

HTH,
Nikos

 

[Nikos Yannacopoulos]

Georg,

You could try something like:

Sub check_network()
On Error GoTo network_down
ChDir "K:\"
On Error GoTo 0
If ActiveWorkbook.Path <> "" Then
ChDir ActiveWorkbook.Path
Else
ChDir Application.Path
End If
'CODE TO DO WHAT YOU WANT
Exit Sub

network_down:
MsgBox "the network is down"
'Replace MsgBox with CODE TO REPEAT CHECK

End Sub

HTH,
Nikos