Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
File path listing and analysis
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
File path listing and analysis
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
File path listing and analysis
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 "Bob Phillips" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
File path listing and analysis
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 "DaveyC" wrote: 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 "Bob Phillips" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
file path | Excel Discussion (Misc queries) | |||
file path | Excel Discussion (Misc queries) | |||
Excel updating from XML file - file path specific? | Excel Discussion (Misc queries) | |||
Want file title listing as in Excel 5.0 File>Find | Excel Discussion (Misc queries) |