Can I use a custom function to pull data from a csv file?

I need to extract data from a csv file into an existing excel file. I have little knowledge of custom functions and the deeper mechanics of excel as a whole so with the help of GPT 3.5 I developed a custom function to extract data from a test csv file with 2 records.

Here is the function GPT gave me:

Function GetCSVCellValueFromRecord(csvFilePath As String, recordIndex As Long, targetColumnName As String) As Variant
    Dim csvContent As String
    Dim lines() As String
    Dim headers() As String
    Dim columnIndex As Long
    Dim i As Long
    
    ' Read the entire CSV file into a string
    Open csvFilePath For Input As #1
    csvContent = Input$(LOF(1), 1)
    Close #1
    
    ' Split the CSV content into lines
    lines = Split(csvContent, vbCrLf)
    
    ' Get the headers from the first line
    headers = Split(lines(0), ",")
    
    ' Find the column index of the target data
    columnIndex = -1
    For i = LBound(headers) To UBound(headers)
        If Trim(headers(i)) = targetColumnName Then
            columnIndex = i
            Exit For
        End If
    Next i
    
    ' Return an error if the column name is not found
    If columnIndex = -1 Then
        GetCSVCellValueFromRecord = CVErr(xlErrValue)
        Exit Function
    End If
    
    ' Check if the requested record index is within bounds
    If recordIndex >= 1 And recordIndex <= UBound(lines) Then
        Dim fields() As String
        fields = Split(lines(recordIndex), ",")
        If UBound(fields) >= columnIndex Then
            GetCSVCellValueFromRecord = Trim(fields(columnIndex))
            Exit Function
        End If
    End If
    
    ' Return an error if the record or data is not found
    GetCSVCellValueFromRecord = CVErr(xlErrValue)
End Function

The function calls I tried are :=GetCSVCellValueFromRecord("potter.csv",1,"time") (the csv is in the same folder as the worksheet) =GetCSVCellValueFromRecord(Potter.csv,2,comment) both returned a #Value! error. I'm unsure what went wrong or where to go from here

My ultimate goal is to be able to type the csv file name into a field and have function calls refer to that cell in order to update all relevant fields with information from the csv.

If the issue is you need the full path but only want to pass in the filename, then this change

Function GetCSVCellValueFromRecord(csvFilePath As String, recordIndex As Long, _
                                  targetColumnName As String) As Variant
    Dim csvContent As String
    Dim lines() As String, headers() As String
    Dim columnIndex As Long, i As Long, ff As Integer
    
    ' Read the entire CSV file into a string
    ff = FreeFile 'don't use a hard-coded value...
    Open ThisWorkbook.Path & "\" & csvFilePath For Input As ff
    csvContent = Input$(LOF(ff), ff)
    Close ff
    '...etc

...should do what you want as long as all CSV files are in the same folder where the workbook with the UDF is saved.