VBA fails to find a file

I have a VBA script used to process Word documents. The first thing the program does is to create an index of the documents in a defined set开发者_如何转开发 of folders. It then goes through the list processing each of the indexed documents.

The problem I am having is that it will sometimes decide that a particular document cannot be found, even though it previously indexed the document and a quick spot check shows the document to be in the correct place.

Can anyone shed some light on why VBA should display this behaviour?

The script is using the Dir$ function to index the files, and the Documents.Open function to open each word document for processing.

Sample code:

ChangeFileOpenDirectory (folderName)
inputFileName = Dir$(folderName & "*.doc")
Do While inputFileName <> ""
    ... call various functions here ...
    inputFileName = Dir$
Loop

One of the functions called in the block has the following line:

Set currentDoc = Documents.Open(fileName:=docFileName, AddToRecentFiles:=False, Visible:=False)

This is the point at which the code is failing.

One of the most annoying things I have found is that recent files links are returned as the files themselves with Dir. You can use the FileSystemObject to check the file type.

I copy/paste your code and it works correctly.

However, it leaves all the files open (and hidden), and when you run it in another directory, additional files are opened and added to the open projects (take a look in the VBA editor).

My only guess is that after a while you're hitting the maximum allowable number of open files.

Try adding

currentdoc.Close

just before

inputFileName = Dir$

A few reasons, some duplicated from other answers:

• If the path+ filename is long enough ... (you already answered in a comment)
• If you are writing new files to same directory, Dir$ may get corrupted (happened to me)
• If you have filenames with non-std chars (ex. "#")
• Files locked by other processes
• Files deleted while the macro is running

You may also try this code ...

Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then ....  

First enable the Microsoft Scripting reference in the VBE