Calling Web APIs from VBA Code

VBA is an acronym that stands for Visual Basic for Applications. VBA is a programming language that is included as part of several Microsoft products, including Access, Word, and Excel. For example, Access 2003 VBA uses VBA version 6.0, which is the same version of VBA used by Word 2003 VBA and Excel 2003 VBA. VBA is a programming language that can be used to incorporate additional features into your applications. VBA should not be confused with the Microsoft Visual Basic or Visual Basic .NET programming products. VB and VB.NET each has its own syntax of the Visual Basic programming language. The VB syntax, the VB.NET syntax, and the VBA syntax are very similar, but there are also differences between them.

You can use VBA to call a Web API from a Microsoft Office program. Depending on the type of Web service you want to call, you may need to download one or more additional toolkits in order for your call to work. For example, if you want to call a Web service from VBA using SOAP, you must install a free SOAP Web services toolkit so that Microsoft Office can generate the SOAP and other files for you. You also need to download the latest version of MSXML, Microsoft's XML parser. If you just want to call a Web API using HTTP/GET (REST) or HTTP/POST, then you install MSXML if it is not already installed. You learned in Chapter 1 that the party offering the Web API specifies which of these protocols the Web API will support. Let's look at an example of how each of these works.

Calling Web APIs Using VBA with HTTP/POST and HTTP/GET (REST)

You do not need to install the Web Services Toolkit for Office if you are not calling a Web API using SOAP. You can use VBA code to communicate using HTTP POST and/or GET (REST). Let's look at a basic example of how this works from Microsoft Excel.

Example — Calling Amazon.com from Excel Using VBA and HTTP/GET (REST)

In this example, you call the Amazon.com API discussed in Chapter 4 from Microsoft Excel. Open Excel and create a new workbook called VBATestFromExcel. On Sheet1 of the workbook, arrange the fields as shown in Figure 9-1. It is important that the cells be laid out exactly as shown in the figure if you want the results to be displayed in the correct cells. Use the Visual Basic toolbar to add a button in cell B2 as shown. To view the Visual Basic toolbar, select View O Toolbars O Visual Basic.

si Microsoft Excel - VBATestFromExcel [email protected])0

l^j File Edit View Insert Format Tools Data Window ¡Manage Help RepliGo

. $ x

U JJ.J a I-'I ii/ - ® ; - A" |

III J ■ Iii Ii »J i

/_ k = i J

B14 t fx

A

B

c

1

Enter Search Term:

1

Search Amazon.Com

3

First Search Result:

_

4

ASINflSBN:

5

Link:

G

Author:

7

Tide:

s"

a

10

11

s _j a Jg.!

12

£ 4 S A HI*

—fv"

H 4

i. n \Sheetl/ Sheet2 j( Sheets / j< |

_

HJ

Draw* ^ | AutoShapes * \ M HDS! JiJ; SlSa'I^.J'. Ai^a^.l ||

I Ready

You can modify the name displayed on the Command button by viewing the Properties dialog box from the Toolbox and then changing the Caption property.

After adding the Command button, make sure you are still in design mode. If you are in design mode, you see that a Design Mode button on the toolbox in the upper-left corner is selected. If you are not in design mode, the code behind the button executes if you click or double-click the button. In order to add that code, you double-click the button when you are in design mode. You should see a Visual Basic Editor window open within the currently empty CommandButton1_Click event.

Add the following code to the CommandButton1_Click event, replacing [YourldGoesHere] with your Amazon.com Subscription ID where indicated:

Private Sub CommandButton1_Click()

Dim oXML As Object Dim oDom As Object Dim strXML As String Dim strResponse As String Dim strURL As String

On Error GoTo Handler

Set oXML = CreateObject("Microsoft.XMLHTTP")

'set the parameters to be passed to the Amazon.com web API 'including retrieving the search criteria entered in cell B1 strURL =

"http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&SubscriptionId= [YourIdGoesHere]&Operation=ItemSearch&SearchIndex=Books&Keywords=" & Worksheets("Sheet1").Range("B1").Value

'Clear any prior results

Worksheets("Sheet1").Range("B4").Value = ""

Worksheets("Sheet1").Range("B5").Value = ""

Worksheets("Sheet1").Range("B6").Value = ""

Worksheets("Sheet1").Range("B7").Value = ""

'Call the Amazon.com service With oXML

.Open "GET", strURL, False

'Needed to web service will recognize get/post

.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .send End With

'get the results strResponse = oXML.responseText

'load the results into a new XML document Set oDom = CreateObject("MSXML.DOMDocument") oDom.loadXML (strResponse)

'display the results on the spreadsheet If oDom.hasChildNodes Then

Worksheets("Sheet1

").Range("B4").Value = _

oDom.documentelement.

selectSingleNode("Items/Item/ASIN").Text

Worksheets("Sheet1

").Range("B5").Value = _

oDom.documentelement.

selectSingleNode("Items/Item/ItemAttributes/Title").Text

Worksheets("Sheet1

").Range("B6").Value = _

oDom.documentelement.

selectSingleNode("Items/Item/ItemAttributes/Author").Text

Worksheets("Sheet1

").Range("B7").Value = _

oDom.documentelement.

selectSingleNode("Items/Item/DetailPageURL").Text

Else

'No items matched search

Worksheets("Sheet1

").Range("B4").Value = ""

Worksheets("Sheet1

").Range("B5").Value = ""

Worksheets("Sheet1

").Range("B6").Value = ""

Worksheets("Sheet1

").Range("B7").Value = ""

MsgBox ("No results were returned.")

End If

Set oXML = Nothing

Set oDom = Nothing

Exit Sub

Handler:

MsgBox Err.Description

Set oXML = Nothing

Set oDom = Nothing

End Sub

The previous code calls the Amazon.com Web service using HTTP/GET and passes the URL with the search parameters. Then, the XML results are parsed by field to populate the cells with the returned values.

Save the code from the Visual Basic Editor and then return to Sheet1. Turn off design mode if you are still in it. Enter your search terms in cell B1 and click the Search button. A screen similar to the one shown in Figure 9-2 is then displayed.

As you can see, the first book matching the criteria specified is displayed in the cells on Sheet1. This is just a simple example of how you can use VBA with Microsoft Office to call Web APIs using HTTP.

Was this article helpful?

+1 -2

Responses

  • Jamila
    How to call a website in vba?
    2 years ago
  • CAMELIA BUTTON
    How to call webapi from vba?
    10 months ago
  • Ulrike
    Is it safe to send information to a web API from excel?
    5 months ago
  • florian scholz
    Can vba access rest api?
    1 month ago
  • DAISY
    Can word vba call rest api?
    23 days ago

Post a comment