You can definitely do NTLM authentication from VBA using WinHttp.WinHttpRequest - you have to add Microsoft WinHTTP Services as a reference to your VBA project. It would be exactly the same as using the powershell script, except that maybe the credential cache doesn't exist. Using HTTPS with Excel VBA. So, no doubt people are familiar with using MSXML2.XMLHTTP60 to make HTTP calls, this can be for web-scraping or even programming against a REST interface. Ideally, you want transport security for these requests but you'll need a different library for that 'Microsoft WinHTTP Services, version 5.1',C: Windows.
umamaheswaranp
New Member
* Through VBA, i need to trigger this URL ('https://myserver/connect/login?api_key=xxx').
A successful login on the same redirect URL, i get two parameters; status=success & request_token=yyyy
1. How to capture redirect URL and how to store status and request_token details in a cell.
* After obtaining the success message and request_token from the login flow, i should POST it to this URL(“https://myserver/session”) with these request parameters; request_token=abc, name=xyz, email=xy@xy.com. In response i get the output as JSON.
2. How to send multiple parameters and receive response.
Thanks
Excel Install Microsoft Winhttp Services
Here are two classes, that help download files asynchronuosly from Access-, Excel-, Word- VBA.
Create two class modules name them HTTPDownloader and HTTPRequest and put the following code in it:
HTTPDownloader:
HTTPRequest:
Excel Install Microsoft Winhttp Services Version
Usage:
Set References (menu extra/references) to “Microsoft ActiveData Objects” and “Microsoft WinHTTP Services”
Add two classes to your VBA-project, name them as indicated above and fill in the code.
In your module/class/form paste at the top:Private WithEvents pDownloader As HTTPDownloader
And make sure you create/destroy the downloader properly (for forms this is best done in the form_load/form_unload events): Set pDownloader = New HTTPDownloader Set pDownloader = Nothing
Now you can download files asynchronuosly withpDownloader.DownloadAsync 'http://whatever.com/file.zip','c:/on_my_machine/file.zip'
Events:
When you choose pDownloader from the left dropdown at the top of your IDE, the right dropdown gives you the Events of the downoader. The following events are available:
OnResponseStart: you won’t probably use this
OnResponseDataAvailable: triggers, when the first batch of data is coming in. Attention: this is triggered A LOT – use with care
OnResponseFinished: If you didn’t give a Filename to save, you have to get your data here (http.responsetext).
OnError: check here whats gone wrong.
Hope this is of use to someone.
I will probably update this soon to accomodate XML, get rid of the ADODB requirement and let the same object do synchronuous downloads. So keep looking;)