"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.

"samprince" wrote:

>
> 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.
>
>
> --
> samprince
> ------------------------------------------------------------------------
> samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
> View this thread: http://www.excelforum.com/showthread...hreadid=539307
>
>

[Don Guillett]

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

--
Don Guillett
SalesAid Software
[email protected]
"samprince" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> samprince
> ------------------------------------------------------------------------
> samprince's Profile:
> http://www.excelforum.com/member.php...o&userid=34168
> View this thread: http://www.excelforum.com/showthread...hreadid=539307
>

[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 you copy a column for instance, you would surely have to change the VB code 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 free 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 copy (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)

--
Don Guillett
SalesAid Software
[email protected]
"samprince" <[email protected]> wrote in
message news:[email protected]...
>
> 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 you
> copy a column for instance, you would surely have to change the VB code
> 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 free
> 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 copy
> (C3:C15) & (E3:E15) to columns (I3:I15) & (J3:J15).
>
>
> Thanks for your help.
>
>
> --
> samprince
> ------------------------------------------------------------------------
> samprince's Profile:
> http://www.excelforum.com/member.php...o&userid=34168
> View this thread: http://www.excelforum.com/showthread...hreadid=539307
>

[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

--
Don Guillett
SalesAid Software
[email protected]
"samprince" <[email protected]> wrote
in message news:[email protected]...
>
> 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?
>
>
> --
> samprince
> ------------------------------------------------------------------------
> samprince's Profile:
> http://www.excelforum.com/member.php...o&userid=34168
> View this thread: http://www.excelforum.com/showthread...hreadid=539307
>

[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

--
Don Guillett
SalesAid Software
[email protected]
"samprince" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> samprince
> ------------------------------------------------------------------------
> samprince's Profile:
> http://www.excelforum.com/member.php...o&userid=34168
> View this thread: http://www.excelforum.com/showthread...hreadid=539307
>