Formatting updated web data

[ecokev]

Hi there folks,

I am using Excel to download web data and then go on to format it and plot the information over time.

The web data is a comma separated table. I can get the data into Excel via the Web Query tool, however, it lumps all the information into one column, when it really needs to be spread over three.

I use the text-to-columns function to split the data into 3 columns, however, each time the web data is updated, all the information is put back into one column. Is there a way that I can retain the 3 column format, or perhaps a way to automate the text-to-columns function to occur each time the web data is updated.

Secondly, once I have the updated data, I would like to have it plot it against days of the month. Is there a way this can automatically happen?

If I use the following set up, it doesn't work.

1-Jun-10 | =A1
2-Jun-10 | =A1
3-Jun-10 | =A1

When the web data is updated, say in cell A1, it would change the value of the first two days. I need to keep the old value, the value that was current on that day, so that I can plot trends over time.

Thanks for any help you are able to offer me! I know it's probably pretty confusing to understand what I need, given my description, but if you need any clarification please don't hesistate .

Kev

 

[Joe4]

Welcome to the Board!

Regarding your first question, using "Record Macro" you can record your steps of applying the text-to-columns routine, so now you will have the VBA code necessary to do this. Then you can just run this macro.

Depending on how your web data is upated (is it automatic? is someone doing it manually?), you may be able to put your VBA code above into an Event Procedure to automatically run this text-to-columns routine whenever the data is updated (an Event Procedure is VBA code that is automatically triggered upon some event happening, like data being updated, etc - see here for more details: http://www.cpearson.com/excel/Events.aspx). You may be able to use the Worksheet_Sheet or Worksheet_Calculate events, but it really depends on how the data is being updated.