Need help modifying some FileSearch code please

[largeselection]

Hi,

I'm a novice so please bear with me...

I'm trying to modify some code from RDB to copy a range (one row, ~40 columns) from many workbooks (500+) into one.

I just found out that the application.FileSearch which he uses has since been discontinued from Excel 2007+. I looked up a number of the replacements that people have proposed, but a lot of them are using dir and objFSO and propose methods of counting files in a folder, but not counting them and then referencing them.

Here is the code I would like to use, but can't because of that discontinued FileSearch. If you have any insight or ideas as to how I can adjust it to be able to both count the # of files and then be able to loop through the routine to copy the same range from every file I'd appreciate it.

Sub CopyRangeValues()
    Dim basebook As Workbook
    Dim mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    Dim rnum As Long
    Dim i As Long
    Dim a As Long
    Dim MemID As Integer
    Dim sourceRange2 As Range
    Dim destrange2 As Range
    
    MemID = Worksheets("START").Range("C12").Value + 1
    
    Application.ScreenUpdating = False
    With Application.FileSearch
        .NewSearch
        .LookIn = \\Directors\Data\Div 25
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
            Set basebook = ThisWorkbook
            rnum = 8
            For i = 1 To .FoundFiles.Count
                Set mybook = Workbooks.Open(.FoundFiles(i))
                Set sourceRange = mybook.Worksheets(1).Range("E" & MemID & ":AQ" & MemID & "")
                a = sourceRange.Rows.Count
                With sourceRange
                    Set destrange = basebook.Worksheets("DATA").Cells(rnum, 2). _
                                    Resize(.Rows.Count, .Columns.Count)
                End With
                destrange.Value = sourceRange.Value
                Set sourceRange2 = mybook.Worksheets(1).Range("B3")
                With sourceRange2
                    Set destrange2 = basebook.Worksheets("DATA").Cells(rnum, 1). _
                                    Resize(.Rows.Count, .Columns.Count)
                End With
                destrange2.Value = sourceRange2.Value
                mybook.Close
                rnum = i * a + 1
            Next i
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[largeselection]

If need be, I can also list all of the files rather than looping through all the files in a folder. I was trying to avoid that since I thought it might slow it down to have it have to reference each individual file by name rather than "For i = 1 to .FoundFiles.Count".

Not sure if that's helpful, but maybe I could create a range or something which has a list of all the filenames in the folder and then have the original code loop through that range?

 

[largeselection]

I found something on the Microsoft site that helped out. It actually did what I was thinking of doing by creating an array based on the files in that folder and then looping through that.

Thanks for looking!

 

[largeselection]

So it works...

Any suggestions on how to get it to work faster? I realize that it is time consuming to open every workbook and do the routine so I'm just wondering if there is a more efficient method.

I ran a test with 19 workbooks and it successfully completed in 75 seconds.

The issue is that I have to run it for 750 workbooks (perhaps more than once).

So 19 Workbooks in 75 seconds = 750 workbooks in 50 minutes.

I understand that is considerably faster and easier than if I were to try and do it myself by hand so I'm completely appreciative that it will even do it, but I figured I'd ask the question to see if anyone knows of a more efficient way to complete the task.

Thanks!


Maybe it would be faster to make a macro which instead of copying/pasting, adds formulas which reference the closed workbooks?

So rather than going to the first workbook, opening it, copying range(E5:AQ5) into the Main Workbook, and then closing the first workbook, then going ot the second workbook and doing the same, and the third, etc, Maybe the code could just add a formula to the Main Workbook that is like:

'Range in Main Workbook for 1 Cell
Dim FPath as String
Dim MemID as Integer
MemID = The Member ID# that I specify in a cell
FPath = String where Folder is
='" & Fpath & "[" & First Workbook in Array created from Folder I specify & "]Sheet1'!$B$" & MemID & "

Would something like that work? I feel like that would probably be faster since then it wouldn't have to open the files