Hi,
I was hoping someone could please help. I have a piece of code that I've been using to download a csv file from the web, but when I tried to use it to download an xls file from another website, the file downloaded does not have any data. I believe the new website requires a login, also. Could someone take a look?
Jill
Sub csvdl()
Dim myURL As String
Dim WinHttpReq As Object
Dim c As Range
'S&P500
myURL = "http://www.standardandpoors.com/prot/logincheck?SPREFERER=/prot/spf/docs/indices/SPUSA-500-USDUF--P-US-L--Constituents.xls"
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send
myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
oStream.SaveToFile ("C:\MyDownloads\SP500.csv")
oStream.Close
End If
End sub
I was hoping someone could please help. I have a piece of code that I've been using to download a csv file from the web, but when I tried to use it to download an xls file from another website, the file downloaded does not have any data. I believe the new website requires a login, also. Could someone take a look?
Jill
Sub csvdl()
Dim myURL As String
Dim WinHttpReq As Object
Dim c As Range
'S&P500
myURL = "http://www.standardandpoors.com/prot/logincheck?SPREFERER=/prot/spf/docs/indices/SPUSA-500-USDUF--P-US-L--Constituents.xls"
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send
myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
oStream.SaveToFile ("C:\MyDownloads\SP500.csv")
oStream.Close
End If
End sub