[發問] 匯入網頁資料

論壇贊助勳章

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2014-3-27 22:02 | 只看該作者

http://hk.ishares.com/product_info/fund/holdings/SEHK/2823.htm?periodCd=d
請問這網頁的 "投資組合詳情" 下的資料怎麼匯到 excel 裡
我用匯入外部資料 .... 他只不到下面的那些值

小菜鳥一個請多指教~

　暱稱: joey0415

2^#

發表於 2014-3-28 08:56 | 只看該作者

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub HK()
Dim URL As String, shts As Worksheet
Dim x As Variant, xi As Integer, A As Object, xlHtm
Set shts = ActiveSheet '
shts.Cells.Clear
URL = "http://hk.ishares.com/product_info/fund/holdings/2823.htm?periodCd=d"
With CreateObject("InternetExplorer.Application")
.Visible = True ' 是否顯示 IE
.Navigate URL
Do While .ReadyState <> 4 Or .Busy
DoEvents
Loop
xlHtm = .Document.body.innerHTML '儲存
Sleep 2000
Set A = .Document.getElementsBytagname("table")
For xi = 2 To 2
.Document.body.innerHTML = A(xi).outerHTML
.ExecWB 17, 2 ' Select All
.ExecWB 12, 2 ' Copy selection
With shts
.Range("A1").Select
.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
End With
.Document.body.innerHTML = xlHtm '還原
Next xi
shts.Cells.EntireColumn.AutoFit ' 自動調整欄寬
.Quit
End With
End Sub

複製代碼

靜思自在 : 成功是優點的發揮，失敗是缺點的累積。