Save old data when refreshing web data

Hello everyone,

I was wondering if the data being pulled by excel from the web, could be saved before refreshing it every minute.

Looking around I got this macro which saves the new data in c3 to c4 and moves the existing cells with data one cell lower. I used a dynamic named range for this as well.

This macro works great manually.

Need help to figure out how to run it through an if function only IF there is a change in value. Something like If C4 <> C3 then....

But for some reason the macro would not run through if function and I cannot figure out why.

Have attached the file here. Any help would be appreciated.

Thanks a lot.

Originally Posted by hansen

Hello everyone,

I was wondering if the data being pulled by excel from the web, could be saved before refreshing it every minute.

Looking around I got this macro which saves the new data in c3 to c4 and moves the existing cells with data one cell lower. I used a dynamic named range for this as well.

This macro works great manually.

Need help to figure out how to run it through an if function only IF there is a change in value. Something like If C4 <> C3 then....

But for some reason the macro would not run through if function and I cannot figure out why.

Have attached the file here. Any help would be appreciated.

Thanks a lot.

Come on, people. SOmeone may know how this can be done. Everything that is to be done is in the macro. Only one line of code needs to be added. Sincerely appreciate any help.

Thanks.

Look at the QueryTable BeforeRefresh and AfterRefresh events.

Originally Posted by T-J

Look at the QueryTable BeforeRefresh and AfterRefresh events.

Thanks for the help. Really appreciate it. I googled the afterrefresh events. I know its the perfect solution. But it contains VB which I am ignorant about.

I gathered some code which works fine. I am trying to fire it through an IF function if possible. Something like if Cell C3 <> C4 then the macro runs, but it isn't happening. If I only knew why, I would try it through a formula coz VB is something out of my reach completely.

Thanks a lot for your help.

To implement the BeforeRefresh and AfterRefresh solution, proceed as follows:

1. Add a class module (Class1) and put in the following code:

Public WithEvents qt As QueryTable

Dim C3BeforeRefresh As Variant

Private Sub qt_BeforeRefresh(Cancel As Boolean)
    C3BeforeRefresh = Worksheets("Live").Range("C3").Value
End Sub

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
    With Worksheets("Live")
        If .Range("C3").Value <> C3BeforeRefresh Then
            .Range("C4").Insert Shift:=xlDown
            .Range("C4").Value = C3BeforeRefresh
        End If
    End With
End Sub

2. Add the following code at the top of your existing Module2 module:

Dim Query As New Class1

Sub Initialise_Query()
    Set Query.qt = ThisWorkbook.Sheets("Live").QueryTables(1)
End Sub

3. Run Initialise_Query once. You could automate this by calling it from the Workbook_Open event in the ThisWorkbook module, like this:

Private Sub Workbook_Open()
    Initialise_Query
End Sub

A simpler solution is to add a second sheet and have the Worksheet_Change event on the Live sheet update that second sheet if the C3 cells are different.

Wow, Thank you very much. It is really kind of you, to help out.

Oh, I still managed to not make it work. It gave a debug error 424 for this line

Set Query.qt = ThisWorkbook.Sheets("Live").QueryTables(1)

So I tried changing the name of the web query to QueryTables(1) and QueryTables and it still did not work.
I think what I did was wrong, but I have no clue about how VB functions. Kindly advise if possible. Thanks

My code works in Excel 2003, but I think your workbook must be Excel 2007.

Maybe the problem is http://www.ozgrid.com/forum/showthread.php?t=89168. If so, try changing:

Set Query.qt = ThisWorkbook.Sheets("Live").QueryTables(1)

To:

Set Query.qt = ThisWorkbook.Sheets("Live").ListObjects(1).QueryTable

That's an educated guess based on that linked page, but I can't test it because I don't have Excel 2007. Also, ListObjects(1) assumes your web query is in the first ListObject. You could run the Excel2007Connections() code on that page to check this.

Originally Posted by Chippy

My code works in Excel 2003, but I think your workbook must be Excel 2007.

Maybe the problem is http://www.ozgrid.com/forum/showthread.php?t=89168. If so, try changing:

Set Query.qt = ThisWorkbook.Sheets("Live").QueryTables(1)

To:

Set Query.qt = ThisWorkbook.Sheets("Live").ListObjects(1).QueryTable

That's an educated guess based on that linked page, but I can't test it because I don't have Excel 2007. Also, ListObjects(1) assumes your web query is in the first ListObject. You could run the Excel2007Connections() code on that page to check this.

Will try this and see if it works. Thanks for your help. Sincerely appreciate it.