Appending Data from Web Data Connection to a Table

[diplomatt13]

There is a CSV data source online that publishes the latest data on COVID-19 cases in Austria.

COVID-19: Überblick - data.gv.at

The CSV file consists of one row with the latest number (cases, recovered, hospitalized, deaths, etc). I connected the data to an Excel file as a data connection (from web) with the URL of the CSV file.

https://info.gesundheitsministerium.at/data/AllgemeinDaten.csv

This imports the single row of data correctly. However, I'd like to take that data, if it has changed from the last refresh, and add it as a row in a separate table in the same Excel file. The "timestamp" column/field in the CSV data connection will indicate if the data is new/unique. The data is updated approximately 2-3 times a day.

I can manually refresh and then manually copy and paste the data into the separate table. However, is there a way to automate this or make this less painful?

 

[John_w]

Welcome to MrExcel forums.

If I understand you correctly, you've used a Power Query to import the csv data into a worksheet, creating a table, and you want to copy the data to another table if the timestamp has changed when the data is refreshed. If so, see if this macro helps. It refreshes the query (in a table named "AllgemeinDaten" on the active sheet) and, if changed, copies the data to the bottom of a table named "MasterTable", also on the active sheet.

Public Sub Refresh_Data_Update_Master_Table()
    
    Dim WebQueryTable As ListObject
    Dim MasterTable As ListObject
    Dim WebDataRow As Range, MasterTableLastRow As ListRow
    
    Set WebQueryTable = ActiveSheet.ListObjects("AllgemeinDaten")
    Set MasterTable = ActiveSheet.ListObjects("MasterTable")
    
    WebQueryTable.QueryTable.Refresh BackgroundQuery:=False
       
    If WebQueryTable.ListColumns("Timestamp").DataBodyRange.Item(1).Value > MasterTable.ListColumns("Timestamp").DataBodyRange.Item(MasterTable.DataBodyRange.Rows.Count).Value Then
    
        'Timestamp of web data is later than last row of Master Table, so copy web data row to bottom of Master Table
        
        Set WebDataRow = WebQueryTable.ListRows(1).Range
        Set MasterTableLastRow = MasterTable.ListRows.Add
        WebDataRow.Copy MasterTableLastRow.Range
    
    End If
    
End Sub

 

[diplomatt13]

Thank you so much! That did precisely what I wanted! I really appreciate the help.

 

[John_w]

Thanks for your feedback and I'm pleased it works for you.

The macro must be run manually to refresh the web query and update the "MasterTable" if the data has changed. It is possible to use Excel's automatic periodic refresh to run a modified version of the macro so that you don't have to run it manually.

Replace the previous code (which should be in a standard module (e.g. Module1) with this code:

Public Sub Manual_Refresh_Data_Update_Master_Table()
    
    Dim WebQueryTable As ListObject
    
    Set WebQueryTable = ActiveSheet.ListObjects("AllgemeinDaten")
    Application.EnableEvents = False
    Update_Master_Table ActiveSheet, WebQueryTable
    Application.EnableEvents = True
    
End Sub


Public Sub Update_Master_Table(DataSheet As Worksheet, WebQueryTable As ListObject)
    
    Dim MasterTable As ListObject
    Dim WebDataRow As Range, MasterTableLastRow As ListRow
    
    Set MasterTable = DataSheet.ListObjects("MasterTable")
        
    If WebQueryTable.ListColumns("Timestamp").DataBodyRange.Item(1).Value > MasterTable.ListColumns("Timestamp").DataBodyRange.Item(MasterTable.DataBodyRange.Rows.Count).Value Then
    
        'Timestamp of web data is later than last row of Master Table, so copy web data row to bottom of Master Table
        
        Set WebDataRow = WebQueryTable.ListRows(1).Range
        Set MasterTableLastRow = MasterTable.ListRows.Add
        WebDataRow.Copy MasterTableLastRow.Range
    
    End If
    
End Sub

Put this code in the sheet module of the sheet containing the "AllgemeinDaten" table:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WebQueryTable As ListObject
    
    Set WebQueryTable = Me.ListObjects("AllgemeinDaten")
    If Not Intersect(Target, WebQueryTable.DataBodyRange) Is Nothing Then
        Application.EnableEvents = False
        Update_Master_Table Me, WebQueryTable
        Application.EnableEvents = True
        MsgBox "Data refreshed at " & Now
    End If
    
End Sub

Next, set up the automatic periodic refresh interval on the web query, via Data tab -> Connections -> Properties:

The Refresh every 1 minute is just for testing, as is the MsgBox line in the Worksheet_Change procedure.

You can still refresh and update manually by running the Manual_Refresh_Data_Update_Master_Table macro.