OFF2000: Endless Loop When Macro Modifies Files in a Folder (254889)


The information in this article applies to:

  • Microsoft Office 2000 Premium
  • Microsoft Office 2000 Professional
  • Microsoft Office 2000 Standard
  • Microsoft Office 2000 Developer
  • Microsoft Office 2000 Small Business
  • Microsoft Excel 2000
  • Microsoft Outlook 2000
  • Microsoft PowerPoint 2000
  • Microsoft Word 2000
  • Microsoft Access 2000
  • Microsoft FrontPage 2000
This article was previously published under Q254889

SYMPTOMS

The following symptom occurs with Microsoft Visual Basic for Applications code that relies on the Dir function to return an empty string as the termination condition of a file-processing loop: The code runs endlessly if the files are modified while the loop is being run.

CAUSE

The Dir function is not returning a zero-length string ("") when called repeatedly against a changing list of files. The list of files in a folder will change if your macro modifies any of the files in the folder.

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

You can work around this behavior by avoiding the use of a zero-length string as the termination condition of the file-processing loop. As an example of this approach, follow these steps:
  1. If it does not already exist, create the folder C:\Temp\Testfiles.
  2. In that folder, create two Word documents, Temp1.doc and Temp2.doc.
  3. Start Microsoft Word and press ALT+F11 to start the Visual Basic Editor.
  4. On the Insert menu, click Module.
  5. In the new module, enter the following lines of code:
    Sub WorkAround()
   Dim i As Integer
   Dim docDoc As Document
   
   With Application.FileSearch
      .LookIn = "c:\temp\testfiles\"
      .FileName = "*.doc"
      .Execute
      For i = 1 To .FoundFiles.Count
         msgbox "File #" & i & " is " & .FoundFiles(i)
         'use the file here
         Set docDoc = Documents.Open(FileName:=.FoundFiles(i))
         'dirty the doc and force it to be saved
         docDoc.Range.InsertAfter "test"
         docDoc.Close (wdSaveChanges)
      Next i
   End With
End Sub
  6. Click anywhere in the following code line 
    Sub WorkAround()
    and then press F8 repeatedly to step through the procedure. Notice that the procedure terminates after processing the two files.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

The Dir function returns a string containing the name of the first file that it finds to match the pattern identified by its <pathname> argument.

The syntax of the Dir function is 
Dir[(pathname[, attributes])]
The pathname may include the wildcards * or ? to specify a collection of files. To get any additional file names that match pathname, call Dir again with no arguments. Provided that the collection of files is not being modified during the process, Dir returns a zero-length string ("") after the last matching file has been identified.

If files in the collection are modified while a loop listing them is taking place however, the modified files will reappear in the list, and the Dir function may never return a zero-length string. As a result, loops that rely on finding a zero-length string as their termination condition may iterate endlessly.

REFERENCES

For more information about the Visual Basic for Applications Dir function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type dir in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:9/12/2006
Keywords:kbbug kbfix KB254889
©2004 Microsoft Corporation. All rights reserved.