using ADODB to import data based on headers for two files

Hi again Leopard

One reason why your code failed was that the TIRES.xlsx file did not contain the named table 'Table1$' for the line rs.Open "Select .... Please use the revised file attached in which range $A$1:$F$58 has the name TiresTable (used in the code below)

Then I've modified your code (in bold below, with some explanation) to give:

Sub test()
    Dim myDir As String, cn As Object, rs As Object, r, myHeading
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder"
        If .Show Then myDir = .SelectedItems(1) & "\"
    End With
    If myDir = "" Then Exit Sub
    'Application.ScreenUpdating = False
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    ' don't try to open this file as below!
    ' With Workbooks.Open(myDir & "RESULT.xlsm")
    '  ### use this instead and identify destination range
    With ThisWorkbook
        With .Sheets(1).Cells(23, 1).CurrentRegion
            myHeading = "`" & Join(.Parent.Evaluate(.Rows(1).Address & "&"""""), "`, `") & "`"
            ' ### clear any existing contents except headings
            If .Rows.Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).ClearContents
        End With
        With cn
            .Provider = "Microsoft.Ace.OLEDB.12.0"
            .Properties("Extended Properties") = "Excel 12.0;HDR=Yes;"
            .Open myDir & "TIRES.xlsx"
        End With
        rs.Open "Select " & myHeading & " From TiresTable", cn
        '.Sheets(1).[A1].CopyFromRecordset rs
        ' ### use this instead (or change/ improve)
        .Sheets(1).Cells(24, 1).CopyFromRecordset rs
        '.Close True   '<--- if you want to close Destination.xlsx, delete apostrophe.
    End With
    Set cn = Nothing: Set rs = Nothing
    Application.ScreenUpdating = True
End Sub

That's in the revised .xlsm file. If you run that code, you should get the correct import. You just need to adjust column B (e.g. using .Autofit) and change some formatting (see the bottom of imported data  which I left in that file).

Hope this fixes your problem. If so, please select my Answer.