3

I am working through an exercise that uses Dir() to find out whether a file exists in the current directory (i.e. the same directory as the workbook I'm using). The code given - and apparently working in the video example - is like this:

IsThere = (Dir("SomeFile.xlsx") <> "")

When I run this code, IsThere returns False. I can work around it by using:

IsThere = (Dir(ActiveWorkbook.Path & "\SomeFile.xlsx") <> "")

but I want to know why Dir isn't looking by default in the current directory as expected.

I'm struggling to find any relevant advice on this. Most of the examples I've found of how to use Dir() are with the file path specified so they don't really shed any light on my problem. The closest I've found is this (obsolete) MSDN reference which says that:

To run correctly, the Dir function requires the Read and PathDiscovery flags of FileIOPermission to be granted to the executing code.

Trouble is, I don't really understand the linked advice in there on how to set PathDiscovery to 1.

As for StackOverflow, this is probably the closest to my problem - although this uses a specified path, and I am not referencing a network location. I note that the answer to this question seems to presume that Dir() should work in the way expected i.e. with a simple filename and not a fully specified path.

Community
  • 1
  • 1
Tom Wagstaff
  • 1,443
  • 2
  • 13
  • 15
  • The answer here is if you're testing for files in a specific location then include the path in the call to `Dir()` - eg do not use (e.g.) `Dir("test.xlsx")` but use `Dir("C:\Folder\Folder2\test.xlsx")` – Tim Williams Dec 16 '16 at 22:59

2 Answers2

4

This has nothing to do with whatever the host application thinks it's directory is. You can always find out what directory Dir will default to by calling the CurDir function:

Debug.Print CurDir$

If you need to change it, call ChDir:

Debug.Print CurDir$
Debug.Print Dir$("*.*")
ChDir "C:\"
Debug.Print CurDir$
Debug.Print Dir$("*.*")

Think of it like sitting at a command prompt and typing dir or cd - it does exactly the same thing. Note that the current directory holds it's state between macro executions, so you can't rely on it being in some default location.

If you need a path relative to an open Workbook, use Workbook.Path if you need a path relative to the default path, use Application.Path. If you need to test for the existence of a file, don't use Dir at all - use the Scripting.FileSystemObject instead. You'll do yourself a huge favor if start thinking of the legacy file functions as deprecated.

Graham
  • 7,431
  • 18
  • 59
  • 84
Comintern
  • 21,855
  • 5
  • 33
  • 80
  • 1
    Regarding FSO - that's fine as long as your code never needs to run on a Mac, but there's really nothing wrong with `Dir()`: it is certainly not "legacy"... – Tim Williams Dec 16 '16 at 20:47
  • @TimWilliams - There's nothing *inherently* wrong with `Dir` once you get used to its idiosyncrasies, but the FSO is much more robust. Maybe "legacy" isn't the best term - I use it in the sense of "existed long, long before a much better option became available". – Comintern Dec 16 '16 at 21:01
  • Really clear, comprehensive answer - works like a charm - thank you! – Tom Wagstaff Dec 17 '16 at 17:33
  • Odd, because regardless of where I open a file from, `CurDir$` always returns the Excel Default File Location...until it is changed. Which seem to make sense as that is going to be the first value of it. And....changing the default file location changes `CurDir$` – Rdster Dec 19 '16 at 13:06
0

Check the Excel Options --> Save --> Default File location to see what your default is. It won't by default use where the file is located.

Rdster
  • 1,846
  • 1
  • 16
  • 30