title: Excel VBA: Getting File Status on a Network Path is Inconsistent
body: <p>I'm trying to fix an intermittent bug in an Excel VBA macro that I've boiled down to being due to Excel returning inconsistent error codes when running the Open command on a file on a network drive.</p>
  
  <p>The GEN_File_Status function below returns an integer code that my other code uses to determine the status of a file (passed as the fName argument). Some error codes (e.g., 52 &amp; 53) lead to the same value being returned, as my calling code does not need to distinguish between these. fName is expected to contain the full file path and filename.</p>
  
  <pre><code>Public Function GEN_File_Status(ByVal fName As String) As Integer
  
    Dim fileInd As Long
  
    On Error GoTo errHandle
    fileInd = FreeFile()
    Open fName For Input Lock Read As #fileInd
    Close fileInd
    On Error GoTo 0
  
    GEN_File_Status = 0       ' File exists and is unopened
    Exit Function
  
  errHandle:
    Select Case Err.Number
      Case 52                 ' Bad filename or number
        GEN_File_Status = -1
      Case 53                 ' File not found
        GEN_File_Status = -1
      Case 70                 ' Permission denied (file may already be open)
        GEN_File_Status = 1
      Case 76                 ' Path not found
        GEN_File_Status = -2
      Case Else               ' Unknown error code
        GEN_File_Status = -2
    End Select
  End Function
  </code></pre>
  
  <p>This works fine if I'm running on a local drive, but when I run on a network drive I occasionally get crashes later in the calling code apparently due to Excel 'giving up' too quickly on establishing whether the filepath does not exist, or whether it just wasn't able to be determined over the network. This failure does not require the network to be all that slow by 'human' standards - based on the code execution time Excel must be giving up fairly quickly.</p>
  
  <p>To troubleshoot, I deliberately used an invalid path on the network in the fName argument, to see what error code Excel would return. Sure enough, I'm getting Err.Number = 76 (Path is not found) most of the time, but also getting Err.Number = 53 (File is not found) some of the time as well, which leads to problems in my calling code. Obviously both error codes are correct in some sense, but if alternatively I use an invalid path on my local C drive, I consistently get '76' error codes. So clearly the 'path not found' 76 error takes precedence, and I need a way to consistently determine this.</p>
  
  <p>Is there any way to tell Excel to more or less "slow down and get the error code right"? I tried inserting DoEvents before this to at least get the processor running optimally but this didn't help, so it does seem to be a network issue. Ultimately, using this or some other approach, I need a way to consistently distunguish between the following situations on a network drive:</p>
  
  <ol>
  <li><p>File exists already</p></li>
  <li><p>File does not exist, but the file path exists</p></li>
  <li><p>File path does not exist</p></li>
  </ol>
  
  <p>Any help would be appreciated, thanks.</p>