如何快速將網頁資料抓到excel裡 - Excel程式區

[發問] 如何快速將網頁資料抓到excel裡

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章論壇榮譽勳章論壇贊助勳章 2015羊年全勤獎章

2^# 跳轉到 »

發表於 2017-12-19 17:24 | 只看該作者

回復 1# paul3063

試試看

Sub TWSEtest()
Dim sh As Worksheet
Dim iDate$, stock$, yYear$, mMonth$, URL$
Dim arDATA(), Table
Dim i%, j%
Set sh = Sheets("工作表1")
sh.Activate
Cells.Clear
stock = "2330" '設定代號
yYear = "2017" '設定年度
mMonth = "01" '設定月份
iDate = yYear & mMonth & "01"
URL = "http://www.twse.com.tw/exchangeReport/STOCK_DAY_AVG?response=Html&date=" & iDate & "&stockNo=" & stock
With CreateObject("InternetExplorer.Application")
.Visible = False ' 不顯示 IE
.Navigate URL
Do While .readyState <> 4
DoEvents
Loop
Set Table = .document.getElementsBytagname("table")(0)
With Table
ReDim arDATA(.Rows.Length, .Rows(1).Cells.Length)
For i = 0 To .Rows.Length - 1
For j = 0 To .Rows(1).Cells.Length - 1
On Error Resume Next
arDATA(i + 1, j + 1) = .Rows(i).Cells(j).innertext
Next
Next
End With
sh.Cells(1, 1).Resize(UBound(arDATA), UBound(arDATA, 2)) = arDATA
[A1].Select
.Quit '關閉瀏覽器
End With
Set Table = Nothing
Set sh = Nothing
Erase arDATA()
End Sub

複製代碼

靜思自在 : 發脾氣是短暫的發瘋。