Handling REST API Handling in Excel VBA Macro

Question

The VBA code below is supposed to call a REST API to grant user access to the file or not. If the REST API returns allow then the code should close normally and grant access, if the REST API retuns disallow then the program should notify user and close workbook. If there is no Internet access, then the user should be notified and the work book should be closed.

My question is how do I code so the REST API response is handled by the macro properly so that it will either end normally or close due to the disallow response from url?

Here is the VBA code so far:

Private Sub Workbook_activate()

Application.EnableCancelKey = xlDisabled

' Run the Error handler "ErrHandler" when an error occurs.

On Error GoTo Errhandler


ActiveWorkbook.FollowHyperlink Address:="https://mysite.com/licensing/getstatus.php?", NewWindow:=True

' If response is allow

' Exit the macro so that the error handler is not executed.

****what goes here??****

      Exit Sub

' If response is disallow

****what goes here??****

      MsgBox "Your license key is not valid. Please check your key or contact customer service."

       ActiveWorkbook.Close SaveChanges:=False


Errhandler:

      ' If no Internet Access, display a message and end the macro.
      MsgBox "An error has occurred. You need Internet access to open the software."

       ActiveWorkbook.Close SaveChanges:=False

End Sub

I don't think you should be working with the .FollowHyperlink() method, as it will probably decouple the webrequest from the VBA code. Try looking into the HttpWebRequest and HttpWebResponse objects... — Recipe
– Recipe, Commented Oct 31, 2014 at 16:06

cboden · Accepted Answer · 2014-10-31 19:00:43Z

here an example for a simple HttpWebRequest:

Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.Open "GET", "https://mysite.com/licensing/getstatus.php?"
oRequest.Send
MsgBox oRequest.ResponseText

If you are behind a proxy you can use something like this:

Const HTTPREQUEST_PROXYSETTING_PROXY = 2
Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.setProxy HTTPREQUEST_PROXYSETTING_PROXY, "http://proxy.intern:8080"
oRequest.Open "GET", "https://mysite.com/licensing/getstatus.php?"
oRequest.Send
MsgBox oRequest.ResponseText

and if you want to use POST (instead of the GET method) to pass some values to the webserver, you can try this:

Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.Open "POST", "https://mysite.com/licensing/getstatus.php"
oRequest.SetRequestHeader "Content-Typ", "application/x-www-form-urlencoded"
oRequest.Send "var1=123&anothervar=test"
MsgBox oRequest.ResponseText

Collectives™ on Stack Overflow

Handling REST API Handling in Excel VBA Macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related