khalid79m
11-05-2008, 04:48 AM
I currently have this macro in place
Dim oFSO As Object
Dim NextRow As Long
Public Sub LoopFolders()
Application.DisplayAlerts = False
Sheets("NonReturnsB1").Delete
Sheets.Add.Name = "NonReturnsB1"
Sheets("NonReturnsB1").Select
Application.DisplayAlerts = True
Set oFSO = CreateObject("Scripting.FileSystemObject")
selectFiles "" & Worksheets("Control_Panel").Range("C14").Value
Set oFSO = Nothing
End Sub
Private Sub selectFiles(sPath)
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr
Set Folder = oFSO.GetFolder(sPath)
For Each fldr In Folder.SubFolders
selectFiles fldr.Path
Next fldr
For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
NextRow = NextRow + 1
ActiveSheet.Cells(NextRow, "A").Value = file.Name
End If
Next file
End Sub
However this only returns the file name, I need the entire path ? Can anyone help
Dim oFSO As Object
Dim NextRow As Long
Public Sub LoopFolders()
Application.DisplayAlerts = False
Sheets("NonReturnsB1").Delete
Sheets.Add.Name = "NonReturnsB1"
Sheets("NonReturnsB1").Select
Application.DisplayAlerts = True
Set oFSO = CreateObject("Scripting.FileSystemObject")
selectFiles "" & Worksheets("Control_Panel").Range("C14").Value
Set oFSO = Nothing
End Sub
Private Sub selectFiles(sPath)
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr
Set Folder = oFSO.GetFolder(sPath)
For Each fldr In Folder.SubFolders
selectFiles fldr.Path
Next fldr
For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
NextRow = NextRow + 1
ActiveSheet.Cells(NextRow, "A").Value = file.Name
End If
Next file
End Sub
However this only returns the file name, I need the entire path ? Can anyone help