Reading XLSX file from the website

[Kalmar]

Hi all,

I understand that this topic was already covered a lot, but I didn't find the answer for exactly my case. The following code works for other web sites, but doesn't work for the one below:
On this side https://indexes.nasdaqomx.com/<wbr>Index/History/NQLA5300 you could press the button "Export" and the xlsx file would be downloaded (the link to the file is https://indexes.nasdaqomx.com/<wbr>Index/ExportHistory/NQLA5300?<wbr>startDate=2013-12-08T00:00:00.<wbr>000&endDate=2014-12-08T00:00:<wbr>00.000&timeOfDay=EOD)

Please help!!!


The code below works for yahoo for example, but not for the link below:
Private Sub CommandButton1_Click()
Dim strTicker As String
strTicker = "NQLA5300"

' Compile the request URL with start date and end date
Dim strURL As String
strURL = "https://indexes.nasdaqomx.com/Index/ExportHistory/" & Symbol _
& "?startDate=2013-12-08T00:00:00.000&endDate=2014-12-08T00:00:00.000&timeOfDay=EOD.xlsx"

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

If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.responseBody
oStream.SaveToFile "c:\Temp\EODHist_20131208-20141208_NQLA5300.xlsx ", 2 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If

Set WinHttpReq = Nothing


End Sub



Of course I would prefer not to download the file, but just to get the data from it. The code below works for yahoo, but not for this site:



Private Sub CommandButton1_Click()

'Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual


Dim qt As QueryTable
Dim UrlRange As range
Set UrlRange = Sheets("Data").range("a1")


Dim strTicker As String
strTicker = "NQLA5300"


Dim URL As String
URL = "https://indexes.nasdaqomx.com/Index/ExportHistory/" & Symbol _
& "?startDate=2013-12-08T00:00:00.000&endDate=2014-12-08T00:00:00.000&timeOfDay=EOD.xlsx"

'QueryQuote:

With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, _
Destination:=UrlRange)
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

‘Deleting Qeurytable
For Each qt In Sheets("Data").QueryTables
qt.Delete
Next qt

qurl = ""



' Application.ScreenUpdating = True

Unload Me
End Sub

 

[pedrowave]

Hi Kalmar, what it is Symbol? Better use "strTicker" in this line to download the file with your first code:

strURL = "https://indexes.nasdaqomx.com/Index/ExportHistory/" & strTicker _  
& "?startDate=2013-12-08T00:00:00.000&endDate=2014-12-08T00:00:00.000&timeOfDay=EOD.xlsx"

If you change this variable into the second code, don't works!!! and I don't know why.

 

[Kalmar]

Thank you. Yes, I've noticed this mess with wrong variables. And cleared it. But still doesn't work as you mentioned.

The clean code is below:

Private Sub CommandButton2_Click()

'Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual


Dim UrlRange As range
Set UrlRange = Workbooks("Main").Sheets("Market").range("a1")

Dim Symbol As String
Symbol = "NQLA5300"

Dim URL As String
URL = "https://indexes.nasdaqomx.com/Index/ExportHistory/" & Symbol _
& "?startDate=2013-12-08T00:00:00.000&endDate=2014-12-08T00:00:00.000&timeOfDay=EOD"

'QueryQuote:

With Workbooks("Main").Sheets("Market").QueryTables.Add(Connection:="URL;" & URL, _
Destination:=UrlRange)
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True


End With

'Deleting Qeurytable
Dim qt As QueryTable
For Each qt In Workbooks("Main").Sheets("Market").QueryTables
qt.Delete
Next qt

URL = ""


Application.ScreenUpdating = True

Unload Me

End Sub