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?

look at [FileSystemObject](http://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba) instead. Way more intuitive than the silly `dir` function — Brad, Feb 28 '14 at 23:45
@Qbik's comment is not relevant for files but IS important for using the Dir() function to list files in directories -- on Windows, you need to include the trailing backslash (`Application.PathSeparator`), even though the trailing forward slash is optional on Mac. — Jamie Ciocco, Jul 13 '21 at 20:09

score 2 · Answer 1 · answered Mar 01 '14 at 01:12

2

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:"

answered Mar 01 '14 at 01:12

Santosh

12,175
4
41
72

Thanks Santosh. In fact instead of "myfile" I have the full name and extension of the file I'm trying to find. I does not work on that mapped unit M: = http://intranet.XXXX.com/mydir/ – user3366899 Mar 05 '14 at 14:57
Forgot to say that the original macro works for any other drive like C:\ or even mapped network units. – user3366899 Mar 05 '14 at 15:07
@user3366899 The dirve you are trying to access, Is it citrix enviornment ? – Santosh Mar 06 '14 at 06:39

score 2 · Answer 2 · answered Feb 27 '17 at 12:28

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

answered Feb 27 '17 at 12:28

Scott

161
5
13

This will not work with IP \\10.254.0.10\SomeDir type of maped servers. Neither on internet names \\server-name.com\SomeDir – Siyon DP Mar 13 '19 at 15:09

Wayne G. Dunn · Answer 3 · 2019-03-11T20:57:18.057

Here is how to use FSO to do what you want:

Option Explicit

Function test_it()
    'Test the Function - must pass the file path and name
    Debug.Print Does_File_Exist("C:\temp\form1.txt")
End Function

Private Function Does_File_Exist(sFullPath) As Boolean
' Will return True or False if file exists.
' Provide the fully qualified path and file name.
' You can disable the MsgBox displays after testing

Dim oFs         As New FileSystemObject
Dim oFile       As File

    Set oFs = New FileSystemObject
    If oFs.FileExists(sFullPath) Then
        Does_File_Exist = True
        MsgBox "Found file: " & sFullPath
    Else
        Does_File_Exist = False
        MsgBox "File not found: " & sFullPath
    End If

    Set oFs = Nothing
End Function

I'm a newbie in VBA for Excel. I copy-pasted your code in VBA and just changed the path. Then I created a macro to call the function. I get an error on the first line Option Compare Database ("expected Text or Binary"). Seems to me that your code is not for VBA for Excel, is it? — user3366899, Mar 05 '14 at 15:04

VBA Dir function not working on Excel 2010

3 Answers3

Linked