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.

 

[hrlngrv]

I can't replicate this error. What's the actual pattern argument to Dir?

 

[xenou]

I've never seen this error either - DIR returns empty strings when I give it non-existent files. Maybe there's something very odd in your directory itself.

The FSO object is pretty flexible and reliable:

Dim FSO as Object '//FileSystemObject
Dim f as Object '//File Object

SET FSO = CreateObject("Scripting.FileSystemObject")
With FSO
    If .FolderExists("C:\myFolder") Then
        For each f in .GetFolder("C:\myFolder").Files
            Debug.Print f.Path
        Next f 
    End If
End With

 

[yky]

Thanks for the replies.

In my case, if the file resides on a network drive and the drive is not found, I get the error message "bad file name of number." The actual code is this:

if dir("\\vaio\flower\flower sales report", vbdirectory") <> "" then
...
elseif dir("\\lenovo\flower\flower sales report", vbdirectory" <> "" then
...

I have a laptop and a desktop. I need this code to work on both computers. If both computers are on and on the same network, the code works fine. If I'm away from the desktop or if I run the code from the desktop while the laptop is not turned on, I get the error message. (Just tested it again. When laptop was on, the code worked fine. I then turned the laptop off and got the error message.)

I searched google and found other people having encountered the same error message also when executing DIR.

 

[yky]

Just want to add that in the debug window, both "dir("\\vaio\flower\flower sales report", vbdirectory") <> """ and "iserror(dir("\\vaio\flower\flower sales report", vbdirectory") <> "")" show the exact same message: <bad file name or number>.

 

[xenou]

From the point of view of your code, I would just add a preliminary test:

If dir() = "bad file blah blah" then
   '//Don't try anymore
Else
   '//Do my Dir() thing

 

[yky]

Thanks for the suggestion.

I tried it and still got the same error message "bad file name or number." Looks to me when the error occurs Excel throws out the error message and stops there.

 

[xenou]

I'm not 100% sure that DIR works at all with unc file paths (it doesn't when I try using it on a local network share). I'm not an expert in this area - I typically use the FSO object for such things, so I can give that as an alternate solution (here I created a function that returns a collection with the file paths from the files in the folder):

[COLOR="Navy"]Sub[/COLOR] TestIt()
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] VBA.Collection
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

    [COLOR="Navy"]Set[/COLOR] col = GetFiles()
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] col [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] col.Count [COLOR="SeaGreen"]'//vba collections are 1-based[/COLOR]
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] col.Item(i)
        [COLOR="Navy"]Next[/COLOR] i
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


[COLOR="Navy"]Function[/COLOR] GetFiles() [COLOR="Navy"]As[/COLOR] VBA.Collection
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'FileSystemObject[/COLOR]
[COLOR="Navy"]Dim[/COLOR] fldr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'Folder[/COLOR]
[COLOR="Navy"]Dim[/COLOR] f [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'File[/COLOR]
    
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]With[/COLOR] FSO
        
        [COLOR="SeaGreen"]'//Get folder on network share[/COLOR]
        [COLOR="Navy"]If[/COLOR] .FolderExists("\\Corpfs\acctg\PPE") [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] fldr = .GetFolder("\\Corpfs\acctg\PPE")
        
        [COLOR="SeaGreen"]'//If unavailable then try local folder on X:[/COLOR]
        [COLOR="Navy"]ElseIf[/COLOR] .FolderExists("X:\PPE") [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] fldr = .GetFolder("X:\PPE")
        
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            
        [COLOR="SeaGreen"]'//Add file paths to collection object[/COLOR]
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] fldr [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] fldr.Files.Count > 0 [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] GetFiles = [COLOR="Navy"]New[/COLOR] VBA.Collection
                [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] f [COLOR="Navy"]In[/COLOR] fldr.Files
                    GetFiles.Add f.Path
                [COLOR="Navy"]Next[/COLOR] f
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

 

[hrlngrv]

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

 

[xenou]

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.