• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Importing data from web page

Ranjith kumar

New Member
Hello Everyone!
I'm getting external data from webpage which gives me data in the form of a table.
My query is - whenever i get new data in that tables, old data should be moved down and new data should be placed there, but it is replacing the old data with new data.
How to move the old data..help me with this please
Regards,
Ranjith
 
Hi, Ranjith kumar!

In the ActiveSheet.QueryTables.Add connection method, check for the property RefreshStyle. It can have any of these 3 values:
xlInsertDeleteCells: 1, default
xlInsertEntireRows: 2
xlOverwriteCells: 0

I'd bet on that you have the last one where you should have the first one.

Regards!
 
Hello SirJB7,
I tried all the three refresh methods but the result is same.
I'm attaching a sample file.
Regards!
 

Attachments

  • custom rates.xlsm
    17.8 KB · Views: 5
Hi, Ranjith kumar!

Your file even if .xlsm it doesn't contain any macro, hence no ActiveSheet.QueryTables.Add connection method to set the RefreshStyle property. So how did you tested it with the three values?

Regards!
 
Hello SirJB7,
I din't use VBA for this, Instead there's an option for refresh methods(3 methods) in connection properties, I hope those 3 are the same mentioned by you in VBA..but i'm not sure about that.
Let me know if i'm wrong.
 
Hi, Ranjith kumar!

I have never used a query from a data connection without being run from a macro, so I can't happen to find where you say you can set those three values for the refresh style property. Would you take a screenshot and upload it? Thank you.

Regards!
 
Hi, Ranjith Kumar!

Well, in my everyday more hated 2013 version (including because of the multiple document interface instead of the previous single one -MDI vs. SDI-) I can't get that, just what's below, i.e., all but the chance to set the 3 values for the damned parameter.

However if what you need is to keep the history of previous values and don't delete/clear any one, I'd suggest you to take this path:
a) change the file from .xlsx to .xlsm
b) keep the query as today
c) create a new worksheet to hold historical information in columns Date, Currency, Import, Export
d) build a macro that after retrieving the data updates this new worksheet.

Is it suitable for you?

Regards!
Importing data from web page (for Ranjith kumar at chandoo.org).png
 
Last edited:
Hi JB7,
I can follow what you said, but it is manual work contrary to which i need i.e automation.
Can u suggest me a vba code in such a way that - After refreshing,before updating the cells,if there is new data then move down or move to other sheet whatever but store the old data and then paste the new data there..
 
Hi, Ranjith kumar!

Neither today surely nor tomorrow I guess, then weekend comes, so it'd be next week, from Tuesday in advance, but not exactly when.
In the meanwhile maybe you want to give a look at these links, I think they all have something related to this:
http://chandoo.org/forum/threads/macros-web-query-huge-headache-can-anyone-help-me.4175/
http://chandoo.org/forum/threads/specific-data-and-html-beyond-excel-39-s-capabilities.9640/
http://chandoo.org/forum/threads/copy-data-from-a-website-to-excel.12897/

Regards!
 
Hi, Ranjith kumar!
Sorry for the late answer but been traveling the last couple of weeks. However, glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
Back
Top