New to Excel - web queries?


New Member
Nov 11, 2005

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. :)


Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I have done this archiving the NAVs of mutualfunds in which I am interested

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"

Selection.Clear 'I first clar the old data
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()
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)
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
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.
Upvote 0

Forum statistics

Latest member
Gian Carlos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back