| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"New Web Query"
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"New Web Query"
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"New Web Query"
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"New Web Query"
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 "samprince" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"New Web Query"
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"New Web Query"
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 "samprince" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"New Web Query"
Thanks, VBA Help quotes: Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure" That would fulfill the my_procedure after 15 seconds. Therefo 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
"New Web Query"
you can find some discussion here on looping it.
http://tinyurl.com/oalkb -- Don Guillett SalesAid Software "samprince" wrote in message ... Thanks, VBA Help quotes: Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure" That would fulfill the my_procedure after 15 seconds. Therefo 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "Invalid Web Query" error on opening a 2003 worksheet | Excel Discussion (Misc queries) | |||
| "New Web Query" always refreshes data from website (3 times) | Excel Worksheet Functions | |||
| "New Web Query" function | Excel Discussion (Misc queries) | |||
| "New Web Query" function | Excel Discussion (Misc queries) | |||
Linear Mode
