VBA如何抓取指定网址中的特定数据

Kaohsing

马行空_0314 发表于 2024-8-30 12:01
朋友好，可以试试我的账号，不过也是一个普通账户

账户：18117339097

马行空_0314

感谢上面各位的帮忙指点，看了B站的视频，需要一定解析操作，等有时间了慢慢看b站视频学习吧

马行空_0314

Kaohsing 发表于 2024-8-30 15:06

感谢老师帮忙指点，帮忙看看，我执行的时候，提示下面send（）里面的部分 编译错误 语法错误，这是什么原因？



Sub iwencai()
    Dim req As Object
    Dim url As String
    Dim sText As String
    url = "http://www.iwencai.com/customized/chart/get-robot-data"
    Set req = CreateObject("WinHttp.WinHttpRequest.5.1")
   
    With req
    .Open "POST", url, False
     'With CreateObject("MSXML2.XMLHTTP")     'With CreateObject("WinHttp.WinHttpRequest.5.1")
          .setRequestHeader "Accept", "application/json, text/plain, */*"
          .setRequestHeader "Accept-Encoding", "gzip, deflate"
          .setRequestHeader "Accept-Language", "zh-CN,zh;q=0.9,en;q=0.8"
          .setRequestHeader "Cache-control", "no-cache"
          .setRequestHeader "Content-Length", "395"
          .setRequestHeader "Content-Type", "application/json"
          .setRequestHeader "Cookie", "other_uid=Ths_iwencai_Xuangu_la2t8t5hnkc0u1rz6scwiee7fi624em8; ta_random_userid=5xmeowibra; wencai_pc_version=0; cid=e420547b04a22300b6c4e73349a9e9331693281844; ComputerID=e420547b04a22300b6c4e73349a9e9331693281844; WafStatus=0; v=A4Ud8DaMT-Xcd2u5wY9fChzmlMq6QjnUg_YdKIfqQbzLHqs0T5JJpBNGLf8U"
          .setRequestHeader "hexin-v", "A4Ud8DaMT-Xcd2u5wY9fChzmlMq6QjnUg_YdKIfqQbzLHqs0T5JJpBNGLf8U"
          .setRequestHeader "Host", "www.iwencai.com"
          .setRequestHeader "Origin", "http://www.iwencai.com"
          .setRequestHeader "Pragma", "no-cache"
          .setRequestHeader "Proxy-Connection", "keep-alive"
          .setRequestHeader "Referer", "http://www.iwencai.com/unifiedwap/result?w=9%3A40%E6%B6%A8%E5%B9%85%E5%A4%A7%E4%BA%8E%E6%88%96%E7%AD%89%E4%BA%8E5%25%EF%BC%8C%E9%9D%9E%E5%8C%97%E4%BA%A4%E6%89%80&querytype=stock"
          .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.84 Safari/537.36 HBPC/12.1.3.310"
         
          .send ("{"source":"Ths_iwencai_Xuangu","version":"2.0","query_area":"","block_list":"","add_info":"{\"urp\":{\"scene\":1,\"company\":1,\"business\":1},\"contentType\":\"json\",\"searchInfo\":true}","question":"9:40涨幅大于或等于5%，非北交所","perpage":50,"page":1,"secondary_intent":"stock","log_info":"{\"input_type\":\"click\"}","rsh":"Ths_iwencai_Xuangu_la2t8t5hnkc0u1rz6scwiee7fi624em8"}")
         
          sText = responseText
         
         
          Debug.Print sText
         
         
End With



End Sub

tspliu

马行空_0314 发表于 2024-9-11 14:48
感谢老师帮忙指点，帮忙看看，我执行的时候，提示下面send（）里面的部分 编译错误 语法错误，这是什么原 ...

如果你搞不清哪里应该用两个双引号，哪里用一个双引号，那就把字符串放在单元格里.send([a1])

马行空_0314

用fiddler找到了发送的请求地址，截图红框中的“为你找到17天结果”中的17就是我想要抓的数字，但是为什么下面的代码不能返回包含这个结果的字段呢？

Sub 网抓4()
    Dim req As Object
    Dim url As String
    Dim sText As String

    url = "https://www.iwencai.com/customized/chart/get-robot-data"

    Set req = CreateObject("WinHttp.WinHttpRequest.5.1")

    With req
        .Open "POST", url, False
        .setRequestHeader "Accept", "application/json, text/plain, */*"
        .setRequestHeader "Accept-Language", "zh-CN,zh;q=0.9"
        .setRequestHeader "Cache-control", "no-cache"
        .setRequestHeader "Connection", "keep-alive"
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Cookie", "other_uid=Ths_iwencai_Xuangu_z79bszybmud9ircec4pp1pm0qysarh3u; ta_random_userid=90vkjm3zed; cid=a36570889a6593ceeb8b219cdd7eb5c51724999707; v=A48vs2kFNVcOZTGr_JonETjpHiiItOPWfQjnyqGcK_4FcKHUqYRzJo3YdxCy"
        .setRequestHeader "Host", "www.iwencai.com"
        .setRequestHeader "Origin", "https://www.iwencai.com"
        .setRequestHeader "Pragma", "no-cache"
        .setRequestHeader "Referer", "https://www.iwencai.com/unifiedwap/result?w=9:30涨幅大于或等于5%，非北交所&querytype=stock"
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.6261.95 Safari/537.36"
        .setRequestHeader "hexin-V", "AzKSCKTSgJz7arygQS2yas22g3MFwzZdaMcqgfwLXuXQj9wv5FOGbThXepbP"

        ' JSON payload for the POST request
        Dim jsonPayload As String
        jsonPayload = ThisWorkbook.Sheets(1).Range("A13").Value
        
        Debug.Print jsonPayload
        
        
        
        .Send jsonPayload
        
        sText = .responseText
    End With
   
    Debug.Print sText

    ' 在此处添加处理响应文本 (sText) 的代码
End Sub


其中

下面的请求地址我放在了A13单元格

{"source":"Ths_iwencai_Xuangu","version":"2.0","query_area":"","block_list":"","add_info":"{\"urp\":{\"scene\":1,\"company\":1,\"business\":1},\"contentType\":\"json\",\"searchInfo\":true}","question":"9:30涨幅大于或等于5%，非北交所","perpage":"100","page":1,"secondary_intent":"stock","log_info":"{\"input_type\":\"click\"}","rsh":"Ths_iwencai_Xuangu_77ysuy7dvarvk9q3a874qpm3oqvbrayy"}


VBA返回下面的字段，请高手大神帮忙看看是什么意思，怎么调整程序

{"source":"Ths_iwencai_Xuangu","version":"2.0","query_area":"","block_list":"","add_info":"{\"urp\":{\"scene\":1,\"company\":1,\"business\":1},\"contentType\":\"json\",\"searchInfo\":true}","question":"9:30涨幅大于或等于5%，非北交所","perpage":"100","page":1,"secondary_intent":"stock","log_info":"{\"input_type\":\"click\"}","rsh":"Ths_iwencai_Xuangu_77ysuy7dvarvk9q3a874qpm3oqvbrayy"}
<h1>Nginx forbidden.</h1> <br/>request info: 114.86.130.0

前进者

把cookie注释掉，这句.setRequestHeader "hexin-V", "换成您自己抓包获取的字符串"，测试成功！

马行空_0314

前进者 发表于 2024-9-17 19:34
把cookie注释掉，这句.setRequestHeader "hexin-V", "换成您自己抓包获取的字符串"，测试成功！

感谢朋友帮忙测试，不过得到的信息还是下面一样的，看起来是被服务器拒绝访问了，估计是反爬虫的原因，没有得到我想要的选出A股的数量。还是说朋友你的程序能盘出来选出股票的数量？

{"source":"Ths_iwencai_Xuangu","version":"2.0","query_area":"","block_list":"","add_info":"{\"urp\":{\"scene\":1,\"company\":1,\"business\":1},\"contentType\":\"json\",\"searchInfo\":true}","question":"9:30涨幅大于或等于5%，非北交所","perpage":"100","page":1,"secondary_intent":"stock","log_info":"{\"input_type\":\"click\"}","rsh":"Ths_iwencai_Xuangu_77ysuy7dvarvk9q3a874qpm3oqvbrayy"}
<h1>Nginx forbidden.</h1> <br/>request info: 101.226.143.242