With this macro, I can list files in a selected directory.
In the spreadsheet, there already exists a list created previously.
Currently, the macro asks which rows to delete before it re-writes the total list of files (this is done because the list changes frequently).
What I want to do instead, is select a range and then apply an update (add if new, nothing if it exists, delete if not there anymore).
There will be data above and below, so additions/subtractions need add/delete row.
Will this be possible?
Dim Row As Long
Sub File_list()
Dim rRange As Range
Dim sFolder As FileDialog
On Error Resume Next
Set rRange = Application.InputBox(Prompt:="Select rows to delete", Title:="Replacing?", Type:=8)
With rRange
.Offset(0, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Set sFolder = Application.FileDialog(msoFileDialogFolderPicker)
If sFolder.Show = -1 Then
Row = 0
File_Details_List_Files sFolder.SelectedItems(1), True
End If
End Sub
Private Sub File_Details_List_Files(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
Dim FSO As Object
Dim SourceFolder As Object
Dim FileItem As Object
Dim strFile As String
Dim FileName As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(SourceFolderName)
If Row = 0 Then Row = ActiveCell.Row
With CreateObject("Scripting.Dictionary")
For Each FileItem In SourceFolder.Files
strFile = FileItem.Name
.Item(strFile) = Array(FileItem.Name)
Next FileItem
If .Count > 0 Then
For Each FileName In .Items
Rows(Row).Insert
Cells(Row, 1).Formula = FileName
Row = Row + 1
Next FileName
End If
End With
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
End Sub
Bookmarks