在Excel VBA宏中处理REST API处理
问题描述:
下面的VBA代码应该调用REST API来授予用户对该文件的访问权限。如果REST API返回允许,那么代码应正常关闭并授予访问权限,如果REST API回退不允许,那么程序应通知用户并关闭工作簿。如果没有互联网接入,则应通知用户并关闭工作簿。在Excel VBA宏中处理REST API处理
我的问题是我该如何编写代码,以便正确处理REST API响应,以便它能正常结束或关闭,原因是来自url的禁止响应?
这是迄今为止VBA代码:
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
答
这里简单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
如果您是使用代理,你可以使用这样的事情:
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
如果您想使用POST(而不是GET方法)将一些值传递给Web服务器,您可以尝试使用s:
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
我不认为你应该使用.FollowHyperlink()方法,因为它可能会解耦来自VBA代码的webrequest。尝试查看HttpWebRequest和HttpWebResponse对象... – Recipe 2014-10-31 16:06:47