本帖最後由 Scott090 於 2017-11-12 17:01 編輯
'捲動網頁
'''' i 值太小，網頁資料會來不及進來；太大會浪費時間
'''' 有何方法可以知道資料已進來齊全而停止；或其他方式取代本方法?
i = 0
While i < 50
.Document.Parentwindow.scrollby 0, 10000 'Move,Scroll IE window down to bottom
i = i + 1
Wend
請問那一位前輩幫忙? 先謝謝
以下的例子是資料的長短多寡取決於日期期間的長短，如何做會比較有效率?- '使用 ie,Application 物件
- '從 finance.Yahoo.com取得歷史資料
- '
- Sub getHistoricalData()
- Dim Code
- Code = "AAPL"
- Const mysteryNum = 2209190400#
- Dim ie, DATAar, A$, yyDate
- Dim URL As String
- Dim StartDate, EndDate, timer, tt As Date
- Dim Table As Object, oDoc As Object
- Dim i%, j%, k%
-
- StartDate = "1999/1/2" '開始日期
- EndDate = Date '結束日期，預設為今天
-
- '轉換為秒鐘數字
- StartDate = DateValue(StartDate) * 86400 - mysteryNum
- EndDate = DateValue(EndDate) * 86400 - mysteryNum
-
- Set ie = CreateObject("InternetExplorer.Application")
-
- With ie
- Application.StatusBar = "打開網頁，等待資料備齊 ....."
- URL = "https://finance.yahoo.com/quote/" & Code & "/history?period1=" & StartDate & "&period2=" & EndDate & "&interval=1d&filter=history&frequency=1d"
- .Visible = True ' False '顯示 IE否?
- .navigate URL
- Application.Wait Time + #12:00:04 AM# '等候網頁4秒鐘
- tt = Time + #12:00:05 AM#
- Do While (.busy Or .readyState <> 4) And Time < tt
- DoEvents
- Loop
-
- '捲動網頁
- '''' i 值太小，網頁資料會來不及進來；太大會浪費時間
- '''' 有何方法可以知道資料已進來齊全而停止；或其他方式取代本方法?
- i = 0
- While i < 50
- .Document.Parentwindow.scrollby 0, 10000 'Move,Scroll IE window down to bottom
- i = i + 1
- Wend
-
- Application.StatusBar = "取網頁資料中 .... "
- Set oDoc = .Document.getElementsByTagName("TABLE")(1)
-
- ActiveSheet.Cells.Clear
- ReDim DATAar(oDoc.Rows.Length - 1, 7)
-
- For i = 0 To oDoc.Rows.Length - 2
- For j = 0 To 6
- On Error Resume Next
- DATAar(i + 1, j + 1) = oDoc.Rows(i).Cells(j).innertext
- If j = 0 And i <> 0 Then
-
- '''' yyDate = CDate("Oct 01, 2017")
- A = DATAar(i + 1, j + 1)
- A = Mid(A, 2, 3) & " " & Mid(A, 8, 2) & ", " & Right(A, 4)
- yyDate = CDate(A)
- DATAar(i + 1, j + 1) = yyDate
-
- End If
- Next
- Next
- Application.StatusBar = "資料移到Excel 工作表 ....."
- ActiveSheet.Cells(1, 1).Resize(UBound(DATAar), 7).Value = DATAar
- Columns("A:A").NumberFormatLocal = "yyyy/mm/dd"
- Columns("G:G").NumberFormatLocal = "#,##0_)"
- .Quit
- End With
- Application.StatusBar = "資料下載完成 ....... "
- Application.Wait Time + #12:00:02 AM#
- Application.StatusBar = False
-
- End Sub
|
發表於 2017-11-12 16:59
| 
