FOUNDFILES.COUNT RETAINS OLD VALUE

tyronemeister

New Member
Joined
Jun 30, 2006
Messages
28
The following code imports all files from a directory and appends them to a single workbook. It works without problems once, but gives an error after that if the number of files changes in the directory. It retains the count value for the initial directory count.

Does this value need to be cleared at some point or does foundfiles.count need to be initialized at zero?

Thanks.

Code:
Sub ImportDirectoryFiles()
Dim ImportWB As Workbook
Dim MasterWB As Workbook
Dim i As Integer

Cells.Clear
Application.ScreenUpdating = False
Set MasterWB = ActiveWorkbook

With fs
    .NewSearch
    .LookIn = "C:\test2\"
    .Filename = "*.CSV"
     .SearchSubFolders = False
    .Execute

If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & "file(s) found."
For i = 1 To .FoundFiles.Count

Set ImportWB = Workbooks.Open(.FoundFiles(i))
ImportWB.Sheets(1).UsedRange.Copy MasterWB.Sheets(1).Range("A65536").End(xlUp).Offset(1)
ImportWB.Close False

Next i
Else
MsgBox "There were no files found."
End If
End With
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Watch MrExcel Video

Forum statistics

Threads
1,113,823
Messages
5,544,538
Members
410,619
Latest member
gregor222
Top