Help please! Open file dialog to network path only if available

[farlice]

Hi all,

I am very new to macros and VBA in excel and am having difficulty with one particular thing. I have managed to get the open file dialog to point to a particular folder on a network drive (Q). What I want is for it to point there if the user is logged on, i.e. Q is available, but if not goto "C:\Documents and Settings".

I have looked all over various message boards for a solution and have tried to cobble something together, but have failed completely.

My original code was:

ChDrive "Q"
ChDir "Q:\Link Files\Housing\PR"

This works fine, but obviously produces an error if not logged into the network. So I tried:

With drive
On Error GoTo Cdrive
ChDrive "Q"
ChDir "Q:\Link Files\Housing\PR"
Cdrive:
ChDrive "C"
ChDir "C:\Documents and Settings"
End With

I know this probably looks ridiculous to people who know about VBA, but I thought I would give it a go - have tried a number of variations to no avail.

Any help appreciated.

Thanks

 

[Sektor]

If Dir("Q:\Link Files\Housing\PR") <> vbNullString Then
    ' Network drive is available
End If

 

[farlice]

Hi Sektor, thanks for very swift reply - I can see what your code does, but don't know how to fit it with my code (or if I need to make new code) in order for this to work.

I tried putting it in as follows:

If Dir("Q:\Link Files\Housing\PR") <> vbNullString Then
' Network drive is available
ChDrive "Q"
ChDir "Q:\Link Files\Housing\PR"
End If

But this returns the last folder viewed (Q is available to me at the moment, so presumably it should go there?)

Thanks