Content Comparison

Sub ConceptSauce_ImageMatchHelper_Debug()
' Copyright ConceptSauce ltd 2023
    Dim folderPath As String
    Dim destFolderPath As String
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim fileObj fso As Scripting.FileSystemObject
    Dim filenamesDict As Object
    Dim folderbaseFilename As ObjectString
    Dim fsoresponse As ObjectVbMsgBoxResult
    Dim filenamesDictmoveFiles As ObjectBoolean
    Dim baseFilenamelastSearchFolder As String
    Dim responselastCopyFolder As String
  VbMsgBoxResult  Dim debugOutput As String
'
Create a dictionary to store' collectionsSet ofdefault filefolder paths from named ranges, indexed by exact base filenames
 if available
    On Error Resume Next
   Set filenamesDictlastSearchFolder = CreateObjectThisWorkbook.Names("Scripting.Dictionary")"LastSearchFolder").RefersToRange.Value
    lastCopyFolder = ThisWorkbook.Names("LastCopyFolder").RefersToRange.Value
    On Error GoTo 0

    ' Prompt user for the folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder to Search"
        .InitialFileName = lastSearchFolder
        .AllowMultiSelect = False
        If .Show = -1 Then
            folderPath = .SelectedItems(1)
        Else    ' Save the selected folder path
   Exit Sub        SetNamedRange End If"LastSearchFolder", folderPath
    End With   Else
  ' Create filesystem object     Set fso = CreateObject("Scripting.FileSystemObject")Exit Sub
    Set folder = fso.GetFolder(folderPath) End If
   ' AddEnd filenamesWith
from
the main folder to the' dictionaryCreate filesystem object
  For Each fileObj In folder.Files
        baseFilenameSet fso = Split(fileObj.Name, ".")(0)New Scripting.FileSystemObject

    ' Create a Ifdictionary Not filenamesDict.exists(baseFilename) Then
   to store file paths by base filename
        Set filenamesDict(baseFilename) = New CollectionCreateObject("Scripting.Dictionary")
         End If
        filenamesDict(baseFilename).Add fileObj.Path
AddFilesRecursively fso.GetFolder(folderPath), filenamesDict

  Next fileObj 
    ' Get the active worksheet
    Set ws = ThisWorkbook.ActiveSheet

    ' Ask user to select the column to match
    On Error Resume Next
    Set rng = Application.InputBox("Select a range", "Select cells with filenames to match", Type:=8)
    On Error GoTo 0


   ' Exit if the range is not valid or has no cells
    If rng Is Nothing Or rng.Cells.Count = 0 Then Exit Sub

    ' Color-code cells based on filename matches
    For Each cell In rng.Cells
        baseFilename = Trim(CStr(Trim(cell.Value)) ' Trim spaces
        If filenamesDict.existsPartialMatchExists(baseFilename, filenamesDict) Then
            cell.Interior.Color = RGB(0, 255, 0) ' Green
        Else
            cell.Interior.Color = RGB(255, 0, 0) ' Red
        End If
    Next cell

    ' Ask the user if they want to copy or move the found files
    response = MsgBox("Do you want to copy or move the found files to a new folder?" & vbCrLf & _
                      "Yes = Copy, No = Move, Cancel = Exit", vbYesNovbYesNoCancel + vbQuestion, "CopyFile FilesOperation")
    If response = vbYesvbCancel Then Exit Sub
    moveFiles = (response = vbNo)

    ' Prompt for destination folder and set initial path
    With Application.FileDialog(msoFileDialogFolderPicker)

           .Title = "Select a Destination Folder"
        .InitialFileName = lastCopyFolder
        .AllowMultiSelect = False
   
        If .Show = -1 Then
   
            destFolderPath = .SelectedItems(1)
             Else' Save the destination folder path
            SetNamedRange "LastCopyFolder", destFolderPath
        Else
            Exit Sub
        End If
    End With

 If   ' Initialize debugging output
  End With debugOutput = "Debug Output:" & vbCrLf

    ' Move or Copycopy thematched files that exactly match the cell value
   
    Dim processedFilesDict As Object
    Set processedFilesDict = CreateObject("Scripting.Dictionary")
    Dim filePath As Variant
    For Each cell In rng.Cells
   
        baseFilename = Trim(CStr(Trim(cell.Value)) ' Trim spaces
         If filenamesDict.exists(baseFilename) Then
                For Each filePath In filenamesDict(baseFilename)
                If Not processedFilesDict.exists(filePath) Then
                    debugOutput = debugOutput & "Matched File: " & filePath & vbCrLf
                    If fso.FileExists(filePath) Then ' Ensure the file exists
                        If moveFiles Then
                            debugOutput = debugOutput & "Moving to: " & destFolderPath & "\" & fso.GetFileName(filePath) & vbCrLf
                            fso.MoveFile filePath, destFolderPath & "\" & fso.GetFileName(filePath)
                        Else
                            debugOutput = debugOutput & "Copying to: " & destFolderPath & "\" & fso.GetFileName(filePath) & vbCrLf
                            fso.CopyFile filePath, destFolderPath & "\" & fso.GetFileName(filePath), True
                        End If
                        processedFilesDict.Add filePath, True
                    Else
                        debugOutput = debugOutput & "File not found: " Next& filePath & vbCrLf
                    End If
                End If
            Next cell filePath
        Else
            debugOutput = debugOutput & "No match for: " & baseFilename & vbCrLf
        End If
    Next cell

    ' Output debugging information
    ' Inform the user that the process is complete Debug.Print debugOutput
    ' MsgBox debugOutput, vbInformation, "Debugging Information"

    MsgBox "Process complete!", vbInformation, "Done"
End Sub

' Optimized recursive subroutine to add files from folder and subfolders
Sub AddFilesRecursively(folder As Scripting.folder, filenamesDict As Object)
    Dim fileObj As Scripting.File
    Dim subfolder As Scripting.folder
    Dim baseFilename As String

    For Each fileObj In folder.Files
        If InStr(fileObj.Name, ".jpg") > 0 Or InStr(fileObj.Name, ".png") > 0 Then
            baseFilename = Trim(Split(fileObj.Name, ".")(0)) ' Trim spaces from base filename
            If Not filenamesDict.exists(baseFilename) Then
                Set filenamesDict(baseFilename) = New Collection
            End If
            filenamesDict(baseFilename).Add fileObj.Path
        End If
    Next fileObj

    For Each subfolder In folder.Subfolders
        AddFilesRecursively subfolder, filenamesDict
    Next subfolder
End Sub

' Helper function for partial matches
Function PartialMatchExists(baseFilename As String, filenamesDict As Object) As Boolean
    Dim key As Variant
    PartialMatchExists = False
    For Each key In filenamesDict.Keys
        If Left(key, Len(baseFilename)) = baseFilename Then
            PartialMatchExists = True
            Exit Function
        End If
    Next key
End Function

' Function to set or update a named range with folder path
Sub SetNamedRange(rangeName As String, folderPath As String)
    On Error Resume Next
    ' Ensure the range is valid by wrapping the folderPath as a valid Excel string literal
    ThisWorkbook.Names.Add Name:=rangeName, RefersTo:="=""" & folderPath & """"
    On Error GoTo 0
End Sub