PDA

View Full Version : Web Service Call and Authentification



fredlo2010
06-14-2014, 10:11 AM
Hello everyone,

I am trying to get a code that makes a URL call to download an xls file. After doing some research online I found some information. and I put together some code bellow. I have tested it and its working.





' Sample file from http://databases.about.com/od/tutorials/a/excelsample.htm
' Code modified from http://social.msdn.microsoft.com/Forums/office/en-US/16900c74-0ee3-469a-b44d-6c00bb58f7c0/web-service-calls-from-vba-excel


Sub TryHttpCall()


Dim MyRequest As Object
Dim byteStream As Object
Dim strFullName As String
Dim strURL As String


strFullName = "C:\Users\Alfred\Downloads\file.xls"
strURL = "http://databases.about.com/library/samples/address.xls"

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

MyRequest.Open "GET", strURL

' Send Request.
'MyRequest.Setcredentials "myemail@gmail.com", "pass", 0
MyRequest.Send

Set byteStream = CreateObject("ADODB.Stream")

' Wite to the file.
With byteStream
.Open
.Type = 1
.Write MyRequest.ResponseBody
If Not Dir(strFullName) = vbNullString Then Kill strFullName
.SaveToFile (strFullName)
.Close
End With


' Cleanup
Set MyRequest = Nothing
Set byteStream = Nothing


End Sub


The issue come when I am trying to add authentication to it. Of course the url I need it to work with is much more complex. This is a modified sample of how it looks more or less.


"https://hub.myCompany.com/report/ReportName/?ctoken=c60f897c-ce23-4c10-b562-69a7ec38d805&submitted=true&dataSource=sqlite&reportAccountingRunType=1&clientId=41257&Name=My+Client+Name&portfolioName=CLIENT_INFO&legal=CLIENT_INFO_MF&accountingParameter=CLIENT_INFO&endDate=06%2F06%2F2014&ert=XLS&ertOnFailure=json"

Basically the link is calling a Talend job (http://www.talend.com/products/big-data) putting together a report and downloading it from the server. I have tried several things online but they dont work. When I run the code with the link above what I get is a website (with an excel file extension) informing me that I am not logged on, and that the browser I am using is not supported.

Also note that I will get a new security token everytime I log on.

Any ideas, suggestions? I am a little lost here. Haven't been this lost since I started learning VBA :(

Thanks a lot for the help.