Import html web tables

Question

I want to import data from a web page using VBA, but I have a 1004 error. This is the code that I use:

Sub QueryStarter()
'This is an adaptation from some code found at:
'https://stackoverflow.com/questions/19306832/web-query-vba-refresh
    
    Dim url As String
    url = "URL;https://www.xe.com/currencytables/?from=USD&date=2020-12-02"
    With Worksheets("Sheet1").QueryTables.Add(Connection:=url, Destination:=Worksheets("Sheet1").Range("A1"))
        .Name = "My Query"
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False   <-----this line gives me the error
    End With
      
End Sub

I need to import a table from that website. If someone have experience using VBA query, but no with the IE application, maybe with HTML. I would really appreciate if you can help me with that.

When I run your code, it imports the table successfully, without any errors. — Domenic, Dec 06 '20 at 15:44
Does the error occur the first time you run it, or only on subsequent runs? — Domenic, Dec 06 '20 at 15:46

Import html web tables

0 Answers0