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