Dynamic Web Query

[ourmick]

Is it possible to use VBA to run a web query which uses a different URL address each time?

The relevant URL can always be found in cell "A1" of sheet "address"
and the tables from the web query need to be downloaded to cell "A1" of sheet "Download"

Thanks very much
Mick

 

[John_w]

Sub Macro1()
    With Sheets("Download").QueryTables.Add(Connection:="URL;" & Sheets("Address").Range("A1").Value, _
        Destination:=Sheets("Download").Range("A1"))
        .Name = "web_query"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Download").QueryTables(1).Delete
End Sub

Record a macro to get the specific web query property values for YOUR web query and use in the code instead of the values above.

 

[ourmick]

Perfect!!

Thanks so much John_w