"New Web Query"

[samprince]

I just did a search for new web query and nothing came up so please
don't flame me [=

Excel always comes up with the goods and regulary fulfills the exact
requirement of it you wish for.

I have solved a problem 95% but the final 5% I would like your help on
if possible.

I have an excel document, which I have worked out how to download data
from the internet via a web query every ten minutes. However, when this
refreshes, it overwrites what was collected in the previous ten
minutes.

What I wish to accomplish is for every ten minutes the data to refresh
but 'add the new data' into the next column. This will therefore give
me a large set of data which I can analyse over time.

Any comments, suggestions or the answer to my woes will be much
appreciated.

Many thanks,
SP.

 

[Miguel Zapico]

Regardless of how you are running the query (manually or through a macro),
you may change the approach of what data to relocate. Instead of trying to
get the data from the query in a new column, copy the existing data to the
next available column.
You can do it manually once while recording a macro, edit it to assure that
it will do what you expect and use the macro before you run the web query.

Hope this helps,
Miguel.

 

[Don Guillett]

Write a macro to move to a different page to the next available column or to
insert before the first column.

 

[samprince]

Thanks for your advice, however. Macros are not one of my forte's.

I can set a macro to run and so someting incredibly basic, yet when yo
copy a column for instance, you would surely have to change the VB cod
to make it say instead of copy to column x copy to 'next free colum'

I would also need to include into the macro to to copy to next fre
column each time the web query refreshes/data in cell $x$x changes.

So for instance typically, I will need to copy column (C3:C15)
(E3:E15) to the next free columns.

In the first instance I would be copying into (G3:G15) & (H3:H15) but
10 minutes later when the web query refreshed I will need to cop
(C3:C15) & (E3:E15) to columns (I3:I15) & (J3:J15).


Thanks for your help

 

[Don Guillett]

You should always stay in the original thread. I couldn't find one. But you
would need a macro to copy the datat to the next available column

something like
lc=cells(1,columns.count).end(xltoleft).column+1
range(cells(3,"c"),cells(15,"c")).copy cells(3,lc)

 

[samprince]

Thanks for your advice.

How would I be able to incorporate a timed action into the macro or
have it copy the cells into the next available column when the cell
data is auto refreshed by the web query each 10 minutes?

 

[Don Guillett]

Instead of an auto refresh in the query use an
ONTIME macro to copy the columns and then refresh the query.
look in vba help index for ONTIME

 

[samprince]

Thanks,

VBA Help quotes:

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

That would fulfill the my_procedure after 15 seconds.

Therefore:
Application.OnTime Now + TimeValue("00:10:00"), "my_Procedure"

Would be the TimeValue I would need. Would this be recurring 10 minute
intervals or Just once?

 

[Don Guillett]

you can find some discussion here on looping it.

http://tinyurl.com/oalkb