Appending Data from Web Data Connection to a Table

diplomatt13

New Member
Joined
Apr 24, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.

VBA Code:
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
 
Upvote 0
Thank you so much! That did precisely what I wanted! I really appreciate the help.
 
Upvote 0
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:

VBA 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:
VBA Code:
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:
Connection Properties.PNG


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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top