VBA Recording Power Query not working

**justinwb** · 04-13-2019, 08:11 AM

Good afternoon everyone

I am having a problem with Recording a Marco with Power Query

I set up a Excel sheet with Power query in Office 365 working at home
As my work only runs office 2013 I need to know change all the code over I have set up both Office 365 and Office 2013 on my home PC correctly using office 2013

I have been able to set up all my data and tables with naming them all when I do a Marco record same as I did it on office 365 its not saving any of the PQ code

My steps are open sheet Transport click on Cell A1
click Power Query From Table/Range Power Query opens
Click Advanced Editor replace all code with below

let
    Source = Excel.CurrentWorkbook(){[Name="Datetransport"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Date", "Truck Number", "Time#(lf)Slot", "Arrival Time", "Load Start Time", "Load Finish Time ", "Paperwork To Driver", "Uni Carrier", "Carrier", "Load Number 1", "Load Number 2", "Load Number 3", "Load Number 4", "Load Number 5", "Load Number 6", "Load Number 7", "Load Number 8", "Load Number 9", "Load Number 10", "Load Number 11", "Load Number 12", "Load Number 13", "Load Number 14", "Load Number 15", "Load Number 16", "Load Number 17", "Load Number 18", "Load Number 19", "Load Number 20", "Load Number 21", "Load Number 22", "Load Number 23", "Load Number 24", "Load Number 25", "Load Number 26", "Load Number 27", "Load Number 28", "Load Number 29", "Load Number 30", "Comments"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Date", "Truck Number", "Time#(lf)Slot", "Arrival Time", "Load Start Time", "Load Finish Time ", "Paperwork To Driver", "Uni Carrier", "Carrier", "Comments"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Value] <> null and [Value] <> ""),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Time#(lf)Slot", type time}, {"Arrival Time", type time}, {"Load Start Time", type time}, {"Load Finish Time ", type time}, {"Paperwork To Driver", type time}, {"Value", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Truck Number", "Date", "Value", "Uni Carrier", "Time#(lf)Slot", "Arrival Time", "Load Start Time", "Load Finish Time ", "Paperwork To Driver", "Carrier", "Comments"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "Load Number"}, {"Uni Carrier", "Destination Location Name"}, {"Load Start Time", "Commence Loading"}, {"Load Finish Time ", "Complete Loading"}})
in
    #"Renamed Columns"

Close and load to table existing worksheet =Transport!CV1 then load

everything works

when I try and do the same with recording Macro at the end my vba only shows

    Sheets("Transport").Select
    Range("Datetransport[[#Headers],[Date]]").Select
    Selection.AutoFilter
    ActiveSheet.ListObjects("Datetransport_2").Resize Range("$CV$1:$DF$84")
    Range("CV2").Select
End Sub

when I used office 365 it looked like below

Any help would be great Still learning how to Code in VBA and use record a lot


Sub V3DatePW()
'

    
    Sheets("Transport").Select
    Range("Datetransport[[#Headers],[Date]]").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Queries.Add Name:="Datetransport", Formula:= _
        "let" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Datetransport""]}[Content]," & Chr(10) & "    #""Removed Other Columns"" = Table.SelectColumns(Source,{""Date"", ""Truck Number"", ""Booked Time"", ""Arrival Time"", ""Load Start Time"", ""Load Finish Time "", ""Paperwork to Driver"", ""Uni Carrier"", ""Carrier"", ""Load Number 1"", ""Load Number 2"", ""Load Number 3"", ""Load Numb" & _
        "er 4"", ""Load Number 5"", ""Load Number 6"", ""Load Number 7"", ""Load Number 8"", ""Load Number 9"", ""Load Number 10"", ""Load Number 11"", ""Load Number 12"", ""Load Number 13"", ""Load Number 14"", ""Load Number 15"", ""Load Number 16"", ""Load Number 17"", ""Load Number 18"", ""Load Number 19"", ""Load Number 20"", ""Load Number 21"", ""Load Number 22"", ""Loa" & _
        "d Number 23"", ""Load Number 24"", ""Load Number 25"", ""Load Number 26"", ""Load Number 27"", ""Load Number 28"", ""Load Number 29"", ""Load Number 30"", ""Comments""})," & Chr(10) & "    #""Unpivoted Columns"" = Table.UnpivotOtherColumns(#""Removed Other Columns"", {""Date"", ""Truck Number"", ""Booked Time"", ""Arrival Time"", ""Load Start Time"", ""Load Finish Time "", ""Pape" & _
        "rwork to Driver"", ""Uni Carrier"", ""Carrier"", ""Comments""}, ""Attribute"", ""Value"")," & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Unpivoted Columns"",{""Attribute""})," & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(#""Removed Columns"", each [Value] <> null and [Value] <> """")," & Chr(10) & "    #""Reordered Columns"" = Table.ReorderColumns(#""Filtered Rows"",{""Truck Numb" & _
        "er"", ""Date"", ""Value"", ""Uni Carrier"", ""Booked Time"", ""Arrival Time"", ""Load Start Time"", ""Load Finish Time "", ""Paperwork to Driver"", ""Carrier"", ""Comments""})," & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Reordered Columns"",{{""Date"", type date}, {""Value"", type text}, {""Booked Time"", type time}, {""Arrival Time"", type time}, {""Load " & _
        "Start Time"", type time}, {""Load Finish Time "", type time}, {""Paperwork to Driver"", type time}})," & Chr(10) & "    #""Renamed Columns"" = Table.RenameColumns(#""Changed Type"",{{""Value"", ""Load number""}, {""Uni Carrier"", ""Destination Location Name""}, {""Arrival Time"", ""Arrive Dispatch""}, {""Load Start Time"", ""Commence Loading""}, {""Load Finish Time "", ""Complete" & _
        " Loading""}, {""Paperwork to Driver"", ""Collect Paperwork""}})" & Chr(10) & "in" & Chr(10) & "    #""Renamed Columns"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Datetransport;Extended Properties=""""" _
        , Destination:=Range("$A$700")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Datetransport]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Datetransport_2"
        .Refresh BackgroundQuery:=False
        Sheets("Process").Select
        Range("A1").Select
    End With
End Sub