Runtime Error QueryTables to download Web CSV

[CharlesB]

I am having trouble downloading a csv file from the internet and importing it into an excel sheet. The method I am using is the Querytable.add method

This code works

Sub Import_CSV_File_From_URL()

    
    Dim URL As String
       
    Sheets("Sheet2").Select
    
    URL = "https://example.com/filename" & _
        Format(Range("TWDate"), "yyyymmdd")
     
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & URL, Destination:=Range("A1"))
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
    
    ActiveSheet.QueryTables(1).Delete
    




 
End Sub

(url changed as it contains a auth key) - TWDate is a named cell which contains the date of the previous Sunday so that the url ends with the date to request the most recent weeks data.

But I am having difficulty with this code where I attempt to add 2 dates (so that the web generates a file which compares this weeks data with last week)

Sub Import_CSV_File_From_URL()


    
    Dim URL As String
       
    Sheets("Sheet2").Select
    
    URL = "https://example.com/filename" & _
        Format(Range("TWDate"), "yyyymmdd") & "&period_b=W" & Format(Range("LWDate"), "yyyymmdd")
     
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & URL, Destination:=Range("A1"))
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
    
    ActiveSheet.QueryTables(1).Delete
    




 
End Sub

Using this code I get a Runtime Error 5 Invalid procedure call or argument - on the line beginning With ActiveSheet.QueryTables.Add. Any Idea why? if I add the line Range("a1").Value = URL to effectively print my url to a cell I can see that the URL is correct and downloads if I just paste it into the browser so why is the macro failing?

Thanks in advance