PathName

[marylu]

I am currently writing a macro off a mapped server. However, the
pathname that I am using gives me an error.

Run-time error '1004';
'file name' could not be found. Check the spelling of the file name,
and verify that the file location is correct.

my code is as follows...
MyPath = ActiveWorkbook.Path
ChDir MyPath
MyFile = Dir(MyPath + "\*.xls")
'MsgBox "The File is " & MyFile

Do While MyFile <> ""

While (MyFile = "MasterList.xls")
MyFile = Dir
Wend

Workbooks.Open Filename:=MyFile
Sheets("Info").Select

....... and it continues on.


This code works fine as long as its in my local directories, such as in
C: or the desktop. However, if i run off the mapped drive it won't
work. Please help.

 

[Dave Peterson]

Just a comment first:

VBA is pretty forgiving, but you may want to change:
MyFile = Dir(MyPath + "\*.xls")
to
MyFile = Dir(MyPath & "\*.xls")
(+ usually means Add (numbers), while & means concatenate (strings))

Still not an answer to your question, but if you really want to change
directories, maybe you should do:
ChDrive mypath
chdir mypath

(just in case the drive changes).

And from the snippet of code you posted, it doesn't look like you need to change
drives or folders.

But if you want to change to a UNC drive, you'll have to use a windows API
function:

here's a post from Rob Bovey/Tom Ogilvy:

========

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

Example of usage

Sub GetFile()
On Error GoTo ErrHandler
ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp"
Exit sub
ErrHandler:
MsgBox "Couldn't set path"
End Sub

Use like ChDir and ChDrive combined.

 

[Hank Scorpio]

I am currently writing a macro off a mapped server. However, the
pathname that I am using gives me an error.

Run-time error '1004';
'file name' could not be found. Check the spelling of the file name,
and verify that the file location is correct.

my code is as follows...
MyPath = ActiveWorkbook.Path

I agree with Dave about changing the + to an & on the second line
below, but I think your problem is a touch simpler than it may first
appear. I know quite a few people who have been caught by it, myself
included once.

ChDir MyPath
MyFile = Dir(MyPath + "\*.xls")

The problem is a slight misunderstanding of what ChDir does. It
changes the current directory of the specified device (drive). It does
NOT, however, change the current device itself. Thus if the file is
located in (say) U:\MyStuff, ChDir is saying that it wants the default
directory for the U:\ drive changed to MyStuff. However if your own
current directory is presently, say, H:\MyLocalStuff, it will NOT
change your current device from the H:\ drive to the U:\ drive. For
that you need to use the ChDrive command. Try adding this in front of
the ChDir statement:

ChDrive Left(MyPath, 1)

This is why it worked on your C:\ drive. There was only a directory
change involved, not a device (drive) change.

If you want to see this in action, you can also add the following line
in front of the Workbooks.Open method (before you add the line above
to your code):

MsgBox CurDir

Actually now that I look, on-line help for the ChDir command covers
this more eloquently than I have:
----------------------------------------
The ChDir statement changes the default directory but not the default
drive. For example, if the default drive is C, the following statement
changes the default directory on drive D, but C remains the default
drive:

ChDir "D:\TMP"
---------------------------------------

 

[Dave Peterson]

I now see that the OP said mapped drive--so the UNC code is nice, but useless
<vbg>.

Still not an answer to your question, but if you really want to change
directories, maybe you should do:
ChDrive mypath
chdir mypath

VBA is very forgiving. You don't actually need: ChDrive Left(MyPath, 1)

 

[Hank Scorpio]

I now see that the OP said mapped drive--so the UNC code is nice, but useless
<vbg>.

But I should get partial credit for my second suggestion <vvbg>:

VBA is very forgiving. You don't actually need: ChDrive Left(MyPath, 1)

Ooops, I misread this; I thought your "seccond suggestion" was that
you don't need the ChDrive statement, and typed a couple of paragraphs
about why it IS necessary; then I Read For Content and got your point.
You don't need the Left() function but can assign myPath directly as
the argument to ChDrive. Quite so, quite so. But hey, I'm sometimes a
neat-freak with my code. 8^>

Accordingly, you get an A+ for effort. 8^>

 

[Dave Peterson]

Woohoo.

(But no extra credit for the unnecessary UNC stuff???)

<GD&R>