I've found lots of Excel VBA code for creating a file directory in Excel, and they have been absolutely wonderful.
I've created a file directory using file paths and file names (and have about 20 files in there). However, since it's a shared folder, people will be adding new files to it all the time. If I click the "Grab Files" button again, I end up duplicating the original 20 files, and getting additional (new) files.
Is there a way to setup this directory to include ONLY the new files by "updating" it? Perhaps it can check which files are already listed in the file directory - and add only the new ones to the list? Is this possible? Any help would be great!! Thank you!
Sub GrabFiles()
Dim strPathFile As String
Dim xDirect$, xFname$, InitialFoldr$, xLocation
Application.ScreenUpdating = False
Range("C1").Formula = "Updated on:"
Range("D1").Formula = "=Today()"
Range("D1").Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
InitialFoldr$ = ""
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select the TARA folder"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1)
ListFilesInFolder xDirect$, True
End If
End With
End Sub
Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder, SubFolders As Scripting.Folders
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
Cells(r, 1).Formula = FileItem.Path
'Space for Link
Cells(r, 3).Formula = FileItem.Name
'Space for Description
r = r + 1
Next FileItem
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.Path, True
Next SubFolder
End If
Range("A2").Select
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
Range("F3:F1000").EntireRow.AutoFit
LastRow = Range("D" & Rows.Count).End(xlUp).Row + 1
Sheets("Grab Files").Range("D" & LastRow).Select
ActiveWindow.ScrollRow = Selection.Row
End Sub
Bookmarks