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
|
Similar Threads
