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

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,075
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

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
 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

Well-known Member
Joined
Oct 24, 2015
Messages
7,075
sorry mate, I am away (and against) from vba so I can't help
 

Watch MrExcel Video

Forum statistics

Threads
1,113,930
Messages
5,545,083
Members
410,652
Latest member
Zot
Top