vba - 将 VBA 类付诸实践

Question

我的问题与以下问题有关:JSON import to Excel

我理解这篇文章的大部分内容，但我不明白如何将它们放在一个 excel 模块或类模块中。有人可以帮助我了解如何组装这个野兽来实现我的目的吗？

编辑 29-5-14 @ 7.44AM -

这是我到目前为止所做的尝试:

安装为 ClassModule syncWebRequest

'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

安装为模块测试

Sub Test()
    Dim request As New syncWebRequest
    request.AjaxGet "http://crimson/php/SiteEnquiryAjax.php?action=aisFeed&brandCode=s&siteID=0625?format=json"
    Dim json As String
    json = request.Response
End Sub

我习惯于使用普通模块，但以前不必使用类模块。因此，我试图理解这一点并将其翻译以满足我的要求，但如果我把它做得比人们预期的更笨重，请原谅我。

我正在尝试从返回 JSON 的本地 Web 服务中查询数据。 Web 服务以以下形式返回基于 ID(例如 0656)的数据:

{"total":"1","results":[{"brandName":"ABC","brandingName":"Cat","siteCategory":"Production","siteName":"Scrubbed","streetAddress":"ABC RD, SUBURB, VIC 3000","siteState":"VIC","phoneNumber":"03 0909 0909","mobileNumber":"0409 090 909","faxNumber":"03 9090 9090","applicationServer":"CAT0656ABC001","dateOpened":"1979-08-21","siteNotice":"","lastContact":"2014-05-19 04:36:31",}]}

我的目标是将其放入电子表格中，用户可以在其中输入站点 ID(例如 0656)，然后电子表格将能够提取并使用上面示例的数据进行响应。

编辑 29-5-14 @ 8.42AM -

更新模块测试 - 我的目的是测试链接问题中示例的解析器，但是当我现在运行宏时，我收到一个错误。我的理解是，这可能是我想要实现的目标中缺失的部分。我现在已经注释掉了添加的项目以阻止错误发生。

Sub Test()
    Dim request As New syncWebRequest
    request.AjaxGet "http://crimson/php/SiteEnquiryAjax.php?action=aisFeed&brandCode=s&siteID=0625"
    Dim json As String
    json = request.Response

    'Set clients = parser.Parse(request.Response)
    'For Each client In clients
    'Name = client("Name")
    'State = client("siteState")
    'street = client("Address")("Street")
    'suburb = client("Address")("Suburb")
    'city = client("Address")("City")
    'Next
End Sub

编辑 29-5-14 @ 9.40AM -

换一种说法...我想转这个: “url/SiteEnquiryAjax.php?action=aisFeed&brandCode=s&siteID=0656”的 HTTP 结果

{"total":"1","results":[{"brandName":"ABC","brandingName":"Cat","siteCategory":"Production","siteName":"Scrubbed","streetAddress":"ABC RD, SUBURB, VIC 3000","siteState":"VIC","phoneNumber":"03 0909 0909","mobileNumber":"0409 090 909","faxNumber":"03 9090 9090","applicationServer":"CAT0656ABC001","dateOpened":"1979-08-21","siteNotice":"","lastContact":"2014-05-19 04:36:31",}]}

进入这个: Excel 输出

Answer 1

在不知道您收到的错误的确切细节的情况下，我之前使用过 vba-json 并且我认为您在使用它之前没有实例化解析器:

Dim parser As New JSONLib
Set clients = parser.parse(Request.Response)

就项目的设计而言，我认为您将 Request 分开做得很好作为一个类，然后从一个模块发出请求。我在从 Excel 访问 Salesforce 时遇到了许多类似的问题，并创建了一个我认为可能会有所帮助的库: Excel-REST .它遵循类似的想法，但将其分为三类:客户端、请求和响应。这是一个例子:

Sub RetrieveJSONSimple
  Dim Client As New RestClient
  Dim Response As RestResponse
  Set Response = Client.GetJSON("url...")

  If Response.Status = Ok Then
    ' Response.Data is parsed json
    For Each Client in Response.Data
      Name = Client("Name")
      State = Client("siteState")
      Street = Client("Address")("Street")
      Suburb = Client("Address")("Suburb")
      City = Client("Address")("City")
    Next Client
  End If
End Sub

Sub RetrieveJSONAdvanced
  Dim Client As New RestClient
  Client.BaseUrl = "http://crimson/php/"
  ' Can also setup authentication with HTTP Basic, OAuth, and others

  Dim Request As New RestRequest
  Request.Resource = "SiteEnquiryAjax.php?action=aisFeed&brandCode=s&siteID={SiteId}"
  Request.AddUrlSegment "SiteId", "0625"

  ' GET and json are default, but can be set
  Request.Method = httpGET
  Request.Format = json

  Dim Response As RestResponse
  Set Response = Client.Execute(Request)

  ' Process as before
End Sub