To retrieve a single cell's value for all Excel files in a folder

Try this just change the directory to the correct path (in red below)

Sub LoopThroughFolder()

    Dim MyFile, myval As String, Str As String, MyDir As String, Wb As Workbook
    Dim Rws As Long, Rng As Range
    Dim i As Integer
    
    Set Wb = ThisWorkbook
    'change the address to suite
    MyDir = "C:\temp\customer\"
    MyFile = Dir(MyDir & "*.xlsx")    'change file extension
    ChDir MyDir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0

    Do While MyFile <> ""
        Workbooks.Open (MyFile)
        With Worksheets(1)
            
            myval = Range("I41").Value
            
            Wb.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = myval
            Wb.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = MyFile

            ActiveWorkbook.Close True
        End With
        MyFile = Dir()
    Loop

End Sub