Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare contents of two folders
Morning folks.
I'm looking to compare the contents of two directories, and then output the results to either a text file, or an excel file. We'd tried using a batch command set that we'd found online, but it did not provide the output we'd desired. Most of what I'm finding out there are code comparison programs, and vba code to check the contents of files, as well as subdirectory comparitors. My goal is to simply check to see if the work done so far is being duplicated or not; thus I want to compare the contents of two folders. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare contents of two folders
How about sub-folders?
Should their contents be compared or not? -- Gary''s Student - gsnu200850 "Steve" wrote: Morning folks. I'm looking to compare the contents of two directories, and then output the results to either a text file, or an excel file. We'd tried using a batch command set that we'd found online, but it did not provide the output we'd desired. Most of what I'm finding out there are code comparison programs, and vba code to check the contents of files, as well as subdirectory comparitors. My goal is to simply check to see if the work done so far is being duplicated or not; thus I want to compare the contents of two folders. Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare contents of two folders
No.
In this case, we'll only be comparing top level directory's contents. "Gary''s Student" wrote: How about sub-folders? Should their contents be compared or not? -- Gary''s Student - gsnu200850 "Steve" wrote: Morning folks. I'm looking to compare the contents of two directories, and then output the results to either a text file, or an excel file. We'd tried using a batch command set that we'd found online, but it did not provide the output we'd desired. Most of what I'm finding out there are code comparison programs, and vba code to check the contents of files, as well as subdirectory comparitors. My goal is to simply check to see if the work done so far is being duplicated or not; thus I want to compare the contents of two folders. Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare contents of two folders
Do you only need to know if the same files are in both folders or do the
versions (timedate stamps) of the files need to be the same? -- Gary''s Student - gsnu200850 "Steve" wrote: No. In this case, we'll only be comparing top level directory's contents. "Gary''s Student" wrote: How about sub-folders? Should their contents be compared or not? -- Gary''s Student - gsnu200850 "Steve" wrote: Morning folks. I'm looking to compare the contents of two directories, and then output the results to either a text file, or an excel file. We'd tried using a batch command set that we'd found online, but it did not provide the output we'd desired. Most of what I'm finding out there are code comparison programs, and vba code to check the contents of files, as well as subdirectory comparitors. My goal is to simply check to see if the work done so far is being duplicated or not; thus I want to compare the contents of two folders. Thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare contents of two folders
Mainly if the same files exist.
Although, now that you mention it, knowing the other info would indeed be helpful. "Gary''s Student" wrote: Do you only need to know if the same files are in both folders or do the versions (timedate stamps) of the files need to be the same? -- Gary''s Student - gsnu200850 "Steve" wrote: No. In this case, we'll only be comparing top level directory's contents. "Gary''s Student" wrote: How about sub-folders? Should their contents be compared or not? -- Gary''s Student - gsnu200850 "Steve" wrote: Morning folks. I'm looking to compare the contents of two directories, and then output the results to either a text file, or an excel file. We'd tried using a batch command set that we'd found online, but it did not provide the output we'd desired. Most of what I'm finding out there are code comparison programs, and vba code to check the contents of files, as well as subdirectory comparitors. My goal is to simply check to see if the work done so far is being duplicated or not; thus I want to compare the contents of two folders. Thank you. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare contents of two folders
Put the first folder path in A1 and the second folder path in B1 and run
TestListFilesInFolder: Sub TestListFilesInFolder() Application.ScreenUpdating = False ListFilesInFolder Range("A1").Value, False, 1 ListFilesInFolder Range("B1").Value, False, 2 Application.ScreenUpdating = True End Sub Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean, kolumn As Integer) Dim FSO As Scripting.FileSystemObject Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder Dim FileItem As Scripting.File Dim r As Long, k As Integer MsgBox (SourceFolderName & kolumn) Set FSO = New Scripting.FileSystemObject Set SourceFolder = FSO.GetFolder(SourceFolderName) r = Cells(Rows.Count, kolumn).End(xlUp).Row + 1 k = kolumn For Each FileItem In SourceFolder.Files Cells(r, k).Value = FileItem.Path r = r + 1 Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder.Path, True, kolumn Next SubFolder End If Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing End Sub The files will be listed below the folder paths. Adapted from Joel Rubin's Site: http://www.exceltip.com/st/List_file...Excel/446.html -- Gary''s Student - gsnu200850 "Steve" wrote: Mainly if the same files exist. Although, now that you mention it, knowing the other info would indeed be helpful. "Gary''s Student" wrote: Do you only need to know if the same files are in both folders or do the versions (timedate stamps) of the files need to be the same? -- Gary''s Student - gsnu200850 "Steve" wrote: No. In this case, we'll only be comparing top level directory's contents. "Gary''s Student" wrote: How about sub-folders? Should their contents be compared or not? -- Gary''s Student - gsnu200850 "Steve" wrote: Morning folks. I'm looking to compare the contents of two directories, and then output the results to either a text file, or an excel file. We'd tried using a batch command set that we'd found online, but it did not provide the output we'd desired. Most of what I'm finding out there are code comparison programs, and vba code to check the contents of files, as well as subdirectory comparitors. My goal is to simply check to see if the work done so far is being duplicated or not; thus I want to compare the contents of two folders. Thank you. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare contents of two folders
S-w-eeeeet!!!
Thank you!!! I've bookmarked the link for future reference as well. Yet Another satisfied customer..... "Gary''s Student" wrote: Put the first folder path in A1 and the second folder path in B1 and run TestListFilesInFolder: Sub TestListFilesInFolder() Application.ScreenUpdating = False ListFilesInFolder Range("A1").Value, False, 1 ListFilesInFolder Range("B1").Value, False, 2 Application.ScreenUpdating = True End Sub Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean, kolumn As Integer) Dim FSO As Scripting.FileSystemObject Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder Dim FileItem As Scripting.File Dim r As Long, k As Integer MsgBox (SourceFolderName & kolumn) Set FSO = New Scripting.FileSystemObject Set SourceFolder = FSO.GetFolder(SourceFolderName) r = Cells(Rows.Count, kolumn).End(xlUp).Row + 1 k = kolumn For Each FileItem In SourceFolder.Files Cells(r, k).Value = FileItem.Path r = r + 1 Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder.Path, True, kolumn Next SubFolder End If Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing End Sub The files will be listed below the folder paths. Adapted from Joel Rubin's Site: http://www.exceltip.com/st/List_file...Excel/446.html -- Gary''s Student - gsnu200850 "Steve" wrote: Mainly if the same files exist. Although, now that you mention it, knowing the other info would indeed be helpful. "Gary''s Student" wrote: Do you only need to know if the same files are in both folders or do the versions (timedate stamps) of the files need to be the same? -- Gary''s Student - gsnu200850 "Steve" wrote: No. In this case, we'll only be comparing top level directory's contents. "Gary''s Student" wrote: How about sub-folders? Should their contents be compared or not? -- Gary''s Student - gsnu200850 "Steve" wrote: Morning folks. I'm looking to compare the contents of two directories, and then output the results to either a text file, or an excel file. We'd tried using a batch command set that we'd found online, but it did not provide the output we'd desired. Most of what I'm finding out there are code comparison programs, and vba code to check the contents of files, as well as subdirectory comparitors. My goal is to simply check to see if the work done so far is being duplicated or not; thus I want to compare the contents of two folders. Thank you. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare contents of two folders
I have another question for you....
I went in to the ExcelTips page you gave me, and have made some modifications to Joe's macro. I've set it up so that I could only choose specific attributes/properties of the file, for the two directories. Which means that I had to essentially duplicate the ListFilesInFolder code, but make it input the values one column to the right of the source column. I have yet to further study your sample, so that's what I could be missing. Please see my question down below the code.... '*******************code*********************** Sub TestListFilesInFolder() Workbooks.Add ' create a new workbook for the file list ' add headers With range("A1") .Formula = "Folder contents:" .Font.Bold = True .Font.size = 12 End With range("A3:b3").Formula = "File Name:" range("c3:d3").Formula = "File Size:" range("e3:f3").Formula = "Date Created:" range("g3:h3").Formula = "Date Last Modified:" range("A3:H3").Font.Bold = True ListFilesInFolder "N:\Steve'sTemp\New", True ListFilesOnFolder "N:\Steve'sTemp\Old", True ' list all files included subfolders End Sub '=================================== Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean) ' lists information about the files in SourceFolder ' example: ListFilesInFolder "C:\FolderName\", True Dim FSO As Scripting.FileSystemObject Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder Dim FileItem As Scripting.File Dim r As Long Set FSO = New Scripting.FileSystemObject Set SourceFolder = FSO.GetFolder(SourceFolderName) r = range("A65536").End(xlUp).Row + 1 For Each FileItem In SourceFolder.Files ' display file properties Cells(r, 1).Formula = FileItem.Name Cells(r, 3).Formula = FileItem.size Cells(r, 5).Formula = FileItem.DateCreated Cells(r, 7).Formula = FileItem.DateLastModified ' use file methods (not proper in this example) ' FileItem.Copy "C:\FolderName\Filename.txt", True ' FileItem.Move "C:\FolderName\Filename.txt" ' FileItem.Delete True r = r + 1 ' next row number Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder.Path, True Next SubFolder End If Columns("A:H").AutoFit Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing ActiveWorkbook.Saved = True End Sub '================================================ Sub ListFilesOnFolder(SourceFolderName As String, IncludeSubfolders As Boolean) ' lists information about the files in SourceFolder ' example: ListFilesInFolder "C:\FolderName\", True Dim FSO As Scripting.FileSystemObject Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder Dim FileItem As Scripting.File Dim r As Long Set FSO = New Scripting.FileSystemObject Set SourceFolder = FSO.GetFolder(SourceFolderName) r = 0 r = range("A65536").End(xlUp).Row + 1 ' this appears to be where my 'issue originates. For Each FileItem In SourceFolder.Files ' display file properties Cells(r, 2).Formula = FileItem.Name Cells(r, 4).Formula = FileItem.size Cells(r, 6).Formula = FileItem.DateCreated Cells(r, 8).Formula = FileItem.DateLastModified ' use file methods (not proper in this example) ' FileItem.Copy "C:\FolderName\Filename.txt", True ' FileItem.Move "C:\FolderName\Filename.txt" ' FileItem.Delete True r = r + 1 ' next row number Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder.Path, True Next SubFolder End If Columns("A:H").AutoFit Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing ActiveWorkbook.Saved = True End Sub '******************end of code********************* As you can see, it will input the second directory's file names in the column to the right of the first. However, instead of placing them adjacent, it takes in to account the already counted r, and sets the second set down the number of rows, for the number of files. I set a watch, and the second, ListFilesOnFolder, starts counting at the bottom of the range selected by r. i.e., r = range("A65536").End(xlUp).Row + 1 How would I set this to start at the same row, as did the ListFilesInFolder began? Beyond that, this is working as needed (that is until we find yet another use which would require further modifications-- but that's another day .... ;-) ). Again, thank you. Best. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scratch that.....
Never mind.... I figured it out-- about two minutes after I clicked post.
Again, thank you for all your help. "Steve" wrote: I have another question for you.... I went in to the ExcelTips page you gave me, and have made some modifications to Joe's macro. I've set it up so that I could only choose specific attributes/properties of the file, for the two directories. Which means that I had to essentially duplicate the ListFilesInFolder code, but make it input the values one column to the right of the source column. I have yet to further study your sample, so that's what I could be missing. Please see my question down below the code.... '*******************code*********************** Sub TestListFilesInFolder() Workbooks.Add ' create a new workbook for the file list ' add headers With range("A1") .Formula = "Folder contents:" .Font.Bold = True .Font.size = 12 End With range("A3:b3").Formula = "File Name:" range("c3:d3").Formula = "File Size:" range("e3:f3").Formula = "Date Created:" range("g3:h3").Formula = "Date Last Modified:" range("A3:H3").Font.Bold = True ListFilesInFolder "N:\Steve'sTemp\New", True ListFilesOnFolder "N:\Steve'sTemp\Old", True ' list all files included subfolders End Sub '=================================== Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean) ' lists information about the files in SourceFolder ' example: ListFilesInFolder "C:\FolderName\", True Dim FSO As Scripting.FileSystemObject Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder Dim FileItem As Scripting.File Dim r As Long Set FSO = New Scripting.FileSystemObject Set SourceFolder = FSO.GetFolder(SourceFolderName) r = range("A65536").End(xlUp).Row + 1 For Each FileItem In SourceFolder.Files ' display file properties Cells(r, 1).Formula = FileItem.Name Cells(r, 3).Formula = FileItem.size Cells(r, 5).Formula = FileItem.DateCreated Cells(r, 7).Formula = FileItem.DateLastModified ' use file methods (not proper in this example) ' FileItem.Copy "C:\FolderName\Filename.txt", True ' FileItem.Move "C:\FolderName\Filename.txt" ' FileItem.Delete True r = r + 1 ' next row number Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder.Path, True Next SubFolder End If Columns("A:H").AutoFit Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing ActiveWorkbook.Saved = True End Sub '================================================ Sub ListFilesOnFolder(SourceFolderName As String, IncludeSubfolders As Boolean) ' lists information about the files in SourceFolder ' example: ListFilesInFolder "C:\FolderName\", True Dim FSO As Scripting.FileSystemObject Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder Dim FileItem As Scripting.File Dim r As Long Set FSO = New Scripting.FileSystemObject Set SourceFolder = FSO.GetFolder(SourceFolderName) r = 0 r = range("A65536").End(xlUp).Row + 1 ' this appears to be where my 'issue originates. For Each FileItem In SourceFolder.Files ' display file properties Cells(r, 2).Formula = FileItem.Name Cells(r, 4).Formula = FileItem.size Cells(r, 6).Formula = FileItem.DateCreated Cells(r, 8).Formula = FileItem.DateLastModified ' use file methods (not proper in this example) ' FileItem.Copy "C:\FolderName\Filename.txt", True ' FileItem.Move "C:\FolderName\Filename.txt" ' FileItem.Delete True r = r + 1 ' next row number Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder.Path, True Next SubFolder End If Columns("A:H").AutoFit Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing ActiveWorkbook.Saved = True End Sub '******************end of code********************* As you can see, it will input the second directory's file names in the column to the right of the first. However, instead of placing them adjacent, it takes in to account the already counted r, and sets the second set down the number of rows, for the number of files. I set a watch, and the second, ListFilesOnFolder, starts counting at the bottom of the range selected by r. i.e., r = range("A65536").End(xlUp).Row + 1 How would I set this to start at the same row, as did the ListFilesInFolder began? Beyond that, this is working as needed (that is until we find yet another use which would require further modifications-- but that's another day .... ;-) ). Again, thank you. Best. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare row contents | Excel Programming | |||
compare the contents of one range of cells with the contents of a. | Excel Discussion (Misc queries) | |||
Read- only Protect Folders and contents in WIN2K Server | Excel Discussion (Misc queries) | |||
Delete an entire folders contents (or just Excel files) | Excel Programming | |||
delete all the contents (sub folders and files) in the temp folder | Excel Discussion (Misc queries) |