- 帖子
- 533
- 主題
- 58
- 精華
- 0
- 積分
- 613
- 點名
- 261
- 作業系統
- win 10
- 軟體版本
- []
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2013-3-19
- 最後登錄
- 2026-11-1
             
|
回復 3# iamaraymond
依據大大的指導，修改如下，請看看是不是這樣子。
謝謝- '從鉅亨網取得歷史股價
- '使用 IE 物件
- 'option explicit
- 'option base 1
- Sub TestCNYES1()
- Dim Price()
- Dim Code
- Code = "2330"
-
- Dim IE As Object, URL$
- Dim date0 As Date, StartDate$, EndDate$, submitBTN$, timer, time0 As Date
- Dim Table As Object, oDoc As Object, E
- Dim Re%, Ce%, i%, k%, ColName$
-
- ActiveSheet.Cells.Clear
-
- '設定資料開始及結束日期
- StartDate = Format(DateAdd("m", -2, Date), "yyyy/mm/dd") '取2個月的資料，約40筆
- EndDate = Format(Date, "yyyy/mm/dd")
-
- StartDate = "&ctl00$ContentPlaceHolder1$startText=" & StartDate '開始日期 input name
- EndDate = "&ctl00$ContentPlaceHolder1$endText=" & EndDate ' 結束日期 input name
- ' submitBTN = "&ctl00$ContentPlaceHolder1$submitBut=查詢"
-
- k = 0
- Again:
- Set IE = CreateObject("InternetExplorer.Application")
-
- With IE
- URL = "https://www.cnyes.com/twstock/ps_historyprice.aspx?code=" & Code & StartDate & EndDate
- .Visible = False '不顯示 IE
- .navigate URL
-
- time0 = Now() + #12:00:05 AM# '設定時間控制計時5秒鐘
- Do
- DoEvents
- Loop While (.Busy Or .readystate <> 4) And Time < time0
-
- '計時時間到了網頁未準備好，關閉重啟
- If .readystate <> 4 Then
- .Quit
- k = k + 1
- If k <= 2 Then GoTo Again
- GoTo GiveUp '開啟3次都失敗就放棄
- End If
-
- ' Set E = .Document.getElementsByTagName("TABLE")(0)
- Set E = .Document.all.tags("TABLE")(0) '這個網頁的資料表有變動
- If E Is Nothing Then IE.Quit: GoTo Again
-
- i = E.Rows.Length - 1: k = E.Rows(1).Cells.Length - 1
- ReDim Price(0 To i, 0 To k)
-
- For Re = 0 To i '取19天的歷史紀錄
- For Ce = 0 To k
- Price(Re, Ce) = E.Rows(Re).Cells(Ce).innertext '日期、開、高、低、收、漲跌 漲% 成交量 成交金額
- Next
- Next
-
- ActiveSheet.Cells(1, "A").Resize(i + 1, k + 1).Value = Price
- GiveUp:
-
- .Quit
- End With
- End Sub
|
|
發表於 2017-10-13 15:46
| 
