VBA-tools/VBA-WebPublic

NotificationsYou must be signed in to change notification settings
Fork502
Star2.1k

VBA-Web: Connect VBA, Excel, Access, and Office for Windows and Mac to web services and the web

License

MIT license

2.1k stars 502 forks Branches Tags Activity

Star

Notifications

You must be signed in to change notification settings

Branches Tags

Folders and files

Name		Name	Last commit message	Last commit date
Latest commit History 483 Commits
.github		.github
authenticators		authenticators
docs		docs
examples		examples
rfcs		rfcs
specs		specs
src		src
.editorconfig		.editorconfig
.gitattributes		.gitattributes
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
README.md		README.md
VBA-Web - Blank.xlsm		VBA-Web - Blank.xlsm
VBA-Web - Installer.xlsm		VBA-Web - Installer.xlsm
credentials - example.txt		credentials - example.txt

Repository files navigation

VBA-Web

VBA-Web (formerly Excel-REST) makes working with complex webservices and APIs easy with VBA on Windows and Mac. It includes support for authentication, automatically converting and parsing JSON, working with cookies and headers, and much more.

Getting started

Download thelatest release (v4.1.6)
To install/upgrade in an existing file, useVBA-Web - Installer.xlsm
To start from scratch in Excel,VBA-Web - Blank.xlsm has everything setup and ready to go

For more details see theWiki

Upgrading

To upgrade from Excel-REST to VBA-Web, follow theUpgrading Guide

Note: XML support has been temporarily removed from VBA-Web while parser issues for Mac are resolved.XML support is still possible on Windows, followthese instructions to use a custom formatter.

Notes

Authentication support is built-in, with suppory for HTTP Basic, OAuth 1.0, OAuth 2.0, Windows, Digest, Google, and more. SeeAuthentication for more information
For proxy environments,Client.EnabledAutoProxy = True will automatically load proxy settings
Support for custom request and response formats. SeeRegisterConverter

Examples

The following examples demonstrate using the Google Maps API to get directions between two locations.

GetJSON Example

FunctionGetDirections(OriginAsString,DestinationAsString)AsString' Create a WebClient for executing requests' and set a base url that all requests will be appended toDimMapsClientAsNewWebClientMapsClient.BaseUrl="https://maps.googleapis.com/maps/api/"' Use GetJSON helper to execute simple request and work with responseDimResourceAsStringDimResponseAsWebResponseResource="directions/json?"&_"origin="&Origin&_"&destination="&Destination&_"&sensor=false"SetResponse=MapsClient.GetJSON(Resource)' => GET https://maps.../api/directions/json?origin=...&destination=...&sensor=falseProcessDirectionsResponseEndFunctionPublicSubProcessDirections(ResponseAsWebResponse)IfResponse.StatusCode=WebStatusCode.OkThenDimRouteAsDictionarySetRoute=Response.Data("routes")(1)("legs")(1)Debug.Print"It will take "&Route("duration")("text")&_" to travel "&Route("distance")("text")&_" from "&Route("start_address")&_" to "&Route("end_address")ElseDebug.Print"Error: "&Response.ContentEndIfEndSub

There are 3 primary components in VBA-Web:

WebRequest for defining complex requests
WebClient for executing requests
WebResponse for dealing with responses.

In the above example, the request is fairly simple, so we can skip creating aWebRequest and instead use theClient.GetJSON helper to GET json from a specific url. In processing the response, we can look at theStatusCode to make sure the request succeeded and then use the parsed json in theData parameter to extract complex information from the response.

WebRequest Example

If you wish to have more control over the request, the following example usesWebRequest to define a complex request.

FunctionGetDirections(OriginAsString,DestinationAsString)AsStringDimMapsClientAsNewWebClientMapsClient.BaseUrl="https://maps.googleapis.com/maps/api/"' Create a WebRequest for getting directionsDimDirectionsRequestAsNewWebRequestDirectionsRequest.Resource="directions/{format}"DirectionsRequest.Method=WebMethod.HttpGet' Set the request format' -> Sets content-type and accept headers and parses the responseDirectionsRequest.Format=WebFormat.Json' Replace {format} segmentDirectionsRequest.AddUrlSegment"format","json"' Add querystring to the requestDirectionsRequest.AddQuerystringParam"origin",OriginDirectionsRequest.AddQuerystringParam"destination",DestinationDirectionsRequest.AddQuerystringParam"sensor","false"' => GET https://maps.../api/directions/json?origin=...&destination=...&sensor=false' Execute the request and work with the responseDimResponseAsWebResponseSetResponse=MapsClient.Execute(DirectionsRequest)ProcessDirectionsResponseEndFunctionPublicSubProcessDirections(ResponseAsWebResponse)' ... Same as previous exampleEndSub

The above example demonstrates some of the powerful feature available withWebRequest. Some of the features include:

Url segments (Replace {segment} in resource with value)
Method (GET, POST, PUT, PATCH, DELETE)
Format (json, xml, url-encoded, plain-text) for content-type and accept headers and converting/parsing request and response
QuerystringParams
Body
Cookies
Headers

For more details, see theWebRequest portion of theDocs

Authentication Example

The following example demonstrates using an authenticator with VBA-Web to query Twitter. TheTwitterAuthenticator (found in theauthenticators/folder) uses Twitter's OAuth 1.0a authentication and details of how it was created can be found in theWiki.

FunctionQueryTwitter(QueryAsString)AsWebResponseDimTwitterClientAsNewWebClientTwitterClient.BaseUrl="https://api.twitter.com/1.1/"' Setup authenticatorDimTwitterAuthAsNewTwitterAuthenticatorTwitterAuth.Setup_ConsumerKey:="Your consumer key",_ConsumerSecret:="Your consumer secret"SetTwitterClient.Authenticator=TwitterAuth' Setup query requestDimRequestAsNewWebRequestRequest.Resource="search/tweets.json"Request.Format=WebFormat.JsonRequest.Method=WebMethod.HttpGetRequest.AddQuerystringParam"q",QueryRequest.AddQuerystringParam"lang","en"Request.AddQuerystringParam"count",20' => GET https://api.twitter.com/1.1/search/tweets.json?q=...&lang=en&count=20'    Authorization Bearer Token... (received and added automatically via TwitterAuthenticator)SetQueryTwitter=TwitterClient.Execute(Request)EndFunction