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?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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,884
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,884

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,884
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.
 

Forum statistics

Threads
1,140,944
Messages
5,703,305
Members
421,290
Latest member
james90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top