check if the value is found in the table return nothing else insert value into new row

[Boogies]

Hi all,

I am new to vba and i had tried numerous function and method to complete my request but i still struggling to finish the missing puzzle of the code. While running the code, it will prompt the file explorer to allow user to select folder. If the folder selected, then it will go through the sub folder and files to retrieve values by calling getfileinfo. I have fews date values are stored in my table 1 so i need a function to check if the date values is existed in the table then return nothing else it will insert new date value into new row where the date value is retrieve from getfileinfo. For example, current table has contain values (20/6/2022, 21/6/2022, 22/6/2022 and etc), so the output from this code is shown as 21/6/2022, 22/6/2022, 23/6/2022 and etc. As you can see there is duplication of value that is existed from the table which is 21/6/2022 & 22/6/2022.

Sub update()
   
    Dim yearFolder As String
   
    'Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then 'if OK is clicked for year folder
            yearFolder = .SelectedItems(1)
        End If
    End With
   
    If yearFolder <> "" Then 'if a file was chosen
        Dim FileSystem As Object: Set FileSystem = CreateObject("Scripting.FileSystemObject")
        DoFolder FileSystem.GetFolder(yearFolder)
    End If
       
End Sub
Sub DoFolder(Folder)

    Dim SubFolder
   
    For Each SubFolder In Folder.SubFolders
        DoFiles SubFolder
    Next

End Sub

Sub DoFiles(Folder)

    Dim File
   
    For Each File In Folder.Files
            Call getFileInfo(Folder.path, File.Name, "Summary")
    Next

End Sub

Sub getFileInfo(path As String, filename As String, sheetName As String)

    Dim dt As String: dt = PeekFileCell(path, filename, sheetName, 2, 3) 'Date
  
   'Define worksheet and table name
    Dim tbl As ListObject: Set tbl = ThisWorkbook.Worksheets("Sheet 1").ListObjects("Table 1")
    Dim r As Range: Set r = tbl.Range.Find(dt)
           
            If r Is Nothing Then
                Dim nr As ListRow: Set nr = tbl.ListRows.Add
                nr.Range.Cells(1,1).Value = dt
            End If
           
End Sub

'Return target cell value of a given workbook as a variant
Public Function PeekFileCell(FilePath As String, filename As String, WorksheetName As String, Cellrow As Long, Cellcol As Long) As Variant

    If Len(FilePath) = 0 Or Len(filename) = 0 Or Len(WorksheetName) = 0 Or Cellrow < 1 Or Cellcol < 1 Then
        Exit Function
    End If

    PeekFileCell = ExecuteExcel4Macro("'" & FilePath & "\" & "[" & filename & "]" & WorksheetName & "'!" & Cells(Cellrow, Cellcol).Address(1, 1, xlR1C1))
   
End Function

Any help would be much appreciated.

 

[Boogies]

sorry for posting wrong format

 

[Peter_SSs]

Welcome to the MrExcel board!

sorry for posting wrong format

Your second attempt, though better, still was not right as all the indentation formatting had gone.
So I have removed that code and added the code tags to your first post that still had the indentation information behind the scenes there.

 

[Boogies]

thanks @Peter_SSs for correcting code block, would like to request assistance in this matter, much appreciated

 

[Peter_SSs]

would like to request assistance in this matter, much appreciated

It is not the sort of question I choose to answer in the forum, but somebody else will likely chip in. Good luck!

 

[Boogies]

*Update to be clear what need to be fix is the sub filegetinfo function

Sub getFileInfo(path As String, filename As String, sheetName As String)

    Dim dt As String: dt = PeekFileCell(path, filename, sheetName, 2, 3) 'Date
  
   'Define worksheet and table name
    Dim tbl As ListObject: Set tbl = ThisWorkbook.Worksheets("Sheet 1").ListObjects("Table 1")
    Dim r As Range: Set r = tbl.Range.Find(dt)
           
            If r Is Nothing Then
                Dim nr As ListRow: Set nr = tbl.ListRows.Add
                nr.Range.Cells(1,1).Value = dt
            End If
           
End Sub

I would like to request assistance in this matter, which is help to check the value from r object contain any existing value (for example like date)will exit the function, else it will add into new row which is declared by nr.
scenario
if date (20jun-25jun) is existed in the table 1 at first column then it will exit function , else it will add latest value into new row which is (26jun)