New to Excel - web queries?

[tmcmahon]

Hi,

I'm trying to compile a list from a website that refreshes every 10 minutes. When the list refreshes, some items are dropped and others are added.

When I set up a web query to refresh, it will delete the old info and replace it with the information from the current site.

What I need is something that will automatically query the website every 10 minutes and add the information to my worksheet, rather than replacing my worksheet with the new information.

Does that makes sense and can anyone help me. If this question has already been posted, please direct me to the archived post. Also, I'm fairly new to Excel and so I need a response that is pretty step-by-step.

Thanks.

 

[venkat1926]

I have done this archiving the NAVs of mutualfunds in which I am interested
insheet2

In sheet1 I download the latest NAVs of ALL mutal funds daily.
iam giving you a sample
first i created the query to download a table from a webpage into sheet1 from A1and then run the set of subs I have given commens
=============
Dim cella As Range
Dim cellb As Range
Dim myrange As Range
Dim purprice As Single
Dim interval As Single
Dim purdate As Date
Dim gain As Single
Dim unita As Range
Dim unitb As Range
Dim mfcode As Range
Dim c As Range

Public Sub amfi_nav_download()
MsgBox "this procedure will take sometime click ok and wait"

Worksheets("sheet1").Select
Cells.Select
Selection.Clear 'I first clar the old data
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False ' aready created
'query is run and the table is downloaded in sheet1 A!
Range(Range("a1"), Range("a65536").End(xlUp)).Select
End Sub

Public Sub nav1()
Worksheets("sheet2").Activate
On Error Resume Next
Set cella = Range("a6").End(xlToRight).Offset(0, 1)
'in sheet2 data is copied from sheet from A6 by the next line formula
'this goes into the last column as the previous column will have the data
'downloaded earlier-sort of archival-the name <navtable> is given name 'inthe sheet1 already.It can also be done in situ in this code also
cella = "=VLookup(a6, navtable, 3, False)"
'next few lines - the formula in A6 is coped down to the last row
Set cellb = Range("a6").End(xlDown).End(xlToRight).Offset(0, 1)
Set myrange = Range(cella, cellb)
cella.Copy
myrange.PasteSpecial
myrange.Copy
myrange.PasteSpecial xlPasteValues
myrange.NumberFormat = "0.0000"
'the next few line ---copies the heading from sheet1 to sheet2 with some format changes
cella.Offset(-1, 0) = "=vlookup(A6,navtable,6,false)"
cella.Offset(-1, 0).Copy
cella.Offset(-1, 0).PasteSpecial xlPasteValues
cella.Offset(-1, 0).NumberFormat = "dd-mmm-yy"
Application.CutCopyMode = False
cella.Offset(-1, 0).Columns.AutoFit
End Sub

now I have created a command button in sheet2
the command button click code is as follows

Private Sub CommandButton1_Click()
Application.EnableEvents = False
amfi_nav_download
nav1
Application.EnableEvents = True
End Sub
============
daily (or oce in 10 inutes in your case) you have only to click the commandbutton1 in sheet2. automatically the work is done
now you have only to save thefile every time.

this is only to show you that it can be done. but this sub is customised for my needs. You cannot just copy the codes but you have to modify them.
the lines beginning with apostrophe(') are comment lines and not part of the code.

If you wish to email me welcome.

hope you get the hand of it.

 

[tmcmahon]

Thanks so much!