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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.