How to download .xls and import data?

Question

I have an Excel file with some sheets and I want one of them to be my raw data called "Data" which is on the following url. https://www.bestinver.es/WS/Api/Profitability/DownloadExcelLiquidity?productId=1

I searched and I have something like this:

Sub getData()
    Dim str As String
     
    'Delete existing data
    Sheets("Data").Activate 'Name of sheet the data will be downloaded into. Change as required.
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
     
    'Download stock quotes. Be patient - takes a few seconds.
    str = "https://www.bestinver.es/WS/Api/Profitability/DownloadExcelLiquidity?productId=1"
    QueryQuote:
                With Sheets("Data").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("Data").Range("a1"))
                    .BackgroundQuery = True
                    .TablesOnlyFromHTML = False
                    .Refresh BackgroundQuery:=False
                    .SaveData = True
                End With
     
    Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)
     
    Sheets("Data").Columns("A:B").ColumnWidth = 12
    Range("A1").Select
End Sub

It downloads the data but it reads as it was a .csv and the data is .xls so it throws an error.

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Nov 11 '20 at 13:50

ASH · Accepted Answer · 2020-11-11T22:53:04.117

This will download the file for you.

Sub DownloadFile()

Dim myURL As String
myURL = "https://www.bestinver.es/WS/Api/Profitability/DownloadExcelLiquidity?productId=1"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.send

If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:\your_path_here\your_file.xlsx", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

End Sub

How to download .xls and import data?

1 Answers1