Excel – How to add Excel Web Data, using refresh, into new column or worksheet without overwriting old data

microsoft excel

I am using Excel’s web data to move a table of values from a website into Excel. My refresh period is every 10 minutes. The issue with refresh for my purposes is it overwrites the old values with the new. I need to preserve the table data taken every 10 minutes (I’ll plot this) so the progression of values matters (not just what the current value is). I'll run this while I'm at work so it must be automated.

Preferably the data would add onto a single worksheet but if it requires a new worksheet with every data dump, that’s okay too.

Does anyone know if Excel Web Data can do this? Or something like it? VBA is out because the HTML is too difficult to navigate (for me) and the table has too many unique fields I would need to call and iterate through.



Best Answer

  • Excel's Web Query tool is not capable of preserving the previously fetched data.

    However, using VBA, it is quite easy to automatically copy the data from the query table in Excel every time the Web Query refreshes it.

    Follow these steps to set up a workbook to demonstrate the technique:

    1) Create a new workbook with two worksheets, WebQuery and USD.

    2) Select cell A1 of sheet WebQuery and start a new Web Query using the address https://www.xe.com/currencyconverter/.

    3) Scroll down to the XE Live Exchange Rates table and import it.

    Screenshot of Web Query creation

    4) In the ThisWorkbook module, add this code:

    ' Module     : ThisWorkbook
    ' Version    : 0.1.0
    ' Part       : 1 of 2
    ' References : N/A
    ' Source     : https://superuser.com/a/1331097/763880
    Option Explicit
    Private qtExchangeRates As New clsQueryTable
    Private Sub Workbook_Open()
      qtExchangeRates.InitEvents Worksheets("WebQuery").QueryTables(1)
    End Sub

    5) Create a new Class Module named clsQueryTable and place this code in it:

    ' Module     : Class Module clsQueryTable
    ' Version    : 0.1.0
    ' Part       : 2 of 2
    ' References : N/A
    ' Source     : https://superuser.com/a/1331097/763880
    Option Explicit
    Public WithEvents QueryTable As QueryTable
    Private Sub QueryTable_AfterRefresh(ByVal Success As Boolean)
      If Success Then
        Dim varUSDExchangeRates As Variant
        varUSDExchangeRates = Me.QueryTable.WorkbookConnection.Ranges(1).Columns(2).Value2
        varUSDExchangeRates(LBound(varUSDExchangeRates), 1) = Now
        Worksheets("USD").Range("A1").Offset(Rows.Count - 1).End(xlUp).Offset(1) _
          .Resize(ColumnSize:=1 + UBound(varUSDExchangeRates) - LBound(varUSDExchangeRates)) _
          = Excel.WorksheetFunction.Transpose(varUSDExchangeRates)
        ' Query failed or was cancelled
      End If
    End Sub
    Sub InitEvents(QueryTable As Object)
      Set Me.QueryTable = QueryTable
    End Sub

    6) Set the Web Query to automatically refresh every minute.

    7) Save and close the workbook

    When you re-open the workbook the Web Query will start refreshing every minute and the first data column of the XE Live Exchange Rates table (the current USD exchange rates) will be stored in the USD sheet.

    This demo just extracts one column of data, but any/all of the table's data can be copied in the same way.

    Note that the demo will work correctly as is with any table from any URL, since the code automatically adjusts for the size of the table.

  • Related Question