20180226xlVbaGetStockData

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
Sub LoopGetStockData()
Dim StartTime As Variant
    Dim UsedTime As Variant
    StartTime = VBA.Timer
     
    Cells.ClearContents
    For y = 2017 To 2007 Step -1
        For s = 4 To 1 Step -1
            GetStockData "600000", y, s
        Next s
    Next
     
UsedTime = VBA.Timer - StartTime
    Debug.Print "UsedTime :" & Format(UsedTime, "#0.0000 Seconds")
   'MsgBox "UsedTime :" & Format(UsedTime, "#0.0000 Seconds")
     
End Sub
Sub GetStockData(ByVal StockNo As String, ByVal YearNo As String, ByVal SeasonNo As String)
   
    URL = "http://xxx.com/trade/lsjysj_" & StockNo & ".html?year=" & YearNo & "&season=" & SeasonNo
    '发送请求
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        ' With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .setRequestHeader "Content-Type", "text/html"
        .Send
        WebText = .responsetext
        'Debug.Print WebText
        'Range("A1").Value = WebText
    End With
     
    Dim OneTable As Object
    Dim OneTh As Object
    Dim OneTr As Object
    Dim tHead As Object
    Dim tBody As Object
    Dim r As Long, c As Long
    With CreateObject("htmlfile")
        .write WebText
        Set OneTable = .getElementsByTagName("table")(3)
        r = Cells(Cells.Rows.Count, 1).End(xlUp).Row + 1
        If r = 2 Then r = 1
         
        Set tHead = OneTable.FirstChild
        Set tr = tHead.FirstChild
        c = 0
        If r = 1 Then
            For Each OneTh In tr.ChildNodes
                c = c + 1
                Cells(r, c).Value = OneTh.innerText
            Next OneTh
        End If
        Set tBody = tHead.NextSibling
        For Each OneTr In tBody.ChildNodes
            r = r + 1
            c = 0
             
            For Each td In OneTr.ChildNodes
                c = c + 1
                Cells(r, c).Value = td.innerText
            Next td
        Next OneTr
         
    End With
     
    Set OneTable = Nothing
    Set OneTh = Nothing
    Set OneTr = Nothing
    Set tHead = Nothing
    Set tBody = Nothing
     
End Sub

  

posted @   wangway  阅读(200)  评论(0编辑  收藏  举报
编辑推荐:
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
阅读排行:
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(三):用.NET IoT库
· 【非技术】说说2024年我都干了些啥
点击右上角即可分享
微信分享提示