Copy a range from files in a folder/subfolder and paste in workbook 1

[jtaylor49]

---Background----
I am trying to make a database updating tool.
What I have is one workbook that is the database, with a button to update the database. The button pops up a userform and asks if you want to update the entire database, or just add single files. I am able to do the single file program, but I am having trouble with the entire database.... I want to be able to select multiple folders and copy data from any file in that folder or subfolder.

---Question----
Need vb help to program excel to open all files in a folder (or subfolder in that folder) copy a specified range of data, then go back to the original file and paste it to a specified point.


Regards,
JT

 

[Bong]

The filesearch object will return a collection (foundfiles) of files:

Using the FileSearch Object
Use the FileSearch property to return the FileSearch object. The following example searches for files and displays the number of files found and the name of each file.

With Application.FileSearch
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

The searchsubfolders property will allow you to include subfolders:

Example
This example searches the My Documents folder and all of its subfolders for all files whose names begin with "Cmd." The example also displays the name and location of each file that's found.

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.SearchSubFolders = True
.FileName = "cmd*"
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Then you can For Each ... Next through the FoundFiles collection to do what you want.

_________________
Bob Rashkin

 

[jtaylor49]

Thanks Bob, but the "For Each ... Next through the FoundFiles collection" is what is giving me the most trouble.

 

[jtaylor49]

NVM Bob, I have figured it out...

With fs
    .LookIn = "PATH"
    .SearchSubFolders = True
    .Filename = "DDH1*"
    
    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
         " file(s) found."
        For i = 1 To .FoundFiles.Count
           Workbooks.Open .FoundFiles(i)
               Sheets("Logging Info").Activate
    Range("C3:C11").Select
    Selection.Copy
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy

Application.DisplayAlerts = False
    ActiveWindow.Close
Application.DisplayAlerts = True



Windows("DDH.xls").Activate
    Sheets("unsorted").Select
    ActiveSheet.Paste
    [A1:A9].Select
    Selection.Copy


Sheets("DDH").Activate
Nextrow = Range("B999").End(xlUp).Row + 1
Range("B" & Nextrow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
            
        Next i
    Else
        MsgBox "There were no files found."
    End If
    
    
End With

Thanks again