ChDir won't work with UNC paths. But you can use a Windows API that will work
with either UNC paths or Mapped drives.

Here's an example I saved from a previous post:


And here's an example of that API call. It uses application.getopenfilename,
but you'll see how to use it.

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 testme01()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim Wkbk as workbook

myCurFolder = CurDir
myNewFolder = "\\share\folder1\folder2"

On Error Resume Next
ChDirNet myNewFolder
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDirNet myCurFolder

If myFileName = False Then
Exit Sub 'user hit cancel
End If

'do your stuff to open it and process it.
Set wkbk = workbooks.open(filename:=myfilename)

'....

End Sub

Conan Kelly wrote:

Hello all,

I have a some code that I use to alter a handful of personal files on a
local drive.

I'm trying to set this code up to store the current directory, change to a
specific folder on a local drive, then change the current directory back to
the one that was current before the code ran.

I just figured out that I will have to use both ChDir and ChDrive in order
to do this, and then use both to revert back to original directory before
running the code. No big deal, IF THE NETWORK SHARE IS MAPPED TO A DRIVE
LETTER!!! Most of the network locations that I work in are mapped to drive
letters. But on the rare occasion, I will be working in the network
location directly, ie: \\Server\Share.

It looks like changing CurDir from a network location to a local directory
is no big deal. But I'm having a hard time trying to figure out how to
change from a local to a network location.

If my original directory is...

\\Server\Share

...and I want to go to...

C:\Folder\Subfolder

...then my code would look something like this...

'CurDir would be "\\Server\Share" at this point.
pstrCurrFolder = CurDir
'ChDrive "C" 'it appears this line is not neccesary when going
from network to local
ChDir "C:\Folder\Subfolder"

But it doesn't look like I can revert back to my network location, after all
the processing, if it is not mapped to a drive letter.

ChDir pstrCurrFolder 'only works if pstrCurrFolder is another
location on the current drive.
ChDrive "\\Server" 'No workie!!!

Does anyone know of a way to accomplish what I'm trying to do?

Thanks for any help anyone can provide,

Conan Kelly

---------------------------
"Smokin' weed kills your brain cells. Drinkin' only screws up your
liver...ya got 2 a those."
- Earl Hickey (NBC's "My Name is Earl")

--

Dave Peterson