My macro searches through a shared directory and creates a spreadsheet of all the files found during its search. There are numerous subfolders within the parent folder.
I'm looking for a way to be able to print out the path ending at the folder where the file was found. So far I have been only able to print the path with the file name included. Given that some of the files have rather large names this makes my spreadsheet look ugly. Is there anyway to print the path ending at the last folder instead of having it print with the file names appended?
Here's my code:
Sub CreatePOPList()
Dim filePath As Variant, fsObject As Variant, file As Variant
Dim i As Long
i = 2
'For my purposes Application.FileSearch was the easiest way to browse around my shared directory
With Application.FileSearch
.FileType = msoFileTypeAllFiles
'Search all subfolders in the parent folder
.SearchSubFolders = True
.LookIn = "\\Kowisa03\marketing\Retail\Category_Bible\"
.Execute
'This procedure goes through the .FoundFiles property one array element at a time (each element containing
'the path of a file) and finds the file: name, type, and date created
For Each filePath In .FoundFiles
i = i + 1
Set fsObject = CreateObject("Scripting.FileSystemObject")
Set file = fsObject.GetFile(filePath)
ActiveSheet.Cells(i, 1) = file.Name
ActiveSheet.Cells(i, 2) = file.Type
ActiveSheet.Cells(i, 3) = file.DateCreated
'While the following works for giving me the path it gives me the file name included in the path. I 'I would love to be able to set the cell to equal the path ending
'in the folder where the file was found as opposed to
'the path including the file name.
ActiveSheet.Cells(i, 4) = filePath
Next filePath
.NewSearch
End With
End Sub
I'm looking for a way to be able to print out the path ending at the folder where the file was found. So far I have been only able to print the path with the file name included. Given that some of the files have rather large names this makes my spreadsheet look ugly. Is there anyway to print the path ending at the last folder instead of having it print with the file names appended?
Here's my code:
Sub CreatePOPList()
Dim filePath As Variant, fsObject As Variant, file As Variant
Dim i As Long
i = 2
'For my purposes Application.FileSearch was the easiest way to browse around my shared directory
With Application.FileSearch
.FileType = msoFileTypeAllFiles
'Search all subfolders in the parent folder
.SearchSubFolders = True
.LookIn = "\\Kowisa03\marketing\Retail\Category_Bible\"
.Execute
'This procedure goes through the .FoundFiles property one array element at a time (each element containing
'the path of a file) and finds the file: name, type, and date created
For Each filePath In .FoundFiles
i = i + 1
Set fsObject = CreateObject("Scripting.FileSystemObject")
Set file = fsObject.GetFile(filePath)
ActiveSheet.Cells(i, 1) = file.Name
ActiveSheet.Cells(i, 2) = file.Type
ActiveSheet.Cells(i, 3) = file.DateCreated
'While the following works for giving me the path it gives me the file name included in the path. I 'I would love to be able to set the cell to equal the path ending
'in the folder where the file was found as opposed to
'the path including the file name.
ActiveSheet.Cells(i, 4) = filePath
Next filePath
.NewSearch
End With
End Sub
