Excel Vba avoid repeated extraction of data

Question

i have a folder which contains e.g files abc, poi, krf, lss and i have a button that contains an existing code that iterates through the folder and extract data from each files into the excel sheet.

However despite the data being already extracted to the excel sheet, every time i click the button it will still extract data from each files, i would like to avoid that by saving all the filenames in the folder into an array or other methods THEN whenever i press the button , it will compare the "previous file names" inside the folder, if any changes are done to the folder, e.g new file mmn is added into the folder, it will only do extraction on file "mmn" and skip extraction on the rest of the files that were still in the folder.

e.g my folder now currently has 50 files(all with different names) e.g "abc","mmn","lll", i save these 50 file names and then if i click on my button to extract data, it will check whether my folder had any changes happened to it, e.g if it still contains the 50 files(same name as previous,"abc","mmm","lll") , then don't do any extraction because data already exists in the excel sheet.

however, if the files in the folder changed, e.g a new file named "uio" got added in, then i want to only do extraction on "uio" and add the data below the last row that contains data instead of all the files "abc","mmn","lll" and "uio" .

This is my current code, can anyone please let me know how i can modify it to match my description?

Thank you to everyone.

Option Explicit

Sub LoopFiles()

Dim strFolder As String
Dim strDirRef As String
Dim nFiles As Long, i As Long
Dim strFound() As String

strFolder = "C:UsersDesktopasi"

strDirRef = Dir(strFolder)
Do While Len(strDirRef) > 0
    nFiles = nFiles + 1

    ReDim Preserve strFound(1 To nFiles)
    strFound(nFiles) = strDirRef
    strDirRef = Dir
Loop

For i = 1 To nFiles
    Debug.Print strFound(i)
Next i

End Sub

Answer 1

Hereby a small snippet on how I would store these in a Dictionary object using Exists method.

Public Dict As Object

Sub Test()

Dim oFSO As Object, oFolder As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("C:UsersDesktopasi")
If Dict Is Nothing Then
    Set Dict = CreateObject("Scripting.Dictionary")
End If

For Each oFile In oFolder.Files
    If Dict.Exists(oFSO.GetBaseName(oFile)) Then
        'Skip file
    Else
        'Don't skip file and do something....
        'Add to dictionary for next iteration
        Dict.Add oFSO.GetBaseName(oFile), 1
    End If
Next oFile

Range("A1").Resize(UBound(Dict.Keys)).Value = Application.Transpose(Dict.Keys)

End Sub