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.
