5

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.

Thanks,

TMME

  • Are you looking for Refresh method only? – Rajesh S Jun 12 '18 at 9:41
  • Not necessarily, but hopefully the method includes using the Excel Web Data Query tool which I find intuitive and hands off (if used correctly). – The_MN_MechE Jun 12 '18 at 17:32
  • I can suggest you VBA code with refresh method, what you need to do is change the Sheet name every time before you run the Macro, or you need to do lil alter in code, create variable for Sheet name. Please confirm will it work for you then only I'll post it here. – Rajesh S Jun 13 '18 at 6:48
  • This sounds promising, I know web data runs its own script to fetch the data. Do I modify that or use VBA to call refresh in web data? Also, is it possible to set columns as a variable and move through the next iteration with each refresh? – The_MN_MechE Jun 13 '18 at 11:42
  • Yes it's possible,, but better first you solve the basic issue. – Rajesh S Jun 14 '18 at 6:17
3

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)
  Else
    ' 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.

  • I never thought to grab the refresh using VBA, nice! I’ll sit down and try this out and let you know if this works for me. Thanks for putting this together. – The_MN_MechE Jun 13 '18 at 23:38
  • It gives me a "User Defined Type Not Defined" error and highlights this line "qtExchangeRates As New clsQueryTable"? – The_MN_MechE Jun 15 '18 at 0:44
  • @The_MN_MechE The second block of code needs to be placed in a Class Module (Insert > Class Module) and the name of the class module must be "clsQueryTable". – robinCTS Jun 15 '18 at 5:09
  • So that worked but now I get "Run Time Error 9 Subscript out of range" as it highlights this line "qtExchangeRates.InitEvents Worksheets("WebQuery").QueryTables(1)" which means it's trying to find/call something that it can't find. I've got the spelling right of my workbooks. – The_MN_MechE Jun 16 '18 at 2:02
  • Let us continue this discussion in chat. – robinCTS Jun 21 '18 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.