XL2K: What's up with ChDir? Please help!

[Mike Mertes]

I'm having some trouble with ChDir that I've run across on several previous
occasions.

I'm trying to change the current directory to a shared directory on the
network using it's absolute path.

ChDir "\\Servername\Directory"

It doesn't work. The current directory remains the same.

This network resource is also mapped as "F:\" on most of the computers here
in the office. ChDir works fine if I refer to the network resource as a
drive letter.

ChDir "F:\Directory"

works just fine. However, I can't rely on the network resource being mapped
on all users computers as F, or even being mapped at all for that matter.

I have a feeling it has something to do with the current drive, as ChDir is
described as only changing the directory relative to each drive. That makes
sense. I tried to change the drive to the absolute path of the file server
but it appears ChDrive will only take literal drive letters as arguments and
not computer names or network addresses.

Also, I'm positive that the directory I'm pointing to in the absolute path
is correct as I can use the same string to open a workbook. (workbooks.open
"\\Servername\Directory\workbook.xls")

This is one of those occasions where your (plural,) personal experience can
help! You can't just read about these quirks in a the help file,
unfortuantely. What's with this unusual behaivor? Is it my syntax?

Thanks a lot!

 

[Mike Mertes]

I forgot to mention that ChDir is NOT returning any errors. It acts as if
the path I passed was valid, even though the curDirectory doesn't change. In
addition, this is true if I pass the path as a string or as a variable of
type string.

I found a post by Ron De Bruin on an Excel web forum where he suggests using
the windows API like this:

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

But, is this really necessary?

 

[Tom Ogilvy]

Yes, you have to use the api to do what you describe.

 

[Ron de Bruin]

Hi Mike

The API is working

Maybe you don't have to change the path.
You can fill a array with file names for example without changing the dir.
Then you can open the files in the array and do your stuff

Is that a option for you

 

[Mike Mertes]

Thank you, sir.

 

[Mike Mertes]

That's a good suggestion. But, it doesn't apply. Immediately after changing
the path I call Application.FindFile so the user can choose his file from
the directory I changed to.

Thanks anyway though, Ron. You've already been helpful I got the API
solution out of a previous post of yours. (Which does work.)

Thanks again, everyone.