Strange field name when exporting from Excel to CSV.

Option Explicit

Sub ConsolidateWithADO()
Dim wsData As Worksheet
Dim conn As Object
Dim cat As Object
Dim rst As Object
Dim tbl As Object
Dim fld As Object
Dim strPath As String
Dim strSQL As String

    Application.ScreenUpdating = False
    
    Set wsData = Sheets.Add(After:=Sheets(Sheets.Count))
    
    'wsData.Name = "Consolidated Data"
    
    strPath = ThisWorkbook.Path
    
    Set conn = CreateObject("ADODB.Connection")
    
    Set cat = CreateObject("ADOX.Catalog")
    
    ' set connection to folder with CSV files
    
    conn.Connectionstring = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & strPath & _
            ";Extended Properties=""text;HDR=Yes;"";Persist Security Info=False"
            
    conn.Open
    
    cat.ActiveConnection = conn
    
    Set rst = CreateObject("ADODB.Recordset")
    
    ' loop through each CSV in folder and get data
    For Each tbl In cat.Tables
        
        ' get data from CSV file
        strSQL = "SELECT * FROM [" & tbl.Name & "] W"
        
        rst.Open strSQL, conn
        
        With wsData
        
            ' if no field names, add them
            If .Range("A1").Value = "" Then
                For Each fld In rst.Fields
                    .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Value = fld.Name
                Next fld
                .Columns(1).Delete
            End If
            
            ' copy data from CSV to next empty row
            .Range("A" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset rst
            
        End With
        
        rst.Close
        
    Next tbl
    
    ' clean up
    Set rst = Nothing
    
    Set cat = Nothing
    
    conn.Close
    
    Set conn = Nothing
    
    ' tidy up results a bit
    
    wsData.Cells.EntireColumn.AutoFit
    
    Application.ScreenUpdating = True
    
End Sub

Select allOpen in new window