File path listing and analysis

[DaveyC]

Hi

I have two main folders with many sub-folders and files, one has to be
combined into the other ensuring that the most up to date file is adopted
whenever appropriate. In order to do this I thought the best way would be to
down load or transfer complete filepaths and certain file properties (last
updated date) into Excel and analyse accordingly.

The structure of the two main folders differ.

Does anyone know how to transfer filepath lists and file properties to Excel?

I have looked at other posting son this subject and they seem to fall short
with the filepath and file properties requirements.

Any advice much appreciated
Dave

 

[Bob Phillips]

Have you looked at using FileSystemObject to get those properties

Sub LoopFolders()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("C:\Folder1")

For Each file In Folder.Files
Debug.Print file.Path
Debug.Print file.DatelastModified
Next file

Set file = Nothing
Set Folder = Nothing
Set oFSO = Nothing
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[DaveyC]

Hi Bob

Apologies for not giving any feedback sooner, but Africa isn't great for
comms in places. I started looking at the FileSystemObject you suggested and
could not get it to work. I tried down an alternate route using the
FileSearch application and thought I had cracked it (see below) however I can
not get the LastModified part to work. Could you have a look and give me a
pointer or two? I have apostrophe'd out the parts that don't work.

I am new to programming and work very much on a trial and error basis so
please forgive me if the below is total garbage.

Many thanks
Dave

Private Sub FolderList()
Set fs = Application.FileSearch
With fs
.LookIn = "E:\MainFolder"
.SearchSubFolders = True
.Filename = "*.*"
' .LastModified = msoLastModifiedAnyTime
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Cells((i + 2), 2) = .FoundFiles(i)
' Cells((i + 2), 3) = .LastModified(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

 

[DaveyC]

I think I have solved my own problem with the following code. Not
particularly pretty but it does the job. I combined the FileSearch function
with the FileSystemObject using the output of the former to feed the latter.

Hope this is of use to others

Best regards
Dave

Private Sub FolderList()
Set fs = Application.FileSearch
With fs
.LookIn = "E:\MainFolder"
.SearchSubFolders = True
.Filename = "*.*"
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Cells((i + 6), 2) = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
Dim ft, d, f, s
For j = 1 To i - 1
Set ft = CreateObject("Scripting.FileSystemObject")
nf = Cells((j + 6), 2)
Set f = ft.GetFile(nf)
s = f.DateLastModified
Cells((j + 6), 3) = s
s = f.DateCreated
Cells((j + 6), 4) = s
Next j
End Sub