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", "http://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", _
"http://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
其他相關學習：
- VBA處理大數據政府開放與快速做出查詢系統
- 如何將大量EMAIL自動串接與用OUTLOOK自動批次寄信
- 如何用VLOOKUP函數做飯店管理分享
- 東吳進修廣部88期的EXCEL VBA課又額滿了
- 如何計算年齡並格式化與用VLOOKUP查詢時數
- 如何在EXCEL VBA中快速刪除空白列
- EXCEL VBA大數據自動化設計上課心得分享
- 如何用EXCEL統計樂透彩中獎機率範例
- 如何用COUNTIF函數篩選黑名單(無須撰寫VBA)
- 如何將EXCEL公式改直接貼上修改為VBA
- 如何用EXCEL VBA設計銷貨單將EXCEL當資料庫
- 如何撰寫自動日期格式化的VBA程式
- 如何增加EXCEL VBA按鈕(86)
- 如何用VBA搜尋關鍵字後上色與複製到新工作表
- 如何用VBA搜尋關鍵字後上色與複製到新工作表
- EXCEL函數與VBA設計第6次上課(證照106與110)
- 如何增加EXCEL VBA按鈕
- 如何利用日期函數製作年曆VBA範例
- 如何用EXCEL VBA設計銷貨單分享
- 如何學會欄列鎖定技巧與VBA設計之二(範例：大型試算表)
- 如何學會欄列鎖定技巧與VBA設計之一(範例：大型試算表)
- 從EXCEL函數到VBA自動化(函數應用大升級)
- 如何將檢視與參照函數函數轉EXCEL VBA設計
- 提高工作效率_黑名單篩選範例分享
- 北市公訓處EXCEL函數進階班第1天上課
- 公訓處EXCEL進階課程分享
- 如何用VBA呼叫Outlook郵寄信件
- 如何顯示進度狀態列(ProgressBar)
- 如何批次下載股市資料到EXCEL中
- 如何建立表單與將EXCEL當成資料庫
- 如何讓用ACCESS將EXCEL的資料
- 如何排序工作表
- 如何將單次查詢改為批次查詢(EXCEL VBA自動化)
- 如何在EXCEL VBA的工作表隨機上顏色
- 如何快速在EXCEL中建立查詢系統(免資料庫)之2
- 如何精簡VBA程式與傳遞引數
- 如何快速在EXCEL中建立查詢系統(免資料庫)之1
- 如何將大量工作表整合在一個工作表
- 如何在EXCEL VBA新增沒有重複名稱工作表
- 如何自動下載YAHOO股市資料到EXCEL中
- 如何建立表單與將EXCEL當成資料庫使用
- 如何在EXCE VBA中複製工作表
- 如何在EXCEL VBA如何保護工作表
- 如何在EXCEL VBA新增沒有重複名稱工作表
- 如何在EXCEL工作表中移動
- EXCEL VBA辦公自動化_如何建立查詢系統
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
留言列表
01_EXCEL函數與VBA自動化教學 (4)
{{ article.title }}
