【网页采集教程】【高级篇】第二课-使用VBA解析JSON格式的网页

liucqa

RockedPanda 发表于 2014-8-6 17:39
"1x2"这种命名在JS中是一个合法属性,但这种属性不能通过"."运算符访问,可以通过"[]"运算符访问.VBA刚开始 ...

谢谢帮助        

RockedPanda

liucqa 发表于 2014-8-6 20:37
谢谢帮助

互相协助而已, VBA刚开始接触,还要向您多多学习.

choesehae

留下足迹 备查 感谢分享

VBA万岁

liucqa 发表于 2012-11-4 22:11
通过在VBA中使用上面的JS语句，我们可以得到简单的JSON文本数据。
然而，在实际应用中，上述语句会出现许多 ...

figfig大神的原代码如下（由于红色字体部分均为“email”，所以不存在冲突——想必l是iucqa大师为了说明CallByName函数的作用将原代码稍作了修改）：
Sub figjson4()
aa = "{ ""people"": [{ ""firstName"": ""Brett"", ""lastName"":""McLaughlin"", ""email"": ""brett@newInstance.com"" },{ ""firstName"": ""Jason"", ""lastName"":""Hunter"", ""email"": ""jason@servlets.com"" }, { ""firstName"": ""Elliotte"", ""lastName"":""Harold"", ""email"": ""elharo@macfaq.com"" }]}"
Set X = CreateObject("ScriptControl")
        X.Language = "JScript"
      
    s = "function j(s) { return eval('(' + s + ').people[1]'); }"
     X.AddCode s
       Set y = X.Run("j", aa)
      MsgBox y.firstName
      MsgBox y.email


End Sub

VBA万岁

VBA万岁 发表于 2014-10-9 11:51
figfig大神的原代码如下（由于红色字体部分均为“email”，所以不存在冲突——想必l是iucqa大师为了说明C ...

Mark如附件：
网页采集(高级篇)教程第二课.zip (917.35 KB, 下载次数: 130)

2014-10-9 12:04 上传

点击文件名下载附件

liucqa

VBA万岁 发表于 2014-10-9 12:04
Mark如附件：

Sub figjson4()
    Dim aa, x, s, y
    aa = "{ ""people"": [{ ""firstName"": ""Brett"", ""lastName"":""McLaughlin"", ""email"": ""brett@newInstance.com"" },{ ""firstName"": ""Jason"", ""lastName"":""Hunter"", ""email"": ""jason@servlets.com"" }, { ""firstName"": ""Elliotte"", ""lastName"":""Harold"", ""email"": ""elharo@macfaq.com"" }]}"
    Set x = CreateObject("ScriptControl")
    x.Language = "JScript"
    s = "function j(s) { return eval('(' + s + ').people[1]'); }"
    x.AddCode s
    Set y = x.Run("j", aa)
    MsgBox y.firstName
    MsgBox y.Email
End Sub

y.Email不通过

HHAAMM

没细看，只觉的用js的函数方式没下面这样看着直观

1. Sub figjson()
   
2.     aa = "{ ""people"": [{ ""firstName"": ""Brett"", ""lastName"":""McLaughlin"", ""email"": ""brett@newInstance.com"" },{ ""firstName"": ""Jason"", ""lastName"":""Hunter"", ""email"": ""jason@servlets.com"" }, { ""firstName"": ""Elliotte"", ""lastName"":""Harold"", ""email"": ""elharo@macfaq.com"" }]}"
   
3.     Set x = CreateObject("ScriptControl")
   
4.     x.Language = "javascript"
   
5.     x.AddCode "HHAAMM = " & aa
   
6.     Debug.Print x.Eval("HHAAMM.people[1].firstName")
   
7.     Debug.Print x.Eval("HHAAMM.people[1].lastName")
   
8.     Debug.Print x.Eval("HHAAMM.people[1].email")
   
9. End Sub

复制代码

HHAAMM

Email email
javascript好像是区分大小写的

VBA万岁

liucqa 发表于 2014-10-9 20:06
Sub figjson4()
    Dim aa, x, s, y
    aa = "{ ""people"": [{ ""firstName"": ""Brett"", ""lastNa ...

38楼HHAAMM版主说过，javascript好像是区分大小写的，将aa中的emai改为Emai（或者同改为首字母小字如34楼）就可以了。
Sub figjson4()
Dim aa, x, s, y
aa = "{ ""people"": [{ ""firstName"": ""Brett"", ""lastName"":""McLaughlin"", ""Emai"": ""brett@newInstance.com"" },{ ""firstName"": ""Jason"", ""lastName"":""Hunter"", ""Email"": ""jason@servlets.com"" }, { ""firstName"": ""Elliotte"", ""lastName"":""Harold"", ""Email"": ""elharo@macfaq.com"" }]}"
Set x = CreateObject("ScriptControl")
    x.Language = "JScript"
    s = "function j(s) { return eval('(' + s + ').people[1]'); }"
    x.AddCode s
Set y = x.Run("j", aa)
    MsgBox y.firstName
    MsgBox y.Email
End Sub

VBA万岁

liucqa 发表于 2014-10-9 20:06
Sub figjson4()
    Dim aa, x, s, y
    aa = "{ ""people"": [{ ""firstName"": ""Brett"", ""lastNa ...

当然，此种情况用CallByName函数能解决该冲突问题。但仍需区分大小写。