ChDrive/ChDir don't work with UNC paths.

But Ron's suggestion of the API will work with UNC or mapped drives.

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

Sub testme()

Dim mySavedPath As String
Dim FileToOpen As Variant

mySavedPath = CurDir

On Error Resume Next
ChDirNet "\\xxxxx-s2\documents\Marcotte"
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If

FileToOpen = Application.GetOpenFilename("Excel Files,*.xls")
ChDirNet mySavedPath

If FileToOpen = False Then
Exit Sub
End If

'do your stuff

End Sub


Marcotte A wrote:

Thanks Ron. I'm looking at that site right now. Any idea why this code
works for a folder on my machine, but not for one on a network?

myPath = ThisWorkbook.Path
ChDrive myPath '.......... <--- generates error
ChDir myPath

"ThisWorkbook" is in the correct folder on the network. I would think that
"ThisWorkbook.Path" would give me the correct string to pass to ChDrive.

"Ron de Bruin" wrote:

Hi Marcotte

See
http://www.rondebruin.nl/copy3.htm

Every second macro of each example will also work for a network path

--
Regards Ron de Bruin
http://www.rondebruin.nl


--

Dave Peterson