Hello Gwyn,
The way things work with this function is that the function compiles a collection of all the file names as specified that are contained in the folder and sub folders. You then need to create your own sub routine to deal with those files. In the previous example sub, the For Each portion steps through the file names one at a time, it is only looking at the file name not "inside" the file. The part you are missing is opening each file to get the info you're after as the For Each cycles through the collection of file names.
Try this sub. I've used a different sub name to distinguish between them.
Code:
Sub Collect_Info()
Dim colFiles As New Collection
Dim vFile As Variant
RecursiveDir colFiles, "Y:\Syseng\Conventional Systems\Civil Systems\Ladder & Handrails Inspections 2013\Guardrails & Gratings", "*.xlsx", True
'Application.EnableEvents = False 'turn off events
'Application.ScreenUpdating = False 'turn off screen updating
For Each vFile In colFiles
'open the current vFile workbook
Workbooks.Open (vFile)
'select the sheet in vFile to retrieve info from
'Sheets("0609188").Range("D9").Select
Sheets("0609188").Select
with activesheet
.Cells(9, 4).Select
Selection.Copy
end with
'come back to this workbook
Windows("Master File - B.xlsm").Activate
'find the first available row in col B on sheet 1
Sheets("Sheet1").Select
With ActiveSheet
Cells(.Cells(.Rows.Count, "B").End(xlUp).Row + 1, 2).Select
'paste copied selection here
ActiveSheet.Paste
'just to move cursor to a neutal location
Cells(1, 1).Select
End With
'go back to vFile and close it
'need file name without the path
Windows(Dir(vFile)).Activate
Application.CutCopyMode = False
ActiveWorkbook.Close
Next vFile
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Create a break point at the For Each line and then use F8 to step through and see what is actually going on. If/when you are satisfied with things uncomment the enable.events and screenupdating to speed things up.
Good luck
NoS
Bookmarks