Reading CSV into listobject query table

Hi Guys,

i want to do 2 things:
1) Import CSV table in format like:

"Col1","Col2","Col3"
"1","2",""
"","3","4"

into Excel activesheet.

2) If listobject not exists just create new listobject.

3) If listobject exists just refresh whole table to get the same result like in CSV (for example data added, column deleted and so on).

I tried with ADODB object :

Public Function GetCSVData(ByVal strFile As String) As Object
    Dim lngSplit As Long, strTable As String, strPath As String
    Dim objRS As Object
    Dim strConnection As String, strSQL As String
    
    lngSplit = InStrRev(strFile, "\")
    
    strTable = Mid$(strFile, lngSplit + 1)
    strPath = Left$(strFile, lngSplit - 1)
    
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strPath & ";" & _
                    "Extended Properties=Text;"
                    
    strSQL = "SELECT * FROM " & strTable & ";"


    Set objRS = CreateObject("ADODB.Recordset")
    varCsvData = objRS.Open(strSQL, strConnection, 1, 3, 1)
End Function

Sub CSVDownload()

    Dim varCsvData As Variant
    Dim path As Variant
    
    path = "D:\Pulpit\Newest Pull request\ImportingBundles\CSVTest.csv"

    varCsvData = GetCSVData(path)
    
End Sub

but it failed for me - the varCsvData variant array is empty.

and i tried this code:

Sub main() ' {

    Dim connectionString As String
    Dim path As Variant
    
    path = "D:\Pulpit\Newest Pull request\ImportingBundles\CSVTest.csv"

    connectionString = _
       "OLEDB;" & _
       "provider=Microsoft.ACE.OLEDB.12.0;" & _
       "data source=" & path & ";" & _
       "extended Properties=text"

    Dim destTable As ListObject

    Set destTable = ActiveSheet.ListObjects.Add( _
       SourceType:=xlSrcExternal, _
       Source:=connectionString, _
       Destination:=Cells(2, 2))

    With destTable.QueryTable

        .CommandType = xlCmdSql
        .CommandText = Array("select * from [CSVTest.csv]")
        .BackgroundQuery = True

        .Refresh BackgroundQuery:=False

    End With

End Sub

but i am getting:

Screenshot_106.png

Maybe i could use somehow power query formula through VBA to change it dynamically and use query?
I do not know,
please help,
Best Wishes,
Jacek