VBA Dir function not working on Excel 2010

Question

I mapped an intranet location using the File Explorer. i.e. mapped http://intranet.XXXXXXX.com/mydir/ to M:\

I'm using the Dir function to test if a file is present in that location:

 Dim FileExists as Boolean

 FileExists = Dir("M:\myfile") <> ""

 If FileExists Then MsgBox "File found in M:"

I run that macro on Excel 2007 and it Works Fine. When I run it on Excel 2010 though, Dir("M:\myfile") always returns "", even if the file is present in the specified location. I can´t find a solution that will work on both Excel versions. Any ideas?

Answer 1

You may add file extension as a wildcard character at the end of filepath. I gave a try in excel 2010 and it worked for me.

  Dim FileExists As Boolean
    FileExists = Dir("D:\myfile" & "*.txt") <> ""

    If FileExists Then MsgBox "File found in M:"

Answer 2

I found that if I use the full network name, it works first go. This wasn't just in VBA, but also some shortcuts also - they returned "File could not be found".

Changing from the mapped shortcut, e.g.

Y:\Projects\Proj1\File1.xlsx

to the full mapped path, e.g.

\\server\Department\Projects\Proj1\File1.xlsx

Fixed the problem