I hope im on the right track. Im trying to compile data from multiple files in multiple sub folders within a directory. The code appears the hang up when trying to imbed the information into the "pull from ri" sheet. Not sure what im missing here.
VBA Code:
Sub sample()
Dim FileSystem As Object
Dim HostFolder As String
HostFolder = "H:\ri\"
Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)
End Sub
Sub DoFolder(Folder)
Dim SubFolder
For Each SubFolder In Folder.SubFolders
DoFolder SubFolder
Next
Dim File
Dim WSL As String
Dim LR As Long
For Each File In Folder.Files
' Operate on each file
Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
If InStr(File, ".xlsx") Then
Workbooks("pull from ri").Worksheets("Sheet1").Activate
Workbooks("pull from ri").Worksheets("sheet1").Cells(emptyRow, 1).text = File.Cells("c2").text
End If
Next
End Sub