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.

Improve this question
1
  • 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") Commented Dec 16, 2016 at 22:59

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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"...
@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".
Really clear, comprehensive answer - works like a charm - thank you!
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$
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.