返回列表下一主題上一主題發帖

EXCEL VBA 匯入goodinfo網頁資料

chickout2

小學生 Rank: 1

帖子: 3
主題: 1
精華: 0
積分: 4
點名: 0
作業系統: win
軟體版本: 7
閱讀權限: 10
註冊時間: 2016-5-30
最後登錄: 2016-6-1

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2016-5-31 00:05 | 顯示全部帖子

EXCEL VBA 匯入goodinfo網頁資料

有幸google到版大GBKEE的文章
試著去查詢http://www.goodinfo.tw/stockinfo/ShowK_ChartFlow.asp?RPT_CAT=DR_3M&STOCK_ID=2454&CHT_CAT=MONTH
但會有物件錯誤

Option Explicit
Sub goodinfo()
Dim A As Object, i As Integer, C As Variant, Sh As Worksheet, Stock As String
Do
      Stock = InputBox("輸入股票代號", "股票代號", 2303)
Loop Until Len(Stock) >= 4
Set Sh = ActiveSheet                '可指定工作表
With CreateObject("InternetExplorer.application")
      .Navigate "http://www.goodinfo.tw/stockinfo/ShowK_ChartFlow.asp?RPT_CAT=DR_3M&STOCK_ID=" & Stock & "&CHT_CAT=MONTH"
      .Visible = True
      Do While .Busy Or .ReadyState <> 4
         DoEvents
      Loop
      Sh.Cells.Clear
      Set A = .Document.getelementsbytagname("table")(0)
      For i = 0 To A.Rows.Length - 1
         For C = 0 To A.Rows(i).Cells.Length - 1
            ActiveSheet.Cells(i + 1, C + 1) = A.Rows(i).Cells(C).innertext
         Next
      Next
      Set A = .Document.getelementbyid("content")
      For i = 0 To A.Rows.Length - 1
         For C = 0 To A.Rows(i).Cells.Length - 1
            ActiveSheet.Cells(i + 4, C + 1) = A.Rows(i).Cells(C).innertext
         Next
      Next
      Sh.UsedRange.EntireColumn.AutoFit
   .Quit
End With
MsgBox "OK"
End Sub