CODEWITHSUNDEEP
vbaexcel
CodeCamper
CodeCamper

Reputation: 6980

Open Excel (XLS) file from URL with GET parameters

I am trying to open an XLS file from an HTTP link using GET parameters. If you just copy and paste the link into your web browser you will see it works. If I omit the GET parameters I can open the workbook with workbooks.open but it opens the wrong workbook because you need the GET parameters to pull exactly what I want.

Dim myURL As String
Dim winHttpReq As Object
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
myURL = "http://www.otcmarkets.com/common/ViewStockScreener.xls?otcMarketTier=&otcMarketTierDesc=&otcMarketTierGroup=&otcMarketTierId=&otcMarketTierGroupDesc=&allTierGroups=true&securityType=CORP&securityTypeDesc=Corporate%20Bond&countryId=&locale=&countryDesc=&localeDesc=&allLocales=true&sicIndustryIdentifier="
winHttpReq.Open "GET", myURL, False
winHttpReq.Send
MsgBox Len(winHttpReq.responseBody)
result = winHttpReq.responseBody

Dim x As Workbooks
Set x = result
x(1).Open

Thanks for your assistance!

Upvotes: 1

Views: 1776

Answers (1)

Tmdean
Tmdean

Reputation: 9299

I believe that you can't open a file straight from a URL in Excel, unless it's a SharePoint site.

I think you're on the right track using WinHttpRequest, but you will need to save the result to a file on your local disk before opening up the file.

Dim myURL As String
Dim winHttpReq As Object
Dim responseData() As Byte
Dim fname As String
Dim fno As Integer

Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

myURL = "http://www.otcmarkets.com/common/ViewStockScreener.xls?" & _
     "otcMarketTier=&otcMarketTierDesc=&otcMarketTierGroup=&" & _
     "otcMarketTierId=&otcMarketTierGroupDesc=&allTierGroups=true&" & _
     "securityType=CORP&securityTypeDesc=Corporate%20Bond&countryId=&" & _
     "locale=&countryDesc=&localeDesc=&allLocales=true&sicIndustryIdentifier="

winHttpReq.Open "GET", myURL, False
winHttpReq.Send
responseData = winHttpReq.responseBody

fname = CurDur & "\ViewStockScreener.xls"
fno = FreeFile
Open fname For Binary As #fno
Put #fno, 1, responseData
Close #fno

Workbooks.Open fname

The () after responseData declares it as a variable length byte array. It's necessary to first copy the responseBody into an raw byte array because writing the responseBody directly to a binary file corrupts the data (probably some character encoding issue).

what to do if the ViewstockScreener.xls name becomes randomized as a result of the GET request

You can choose whatever file name you want when writing the response data to the file. If it's important to preserve the file name that the server sends back, that's actually kind of difficult. You would have to look in the Content-Disposition field of the response header and parse out the filename from there.

Upvotes: 2

Related Questions