Rectangle 27 0

Excel Dynamic Web query specific data from table and transpose the result using VBA code?


Sub GetPrices()

    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
        End If

        'refresh the query table
        On Error Resume Next
            qt.Refresh False

            '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 = ""
            End If
        On Error GoTo 0
    Next rCell

    Application.EnableEvents = True

End Sub

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.

Note