由于这是VBA,我会使用COM来调用xmlhttprequest
但以同步方式使用它,以免扰乱 VBA 的单线程执行环境,示例类说明了post
and get
请求方式如下:
'BEGIN CLASS syncWebRequest
Private Const REQUEST_COMPLETE = 4
Private m_xmlhttp As Object
Private m_response As String
Private Sub Class_Initialize()
Set m_xmlhttp = CreateObject("Microsoft.XMLHTTP")
End Sub
Private Sub Class_Terminate()
Set m_xmlhttp = Nothing
End Sub
Property Get Response() As String
Response = m_response
End Property
Property Get Status() As Long
Status = m_xmlhttp.Status
End Property
Public Sub AjaxPost(Url As String, Optional postData As String = "")
m_xmlhttp.Open "POST", Url, False
m_xmlhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
m_xmlhttp.setRequestHeader "Content-length", Len(postData)
m_xmlhttp.setRequestHeader "Connection", "close"
m_xmlhttp.send (postData)
If m_xmlhttp.readyState = REQUEST_COMPLETE Then
m_response = m_xmlhttp.responseText
End If
End Sub
Public Sub AjaxGet(Url As String)
m_xmlhttp.Open "GET", Url, False
m_xmlhttp.setRequestHeader "Connection", "close"
m_xmlhttp.send
If m_xmlhttp.readyState = REQUEST_COMPLETE Then
m_response = m_xmlhttp.responseText
End If
End Sub
'END CLASS syncWebRequest
现在你可以调用上面的代码来返回服务器的响应:
Dim request As New syncWebRequest
request.ajaxGet "http://localhost/ClientDB/AllClients?format=json"
Dim json as string
json = request.Response
这里的问题是我们希望能够以某种方式读取从服务器返回的数据,而不是直接操作 JSON 字符串。对我有用的是使用VBA-JSON http://code.google.com/p/vba-json/(谷歌代码导出here https://github.com/orialmog/vba-json/) 通讯类型Collection
处理 JSON 数组和Dictionary
使用解析器工厂方法处理成员及其声明Parse
这基本上使得创建这些字典集合变得更加简单。
现在我们可以解析 JSON:
[{"Name":"test name","Surname":"test surname","Address":{"Street":"test street","Suburb":"test suburb","City":"test city"}}]
变成类似下面的内容:
Set clients = parser.parse(request.Response)
For Each client In clients
name = client("Name")
surname = client("Surname")
street = client("Address")("Street")
suburb = client("Address")("Suburb")
city = client("Address")("City")
Next
这很好,但是像能够编辑和回发数据之类的东西呢?嗯,还有一个方法toString
从上面的 [Collection/Dictionary] JSON 数据创建一个 JSON 字符串,假设服务器接受 JSON 返回。