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.
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