[EXCEL_VBA練習紀錄] 自動更新匯價 ( EXCEL VBA demo: How to update exchange rate) 以台銀為例

[EXCEL_VBA練習紀錄] 自動更新匯價 ( EXCEL VBA demo: How to update exchange rate) 以台銀為例
想用EXCEL 自行更新匯價
基本上只是想看匯價，可以直接到網頁看
會需要用EXCLE 自動更新匯價，是方便用來更新EXCLE其他的理財資料
例如基金的幣值對換台幣等

Sub exchange_rate_test()
'程式碼參考的網站http://forum.twbts.com/viewthread.php?tid=21084

Dim Stock1 As String
Dim oXmlhttp As Object, oHtmldoc As Object, surl As String, E As Object, R As Integer, C As Integer
  Set oXmlhttp = CreateObject("msxml2.xmlhttp")
   Set oHtmldoc = CreateObject("htmlfile")
     surl = "https://rate.bot.com.tw/xrt/all/day"
        With oXmlhttp
        .Open "Get", surl, False
        .send
        oHtmldoc.Write .responseText
    End With

     With oHtmldoc
        Set E = .all.tags("table")(0) '抓網頁表格
        For R = 0 To E.Rows.Length - 1
            For C = 0 To E.Rows(R).Cells.Length - 1
                ActiveSheet.Cells(R + 1, C + 1) = E.Rows(R).Cells(C).innerText
            Next
        Next
    End With
    
    '整理表格START
    For j = 3 To 30
    If Cells(j, 1) <> "" Then
    strLen = Len(Cells(j, 1))
     Cells(j, 1) = Trim(Mid(Cells(j, 1), strLen / 2 + 3, strLen))
     Cells(j, 1) = Replace(Cells(j, 1), Chr(10), "")
     Cells(j, 6) = ""
     Cells(1, 6) = ""
     End If
     
     Next
    
     Rows(1).Delete Shift:=xlUp
    '整理表格END
    
    Range("A1").Select
    
    End Sub

結果：

下載連結