Cannot open URL using QueryTable code

[Mr. Snrub]

I am trying to download a CSV file through a QueryTable in VBA. I don't know why this is occurring because I can download the CSV file just fine when I use a browser. The path to the CSV file is:

https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html?whichButton=CSV

The VBA code to access the CSV file is the following:

        Dim tsp_url As String
        tsp_url = "https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html?whichButton=CSV"
      
        With Sheets("RawData").QueryTables.Add(Connection:= _
          "TEXT;" & tsp_url, Destination:=Range("A1"))
          .FieldNames = True
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .RefreshStyle = xlOverwriteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .TextFilePromptOnRefresh = False
          .TextFilePlatform = 437
          .TextFileStartRow = 2
          .TextFileParseType = xlDelimited
          .TextFileTextQualifier = xlTextQualifierDoubleQuote
          .TextFileConsecutiveDelimiter = False
          .TextFileTabDelimiter = True
          .TextFileSemicolonDelimiter = False
          .TextFileCommaDelimiter = True
          .TextFileSpaceDelimiter = False
          .TextFileColumnDataTypes = Array(xlMDYFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat, xlGeneralFormat)
          .TextFileTrailingMinusNumbers = True
          .Refresh BackgroundQuery:=False
        End With

The connection fails with the message, "Sorry, we couldn't open (the url)." and it raises error 1004. As I said, the CSV file downloads just fine when I enter the URL in a web browser. How do I fix this?

I am using Microsoft Office Professional Plus 2016. Please help.

 

[John_w]

Try this:

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html?whichButton=CSV" _
        , Destination:=Range("$A$1"))
        .Name = "index.html?whichButton=CSV"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[Mr. Snrub]

No that did not work. I am still getting the error.

 

[Mr. Snrub]

I have just run this using Excel 2007, and it worked!! So why would it work in Excel 2007 but not in Excel 2016? Is there a configuration setting I'm missing?