VBA: how to catch DIR error

[yky]

I thought the DIR function would return a null string if it could not find the file or directory. In my case, it returns "bad file name or number."

How do I catch this error? I tried iserr(dir(....)) and iserror(dir(....)). Both still returned "bad file name or number".

My code looks like this:

if dir(....) <> "" then 'found the file
do something

If I can catch the error, I can modify the code to suit my need. Thanks.

 

[yky]

So Excel is raising an error, that is, displaying an error message in a dialog while suspending macro execution? If so, try error trapping.

On Error Resume Next
stringvariable = ""
stringvariable = Dir("\\a\long\unc\path", vbDirectory)
If Err.Number <> 0 Then
  'effectively invalid unc server, take whatever action you deem appropriate
  Err.Clear
End If
If stringvariable <> "" Then
  'found the directory, so do what you need to do
End If

Thanks. It works.

 

[yky]

I can't get Dir() to work with a UNC file path in VBA. Maybe VBA can't handle this job (?).

I.e., at the command prompt:
DIR \\Corpfs\ACCTG --> works

in VBA
Dir ("\\Corpfs\ACCTG") --> bad file name or number

So if the share exists but Dir can't find it in VBA then we can't use Dir() for this job.

Thanks for the reply. I'm running Excel 2010 on Windows 7. UNC file path works for me if, of course, the file and share exist.

 

[xenou]

I'm running Excel 2010 on Windows 7. UNC file path works for me if, of course, the file and share exist.

Do you mean it works for you in *VBA* with the DIR() command? Have you tried using the FSO library instead of Dir()?

 

[yky]

Yes. The dir() command works in VBA. I also tried you FSO method. It works also. Thank you.

 

[pbornemeier]

I get the "bad file name or number" error with:

Dir("\\Jupiter\Dataset4\", vbDirectory)

when that server is no longer on the local network.

It takes a few seconds for the error to come up, so I assume the dir command is waiting on a response from the DNS server.