Runtime Error 67: “Too many files,” explained and fixed

Error 67, “Too many files”, means the program asked for another open file when none could be granted: VBA's file numbers are a finite pool (1 to 511), and the operating system caps open handles per process as well. In living code this is nearly always a leak: files opened inside a loop, a search, or a per-record routine that are never closed, accumulating until the pool runs dry. The input data is rarely “too many”; the closes are too few.

For people who just run the program

  1. Expect it late in a big job. Error 67 arrives after hundreds of files have been processed, not on the first one. The size of the batch is part of the pattern.
  2. Restart the application to clear it. Restarting releases every leaked handle at once, which is why the job then runs fine for a while. That relief is diagnostic, not a solution.
  3. Run the batch in halves if a deadline looms. Splitting the work keeps each session under the leak's accumulation point while the maintainer fixes the code.
  4. Report roughly how far the job got. “Fails around file 400” measures the leak rate and helps confirm the fix later.

For people who write the code

  1. Close in the loop, not after it. Every Open inside an iteration needs its Close in the same iteration, on the error path as well as the success path:
    2. For eachPath In paths
    fn = FreeFile
    Open eachPath For Input As #fn
    ' ... read ...
    Close #fn          ' inside the loop: outside, it leaks 510 times
Next
  2. Use Reset as a diagnostic, not a cure. Reset closes all open files; if adding it “fixes” the error, it proves a leak exists upstream. Find the unpaired Open rather than sprinkling Reset.
  3. Check objects that open files invisibly. Workbooks, recordsets, and loaded controls all hold handles. A per-record routine that opens a workbook and relies on garbage collection to close it will leak under loop conditions; close explicitly.
FreeFile returning a number does not mean the pool is healthy. FreeFile hands out the next free number; the error arrives when there is none. Instrument the code to count currently open files during a long run and watch the count climb: a flat count is correct, a staircase is the leak.

If it still happens

If opens and closes pair up correctly and error 67 still fires, look outside the file statements: thousands of files in a single directory can fail some operations on some systems, and the Dir() function itself, left mid-enumeration with a pattern, holds state. Test the same code against a smaller directory to separate pool exhaustion from directory scale.

Common questions

How many files can VBA have open at once? File numbers run from 1 to 511, so at most 511 files through VBA file statements, and the operating system may impose its own per-process limit first. Either ceiling produces error 67.

Why does restarting fix error 67 temporarily? Because every open handle dies with the process. The leak restarts from zero, so the job runs until the leaked files accumulate to the limit again.

Related errors

Runtime Error 55 · Runtime Error 61 · Runtime Error 7