EXCEL VBA用XMLHTTP物件抓取外匯資料：如何用XMLHTTP物件直接抓取台銀外匯的資料到EXCEL中，之前用匯入資料從WEB的方式實際上就是用QueryTable物件效果不彰，主要原因是QueryTable顧名思義，就是查詢原始碼裡的Table標籤，再選擇下載到EXCEL，若是網頁中沒有Table標籤，或是不是表格形式的資料，就有無法下載，或是下載後格式錯亂問題，因此這次分享用XMLHTTP物件代勞。
預覽畫面：
完成畫面：
網址：
一.利用台銀官網的CSV檔抓取資料
1.下載整個資料到A1
Public Sub XMLHTTP()
'1.載入XMLHTTP物件為HttpReq物件變數
Set HttpReq = CreateObject("MSXML2.XMLHTTP.3.0")
'2.用Open方法開啟 CSV檔
HttpReq.Open "GET", "https://rate.bot.com.tw/xrt/flcsv/0/day", False
'3.傳送需求給伺服器
HttpReq.send
'4.將結果顯示在 A1儲存格
Range("A1") = HttpReq.responseText
End Sub
2.下載到A欄
Public Sub XMLHTTP_下載到A欄()
'調整第1欄寬為100
Columns(1).ColumnWidth = 100
'建立物件變數HttpReq，取用XMLHTTP物件以抓取網路資料
Set HttpReq = CreateObject("MSXML2.XMLHTTP.3.0")
HttpReq.Open "GET", _
"https://rate.bot.com.tw/xrt/flcsv/0/day", _
False
HttpReq.send
S = HttpReq.responseText
'切割位置從1開始
iStart = 1
'輸出到EXCEL從第1列開始
i = 1
Do While VBA.InStr(iStart, S, vbCrLf) <> 0
'找換行位置
iEnd = VBA.InStr(iStart, S, vbCrLf)
'切割一行
Sline = Mid(S, iStart, iEnd - iStart)
''切割位置+1繼續找
iStart = iEnd + 2
Cells(i, "A") = Sline
i = i + 1
Loop
'最後一筆沒有換行會少一筆(切割為最後位置+1到最後)
Sline = Mid(S, iEnd + 1, Len(S))
Cells(i, "A") = Sline
'顯示訊息
MsgBox "下載完畢!!", vbInformation
End Sub
3.先錄製資料剖析巨集，再分割A欄與刪除不要的欄
Sub 資料剖析()
'1.選取A1
Range("A1").Select
'2.CTRL向右下下選取
Range(Selection, Selection.End(xlDown)).Select
'3.資料剖析，不匯入遠期
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 9), Array(6, 9), _
Array(7, 9), Array(8, 9), Array(9, 9), Array(10, 9), Array(11, 9), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 9), Array(16, 9), Array(17, 9), Array(18, 9), Array(19, 9), Array _
(20, 9), Array(21, 9), Array(22, 1)), TrailingMinusNumbers:=True
'4.自動調整欄寬
Columns.AutoFit
'5.選取A1
Range("A1").Select
End Sub
二、下載網頁原始碼與切割資料到EXCEL中：
1.右鍵可以檢視原始碼
2.找出原始碼裡的資料頭尾，再用MID函數切割
程式碼：
Public Sub XMLHTTP_台銀外匯()
'建立物件變數HttpReq，取用XMLHTTP物件以抓取網路資料
Set HttpReq = CreateObject("MSXML2.XMLHTTP.3.0")
HttpReq.Open "GET", _
"http://rate.bot.com.tw/xrt?Lang=zh-TW", _
False
HttpReq.send
S = HttpReq.responseText
'切割位置從1開始
iStart = 1
'輸出到EXCEL從第1列開始
i = 4
Do While VBA.InStr(iStart, S, "text-right display_none_print_show print_width") <> 0
'1.現今買入
'找換行位置
iStart = VBA.InStr(iStart, S, "text-right display_none_print_show print_width")
iEnd = VBA.InStr(iStart, S, "
")
'切割一行 ? len("rate-content-cash text-right print_hide")
Sdata = Mid(S, iStart + 48, iEnd - iStart - 48)
''切割位置+1繼續找
iStart = iEnd + 48
Cells(i, "B") = Sdata
'2.現今賣出
'找換行位置
iStart = VBA.InStr(iStart, S, "text-right display_none_print_show print_width")
iEnd = VBA.InStr(iStart, S, "
")
'切割一行 ? len("rate-content-cash text-right print_hide")
Sdata = Mid(S, iStart + 48, iEnd - iStart - 48)
''切割位置+1繼續找
iStart = iEnd + 48
Cells(i, "C") = Sdata
'3.即期買入
'找換行位置
iStart = VBA.InStr(iStart, S, "text-right display_none_print_show print_width")
iEnd = VBA.InStr(iStart, S, "
")
'切割一行 ? len("rate-content-cash text-right print_hide")
Sdata = Mid(S, iStart + 48, iEnd - iStart - 48)
''切割位置+1繼續找
iStart = iEnd + 48
Cells(i, "D") = Sdata
'4.即期賣出
'找換行位置
iStart = VBA.InStr(iStart, S, "text-right display_none_print_show print_width")
iEnd = VBA.InStr(iStart, S, "
")
'切割一行 ? len("rate-content-cash text-right print_hide")
Sdata = Mid(S, iStart + 48, iEnd - iStart - 48)
''切割位置+1繼續找
iStart = iEnd + 48
Cells(i, "E") = Sdata
'分割為陣列
i = i + 1
Loop
'顯示訊息
MsgBox "下載完畢!!", vbInformation
End Sub
Public Sub 清除()
Range("B4:E22").ClearContents
End Sub
教學影音(完整版在論壇)：
教學影音完整版在論壇：
https://groups.google.com/forum/#!forum/scu_excel_vba2_86
課程特色：
1.如何將函數轉成VBA2.VBA與資料庫快速結合
EXCEL函數、
VBA程式設計與
資料庫是分別屬於
三個領域的知識，
但卻是目前大家都需要的一項專業技能，要把三者融合的很好實在非常不容易，
剛好我有近20年的VB程式設計與資料庫設計的經驗，
教EXCEL函數與相關課程也有多年，因此清楚如何把最重要的知識教給大家，
ADO資料庫設計的知識非常多，但根據我多年的設計實務經驗，
覺得最重要的是掌握SQL語言，就可以輕易的完成查詢、新增、修改與刪除等功能，
就可以輕易的完成自己想處理的大量資料，大大提高工作效率了!
上課用書是：
Excel函數&VBA其實很簡單(
http://www.books.com.tw/exep/prod/booksfile.php?item=0010457292)
Excel VBA 與資料庫整合大活用(
http://www.books.com.tw/exep/prod/booksfile.php?item=0010463634)
完整教學影音DVD申請:http://goo.gl/ZlBZE
論壇：
http://groups.google.com/group/labor_excel_vba?hl=zh-TW
相關學習：
1.EXCEL VBA設計(自強基金會2012)第4次上課
http://terry55wu.blogspot.tw/2012/03/excel-vba20124.html
2.如何把EXCEL"函數"變為 "VBA"？自強基金會2012第5次上課
http://terry55wu.blogspot.tw/2012/04/excel-vba.html
3.自強基金會2012第8次上課
http://terry55wu.blogspot.tw/2012/05/excel-vba20128.html
4.自強基金會2012第9次上課
http://terry55wu.blogspot.tw/2012/05/excel-vba20129.html
5.EXCEL_VBA與資料庫--自強基金會2012(Ending)
http://terry55wu.blogspot.tw/2012/07/excelvba-2012ending.html
EXCEL,VBA,函數東吳進修推廣部,EXCEL VBA 函數 程式設計 線上教學 excel vba 教學 excel vba指令教學 vba範例教學excel excel vba教學視頻 excel函數教學 excel函數 MYSQL
%E7%AF%84%E4%BE%8B.png)
