Need help with extracting data from CSV files in a folder

Hi guys, I am trying to extract the data from CSV files placed in a folder (Location 1). In this regard, I generated a MACRO but the code is not working with other files.

Please note that the path to the folder is: C:\Users\Universities\Cornell\Plastics Circular Economy\4. Modelling\1. Solar Irradiance Data\Idaho\Location 1

The Excel file in which I want to import data is: Idaho.xlsm

I want to combine all the data from the source files into a master sheet, similar to the one we can have using the Data>Data Extraction>Folders>Combine & Load.

I have the following code:

Sheets("Location 1").Select
    ActiveWorkbook.Queries.Add Name:="Transform Sample File", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Parameter1,[Delimiter="","", Columns=46, Encoding=1252, QuoteStyle=QuoteStyle.None])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
    ActiveWorkbook.Queries.Add Name:="Parameter1", Formula:= _
        "#""Sample File"" meta [IsParameterQuery=true, BinaryIdentifier=#""Sample File"", Type=""Binary"", IsParameterQueryRequired=true]"
    ActiveWorkbook.Queries.Add Name:="Sample File", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Folder.Files(""C:\Users\Cornell\Plastics Circular Economy\4. Modelling\1. Solar Irradiance Data\Idaho\Location 1"")," & Chr(13) & "" & Chr(10) & "    Navigation1 = Source{0}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Navigation1"
    ActiveWorkbook.Queries.Add Name:="Transform File", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = (Parameter1) => let" & Chr(13) & "" & Chr(10) & "        Source = Csv.Document(Parameter1,[Delimiter="","", Columns=46, Encoding=1252, QuoteStyle=QuoteStyle.None])" & Chr(13) & "" & Chr(10) & "    in" & Chr(13) & "" & Chr(10) & "        Source" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
    ActiveWorkbook.Queries.Add Name:="Location 1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Folder.Files(""C:\Users\Universities\Cornell\Plastics Circular Economy\4. Modelling\1. Solar Irradiance Data\Idaho\Location 1"")," & Chr(13) & "" & Chr(10) & "    #""Filtered Hidden Files1"" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true)," & Chr(13) & "" & Chr(10) & "    #""Invoke Custom Function1"" = Table.AddColumn(#""Filtered Hidden Files1"", ""Tr" & _
        "ansform File"", each #""Transform File""([Content]))," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns1"" = Table.RenameColumns(#""Invoke Custom Function1"", {""Name"", ""Source.Name""})," & Chr(13) & "" & Chr(10) & "    #""Removed Other Columns1"" = Table.SelectColumns(#""Renamed Columns1"", {""Source.Name"", ""Transform File""})," & Chr(13) & "" & Chr(10) & "    #""Expanded Table Column1"" = Table.ExpandTableColumn(#""Removed Other Columns1""," & _
        " ""Transform File"", Table.ColumnNames(#""Transform File""(#""Sample File"")))," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Expanded Table Column1"",{{""Source.Name"", type text}, {""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type te" & _
        "xt}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", type text}, {""Column19"", type text}, {""Column20"", type text}, {""Column21""," & _
        " type text}, {""Column22"", type text}, {""Column23"", type text}, {""Column24"", type text}, {""Column25"", type text}, {""Column26"", type text}, {""Column27"", type text}, {""Column28"", type text}, {""Column29"", type text}, {""Column30"", type text}, {""Column31"", type text}, {""Column32"", type text}, {""Column33"", type text}, {""Column34"", type text}, {""C" & _
        "olumn35"", type text}, {""Column36"", type text}, {""Column37"", type text}, {""Column38"", type text}, {""Column39"", type text}, {""Column40"", type text}, {""Column41"", type text}, {""Column42"", type text}, {""Column43"", type text}, {""Column44"", type text}, {""Column45"", type text}, {""Column46"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    Workbooks("Idaho.xlsm").Connections.Add2 "Query - Transform Sample File", _
        "Connection to the 'Transform Sample File' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transform Sample File"";Extended Properties=""""" _
        , "SELECT * FROM [Transform Sample File]", 2
    Workbooks("Idaho.xlsm").Connections.Add2 "Query - Parameter1", _
        "Connection to the 'Parameter1' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Parameter1;Extended Properties=""""" _
        , "SELECT * FROM [Parameter1]", 2
    Workbooks("Idaho.xlsm").Connections.Add2 "Query - Sample File", _
        "Connection to the 'Sample File' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Sample File"";Extended Properties=""""" _
        , "SELECT * FROM [Sample File]", 2
    Workbooks("Idaho.xlsm").Connections.Add2 "Query - Transform File", _
        "Connection to the 'Transform File' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transform File"";Extended Properties=""""" _
        , "SELECT * FROM [Transform File]", 2
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Location 1"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Location 1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Location_1"
        .Refresh BackgroundQuery:=False
    End With