Load cumulative data from Web .csv data Query to Excel table for a different Parameter Date each time

[Manerlao]

Hi all,

I hope everyone is keeping well during these difficult times!

I have an Excel Power Query question and I hope someone is able to help me!


I created several connections in Power Query which are currently 'connections only' within Queries & Connections.

All data is being imported from the web where they are currently .csv files.

i.e.
\Https://www[.]website.com/datafile1_2020-01-01.csv
\Https://www[.]website.com/datafile1_2019-01-01.csv
\Https://www[.]website.com/datafile2_2020-01-01.csv
...

I have different a number of datafiles from 1-10 that are across different years, so I have made the Parameter for the date.

When I load the table to Excel (right-click query > load-to > current worksheet A1) it works fine for all 10 queries.
But then I change the parameter for a different date, and the previous data is lost and the new data for the new date is imported.

What I need is for the data to be pasted under the most recent import from the data query.
Perhaps my methodology is wrong? Is there something else I should do?

Please let me know if anyone is able to help! Thank you!

Best regards,
M.

 

[sandy666]

one way you can
after first load right click on the result then convert to range
then change parameter, load and do the same

 

[Manerlao]

Dear Sandy,

Thank you for this! Yes, that's what I needed! However, do you happen to know how I can loop this through all my queries and paste the queries in the respective sheet under the latest data in the worksheet?

So basically I have a parameter which is for the report date which I change and then I need to import all the data into their respective worksheets from the queries.

I need to loop it:

Run query named "Report1Query" paste data in worksheet "Report1" under the latest data and ensure it's pasted as a range so the query remains a conenction
Run query named "Report2Query" paste data in worksheet "Report2" under the latest data

Here is my current code which works to run the query and paste data into one sheet in cell A1.

 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Report1Query;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Report1Query]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Report1Query"
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.ListObjects("Report1Query").Unlist
    
    Columns("A:A").Select
    Selection.NumberFormat = "yyyy-mm-dd"
    
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
    End With
    Range("A1").Select
End Sub

Please let me know what you think is best. Thank you so much again for your guidance with this.

 

[sandy666]

sorry mate, I am away (and against) from vba so I can't help

 

[Manerlao]

sorry mate, I am away (and against) from vba so I can't help

No problem Sandy! Stay safe and healthy!
I hope someone else would be able to help here.

 

[sandy666]

I'm sure someone will help
have a nice day