Len(Dir(fName)) not working

Zzyzzyth

Board Regular
Joined
Feb 19, 2002
Messages
145
I've seen dozens of examples using this line of code:
If Len(Dir(fName)) > 0 then 'file exists
but I can't get it to work. Is there a certain library reference that I need to include to make it work?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Are you sure you've specified the correct path?

I usually use something like this, but either way, If Dir(Fname) <> "" Then

If it's not a file but a directory, do something like this:

If Dir(Fname, vbDirectory) <> "" Then

If this doesn't help, please provide some more details about what you are trying to do.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi Zzyzzyth

Check the current directory. Maybe it's not correct:

Msgbox CurDir

HTH
PGC
 

Zzyzzyth

Board Regular
Joined
Feb 19, 2002
Messages
145
That's interesting - it returned MyDocuments, etc.....

In my code, I've tried:

ChDir ThisWorkBook.Path
fName = strInvoiceNum
If Len(Dir(fName)) > 0

as well as:
If Dir(fName) <> ""

When I step through the code and verify the path, it is showing the correct server path, why would your MsgBox CurDir get me MyDocuments? Or better yet, how do I fix it?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

What is the value of strInvoiceNum?
 

Zzyzzyth

Board Regular
Joined
Feb 19, 2002
Messages
145
It is generally a combo of CustCode, Week Num, and Sequence like:

"KSS 35 6"

I've tried it with and without & ".xls"
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870

ADVERTISEMENT

Hi again

Is the workbook and the MyDocuments folder in the same drive?

ChDir changes the current directory in the respective drive but does not change drive.

If you are not in the same drive you must use ChDrive

HTH
PGC
 

Zzyzzyth

Board Regular
Joined
Feb 19, 2002
Messages
145
No, the workbook is in a folder on our server. It is a template that does a save as once an invoice has been generated. That part works fine. It saves into the same folder as the template.

I was just trying to put in some bullet-proofing (or owner-proofing, if you know what I mean). If he tries to invoice something that has already been invoiced, I want to open it so that he can visually see that it has been done and has posted to the database.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
In that case don't use the default directory and specify the whole pathname.

Please try:
Code:
If Len(Dir(ThisWorkBook.Path & "\" & fName)) > 0
Hope it works
PGC
 

Zzyzzyth

Board Regular
Joined
Feb 19, 2002
Messages
145
I've actually tried that, too. Stepping through the code, it gives me the server path and the correct file name, but still says the file does not exist.

It's definitely there, I'm staring at it.

That's what made me ask if the is a Libr reference that I need. I'm stumped.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,862
Messages
5,544,727
Members
410,630
Latest member
JFORTH97
Top