Copying Excel data from one file to the other and reformatting

Question

I am trying to copy data from one excel to the other and then reformat.

This is the code I am using:

ActiveWorkbook.Queries.Add Name:="Export", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\Khawaja\Desktop\Export.csv""),[Delimiter="","", Columns=9, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Name"", type text}, {""Surname"", type" & _
        " text}, {""Email"", type text}, {""Action"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    Sheets.Add After:=ActiveSheet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Export;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Export]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "Export"
        .Refresh BackgroundQuery:=False

My source file has data in columns. Each column has a heading of Name, Surname, Email and Action. But when I run the macro, it is not able to detect the column heads.

This is the error I get:

The column 'Name' of the table was not found

Any idea how the error can be removed?

Answer 1

The argument you are looking for in Listobjects.add is XlListObjectHasHeaders. To that argument, you must type XlListObjectHasHeaders:=xlYes. You can also type xlGuess and Excel will guess if the tables has headers. In your case you have string data in both the header and data, therefore the default xlGuess is likely failing for that reason.

  ActiveWorkbook.Queries.Add Name:="Export", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\Khawaja\Desktop\Export.csv""),[Delimiter="","", Columns=9, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Name"", type text}, {""Surname"", type" & _
            " text}, {""Email"", type text}, {""Action"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        Sheets.Add After:=ActiveSheet
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Export;Extended Properties=""""" _
            , XlListObjectHasHeaders:=xlYes, Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [Export]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = False
            .ListObject.DisplayName = "Export"
            .Refresh BackgroundQuery:=False