Almost same web query on every sheet

[JVD]

I want to launch almost the same web query on every sheet. The only thing that changes is a little thing in the webadres (=parameter). These parameters are in the A1 cel of each sheet.
Is it possible to do this automatically?

 

[Teeroy]

Build up a hyperlink by concatenation. Try the following as an example:
in B1: =HYPERLINK("http://" &A1&".com")
in A1: "www.google"

now click on the link in B1

 

[JVD]

Yes, but I now it just opens the webpage?
I want that the webquery would be launched on every sheet, but for each sheet a different parameter (which is standing in cel A1)

 

[Teeroy]

Sorry, my mistake. I misinterpreted as wanting to send a query formatted http request.
You can do it with VBA. The example below has the entire query URL in A1 but you can just as easily concatenate one. You can call the macro from each worksheet_activate event and it will select the ranges in the activesheet and refresh the data whenever you select a different sheet tab.

Sub Web_Query()
   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;" & Range("A1"), _
         Destination:=Range("B1"))
      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With
End Sub

 

[JVD]

Thanks for your answer. Now I'm already a step forwards, but I just doesn't understand yet how I can make it to do it for every sheet automatically? But thank you already!

 

[JVD]

*

but I just don't understand

(spelling mistake, can't find edit button?)

 

[John_w]

This creates the web query posted by Teeroy on all sheets:

Sub Web_Query_All_Sheets()

    Dim ws As Worksheet
    Dim qt As QueryTable
    
    For Each ws In Worksheets
        With ws
            Set qt = .QueryTables.Add(Connection:="URL;" & .Range("A1").Value, Destination:=.Range("B1"))
            With qt
                .BackgroundQuery = True
                .TablesOnlyFromHTML = True
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
        End With
    Next
    
End Sub

If that particular web query doesn't work or retrieves the wrong web data (it is missing several properties which specify exactly which data should be retrieved), do a manual web query with the macro recorder and incorporate the code generated into the With qt block of code above.

 

[JVD]

Yes, that works indeed, but there is a little problem: when there is a wrong URL, it gives an ERROR and then it stops. The sheets after the one with the wrong URL aren't done anymore. Is there a way to say if there is a wrong URL or an ERROR, it don't have to stop, but just go one and still do the other sheets?

 

[JVD]

Never mind, thanks to you both!