subscript out of range error with 500+ files.

[Dean]

Thanks, in advance!

I am getting an error message - run time error 9 - subscript out of range
when I run this macro with about 26 folders and about 520 files. It works
fine with fewer folders/files. Can anyone tell what might be the problem?
When I hit debug, it is showing the following line in yellow:

arrFiles(dFileCount - 1) = .FoundFiles(dFileCount)


Public Sub GetFileList()
sPath = ActiveWorkbook.Path & "\"
sAppName = ActiveWorkbook.Name
If IsDim(arrFiles) = True Then arrFiles = Empty
If IsDim(arrData) = True Then arrData = Empty

Set FS = Application.FileSearch
With FS
.NewSearch
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim arrFiles(0)
For dFileCount = 1 To .FoundFiles.Count
GetFileName (.FoundFiles(dFileCount))
If sFileName <> ActiveWorkbook.Name Then
arrFiles(dFileCount - 1) = .FoundFiles(dFileCount)
ReDim Preserve arrFiles(UBound(arrFiles) + 1)
End If
Next
Else
MsgBox "No files found in " & sPath & " or its sub-folders."
End
End If
End With
If IsEmpty(arrFiles(UBound(arrFiles))) = True Then
ReDim Preserve arrFiles(UBound(arrFiles) - 1)
End If
End Sub

 

[Otto Moehrbach]

That particular error message usually means that you are asking Excel to
work with something that doesn't exist. For instance, your code may refer
to a sheet or workbook or folder by name when that item does not exist in
the particular location. HTH Otto