20160317 [VBA]下載網站的表格型態資料

01.
[題目]
請撰寫程式，以HTMLTable物件將GOGOFUND網站代號為47092的基金歷史淨值下載到Temp工作表。網址如下：
http://www.gogofund.com/convch/fund/nav_historical.asp?FundID=47092
'-------------------------------------------------------------------------------------
[解答]
Sub 操作HTMLTable物件以抓取表格資料()
    Dim myIE As Object
    Dim myTable
    Dim Tables
    Dim i As Integer
    Dim j As Integer
    Set myIE = CreateObject("InternetExplorer.Application")
　'前期綁定，也可寫成：Set myIE = New InternetExplorer
    With myIE
        '.Visible = True
        .navigate "http://www.gogofund.com/convch/fund/nav_historical.asp?FundID=47092"
        Do Until .readyState = READYSTATE_COMPLETE
　    '跑回圈直到成功開啟，也可寫成：Do Until .readystate = 4
        Loop
        Set Tables = .document.getelementsbytagname("table")
　　'抓取網頁中所有表格物件，也可寫成：Set Tables = .document.all.tags("table")
    End With
    With ThisWorkbook.Worksheets("Temp")
        .Cells.Clear
        For Each myTable In Tables
            If Left(myTable.innertext, 2) = "序號" Then
　　　'在每個表格中尋找表格開頭為最左邊二個字元為"序號"者
                For i = 0 To myTable.Rows.Length - 1  '逐行處理
                    For j = 0 To myTable.Rows(0).Cells.Length - 1  '逐欄處理
                        .Cells(i + 1, j + 1) = myTable.Rows(i).Cells(j).innertext
                    Next j
                Next i
            End If
        Next
    End With
    myIE.Quit
    Set myIE = Nothing
End Sub

'-------------------------------------------------------------------------------------

02.
[題目]
試著以HTMLTable物件到台灣期貨交易所下載民國2015年1月23日的台指期五個交割月份的成交價量資料到「臨時資料」工作表。該網頁網址如下：
"http://www.taifex.com.tw/chinese/3/3_1_1.asp?syear=2015&smonth=1&sday=23&COMMODITY_ID=TX"
提示：請先跑迴圈將每個表格的innerText寫入工作表的A1、B1…，以確認要抓取的表格是第幾個表格。
'-------------------------------------------------------------------------------------
[解答] '用即時運算視窗確認是哪一個表格
Sub aa()
    Dim myIE As Object
    Dim myTable
    Dim Tables
    Dim i As Integer
    Set myIE = New InternetExplorer
    With myIE
        .navigate "http://www.taifex.com.tw/chinese/3/3_1_1.asp?syear=2015&smonth=1&sday=23&COMMODITY_ID=TX"
        Do Until .readyState = 4
        Loop
        Set Tables = .document.all.tags("table")
    End With
    For Each myTable In Tables
        Debug.Print myTable.innerText
        Debug.Print i
        i = i + 1
        Debug.Print "----------------"  '表隔間用"---"隔開
    Next
    myIE.Quit
    Set myIE = Nothing
 
End Sub
'-------------------------------------------------------------------------------------
Sub 練習9_6()
  Call 用HTMLTable下載台指期五個交割月份價量資料("2015", "01", "23")
　'呼叫下面的程式
End Sub
'-------------------------------------------------------------------------------------
Sub 用HTMLTable下載台指期五個交割月份價量資料(myY, myM, myD)
  Dim myIE As InternetExplorer
  Dim WebAddress As String
  Dim i As Integer
  Dim j As Integer
  Dim Tables
  WebAddress = "http://www.taifex.com.tw/chinese/3/3_1_1.asp?" & _
              "syear=" & myY & "&smonth=" & myM & "&sday=" & myD & "&COMMODITY_ID=TX"
  Set myIE = New InternetExplorer
  With myIE
    .navigate WebAddress
    Do Until .readyState = READYSTATE_COMPLETE
　'跑回圈直到成功開啟，也可寫成：Do Until .readystate = 4
    Loop
    Set Tables = .document.getElementsByTagName("Table") '抓取網頁中所有表格物件
  End With
  With Tables(4)
    For i = 0 To .Rows.Length - 1      '逐行處理
      For j = 0 To .Rows(0).Cells.Length - 1      '逐欄處理
        Worksheets("臨時資料").Cells(i + 1, j + 1) = .Rows(i).Cells(j).innerText
      Next j
    Next i
  End With
  MsgBox "下載完成！"
  myIE.Quit
  Set myIE = Nothing
End Sub

'-------------------------------------------------------------------------------------