Dim rCell As Range
Dim lIDStart As Long
Dim qt As QueryTable
Const sIDTAG = "&ID="
Application.EnableEvents = False
Set qt = Sheet1.QueryTables(1)
'loop through site IDs
For Each rCell In Sheet2.Range("A2:A3").Cells
'find the id parameter in the web query connection
lIDStart = InStr(1, qt.Connection, sIDTAG)
'if found, change the ID
If lIDStart > 0 Then
qt.Connection = Left$(qt.Connection, lIDStart - 1) & sIDTAG & rCell.Value
Else 'if not found, add the id onto the end
qt.Connection = qt.Connection & sIDTAG & rCell.Value
'refresh the query table
On Error Resume Next
'if the web qeury worked
If Err.Number = 0 Then
'write the date
rCell.Offset(0, 1).Value = Sheet1.Range("A2").Value
'write the price
rCell.Offset(0, 2).Value = Sheet1.Range("A4").Value
Else 'if there was a problem with the query, write an error
rCell.Offset(0, 1).Value = "Invalid Site"
rCell.Offset(0, 2).Value = ""
On Error GoTo 0
Application.EnableEvents = True
Hi Dick, have done as you mentionned, got error on second line. would you mind send me your excel sheet.Many thanks
Put your web query on a different page, then pull the data you need into your list on every refresh. Here's an example.
Thanks Dick. My problem is solved. I have reverse the sheet, that's why it was not working, now it is ok. You are great. Many thanks
Thanks for your prompt reply. Good idea of putting the query in other sheet. How about executing the queries for subsequent rows, do the previous query need to be deleted.I have tried your example but giving run time error 9. Where do I need to put the website url in the code. Again thanks for support
You have only one query and you don't need to delete it. The code just changes that one query for each row. On which line do you get the error? My sheets had a CodeName of Sheet1 and Sheet2 so make sure yours match or you change the code. For the URL, create the query manually on the other sheet for the first Site - the URL will be stored in the query and you won't need it in the code.