How to Get Data from Web and make sure that the existing table is updated with the new data?

[hkaraca]

Dear Fellow Excel Users,

I need help with getting data externally from web and also keeping the previous data prior to the refresh.

My external table/data has 10 rows reporting values per day (10 days data table).

I can link the table to my sheet, however what I can not do is to create an offlinek, year to date table in the sheet which captures the rolling 10 days (everytime it is refreshed) without losing the prior days.

For ex: External bank Data
Day Ratio
02/05/2014 0.15%
02/04/2014 0.13%
02/03/2014 0.14%
01-31-2014 0.23%
01-30-2014 0.16%
01-29-2014 0.16%
01-28-2014 0.18%
01-27-2014 0.19%
01-24-2014 0.19%
01-23-2014 0.21%

Above table changes on a daily basis, only reporting the last ten days.

How can I capture this data on a rolling basis, i.e. in a year to date format, so that everytime I refresh, the internal table gets updated with the new data.

Thank you very much for your valuable support.

Haluk

 

[John_w]

Here's one way. Put this code in the worksheet module of the sheet containing the web query. When the web query is refreshed it copies the retrieved data to the cells below.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim qt As QueryTable
    Dim destCell As Range
    
    If QueryTables.Count = 0 Then Exit Sub

    Set qt = QueryTables(1)
    If Not Intersect(qt.Destination, Target) Is Nothing Then
        Set destCell = Cells(Rows.Count, qt.Destination.Column).End(xlUp)
        If Not IsEmpty(destCell.Value) Then Set destCell = destCell.Offset(1, 0)

        Application.EnableEvents = False
        destCell.Resize(qt.ResultRange.Rows.Count, qt.ResultRange.Columns.Count).Value = qt.ResultRange.Value
        Application.EnableEvents = True
    End If

End Sub

If instead you want to copy the retrieved data to another sheet then simply qualify the destCell range with a sheet reference, like this:

        Set destCell = Sheets("Another_Sheet").Cells(Rows.Count, "A").End(xlUp)

 

[hkaraca]

Hello John,

Thank you very much for your help. This is amazing. But I was not able to get it work (not surprisingly given my limited VBA knowledge).

I did open a new module in the Workbook and saved the code. But I am not sure where it pastes the data updated in the range.

My external data range is A3 to B12.

And I want to have a horizantol feed into for example from D3. So, that the days get updated on a horizantal line everytime I do refresh, without losing the previous dates.

I am not sure if this makes sense but I really appreciate your helps. I did look into this for a bit but can not work it out with functions/formulas only.

Thank you again.
Regards,
Haluk

 

[John_w]

I did open a new module in the Workbook and saved the code. But I am not sure where it pastes the data updated in the range.

It sounds like you've put the code in the wrong place, particularly if nothing happens when the data updates. The code should go in the worksheet module of the sheet containing the external data query - see http://www.contextures.com/xlvba01.html#Worksheet.

When the data updates the code copies the data to the next cells below.