(不定期更新)使用VBA解決 excel web 查詢無法匯入、匯入太慢的股市資料 (第11頁)

snare

snare
個人積分：3046分
文章編號：64514612

3046分

樓主

2017-05-28 0:23

yuehmao wrote:
,那就是這個例子中的 Table 其本質是否比較等同於 VBA 中的"陣列"一樣? 包括用法等等?
...(恕刪)

是的，用法一樣，只是要知道這個物件裡有什麼東西，才知道怎麼拿資料

以71樓範例來說

該網頁有2個表格
紅色範圍
Set Table = HTMLsourcecode.all.tags("table")(0).Rows
藍色範圍
Set Table = HTMLsourcecode.all.tags("table")(1).Rows

綠色範圍
因為合併格子的關係，會造成寬度(欄位)判斷錯誤
所以用第3列來決定，Table(2).Cells.Length

Set Table = HTMLsourcecode.all.tags("table")(1).Rows
這一行是指把網頁中第2個表格內的所有東西，設成一個集合物件

第1個table沒有任何意義，改成下面這樣也可以
Set aaabbbccc = HTMLsourcecode.all.tags("table")(1).Rows

改成 tags("image") 就是網頁中所有圖片集合(61樓範例)

要知道設成物件後的table 或 aaabbbccc ，裡面有什麼資料可以拿
最簡單的就是在set = 的下一行設一個中斷，然後看區域變數視窗
可以看到table，把它展開，可以發現裡面有很多物件集合
要抓的資料就在裡面

不過，既然用法一樣那為什麼不直接填入就好
Range("a1:o29") = Table
因為這個物件集合除了表格之外，還包含著各種不同的資料，html語法、線條…等等一大堆
直接填入會變成這樣

所以才要用迴圈，只取出每格內的文字後，再填入excel
For i = 0 To Table.Length - 1
For j = 0 To Table(i).Cells.Length - 1
debug.print Table(i).Cells(j).innertext
Next j
Next i

table(i).cells(j)對應位置如下
注意一下這裡的.cells(j)不是excel的功能
是CreateObject("htmlfile")這個物件的

對照之後 table(9).cells(2)，內容是14.30
所以如果只要某格的資料，也可以直接指定就好 aaabbbccc(10).cells(3)，不需跑迴圈

yuehmao

yuehmao
個人積分：37分
文章編號：64514840

37分

102樓

2017-05-28 0:56

snare wrote:
是的，用法一樣，只...(恕刪)

報告 snare大大
在下真是太感動了^^

您這篇解說文正是在下想了解的部份~~堪稱經典

在下先來研究,仔細吸收一下內文的東西。

bioleon69

bioleon69
個人積分：63分
文章編號：64514893

63分

103樓

2017-05-28 1:05

snare wrote:
是的，用法一樣，只是...(恕刪)

師傅...偏心了
當年您教導我的時候都沒講這麼細....
我還花好多時間研究

bioleon69

bioleon69
個人積分：63分
文章編號：64515750

63分

104樓

2017-05-28 7:17

snare wrote:
可以呀??Sub t...(恕刪)

師傅,嚇到了,你沒用那個60萬筆的資料作比喻
我還真感受不到寫法上的差異
以為效果有到就好了

----------------

師傅,記憶體這招有厲害
您身上到底還有多少寶物....

redim TempArray(Table.Length, Table(2).Cells.Length)
Dim ttt(1 To 60000, 1 To 10)

這就是所謂寫入記憶體?(速度快的方法)

請教師父,寫的過程
要如何print出記憶體內容呢?
----------------

Url = "http://www.cnyes.com/twstock/ps_historyprice/" & stock & ".htm"
Url_a = "code=" & stock & _
"&ctl00$Cont .....略

師傅,xml,winhttp,這種類型的抓取
只要把網頁變動的id接串成網址,就等於ie的模擬點擊了是嗎?

謝謝師傅師傅萬歲

snare

snare
個人積分：3046分
文章編號：64520567

3046分

樓主

2017-05-28 19:13

bioleon69 wrote:
這就是所謂寫入記憶體?
...(恕刪)

就是把資料放入陣列

bioleon69 wrote:
要如何print出記憶體內容呢?
...(恕刪)

從陣列取出資料

bioleon69 wrote:
只要把網頁變動的id接串成網址,就等於ie的模擬點擊了是嗎?
...(恕刪)

不是，因為沒有點擊的動作
嚴格來說是“代替”，不是等於

是直接跟網頁查詢所需要的資料
但這種查詢時網址不會變的網站，查詢的條件、變數需看網頁原始碼
過濾掉不要的變數，找出重要的變數
如下面4個變數(75樓範例)
code=
&ctl00$ContentPlaceHolder1$startText=
&ctl00$ContentPlaceHolder1$endText=
&ctl00$ContentPlaceHolder1$submitBut=

一但網頁改版，資料就抓不下來，需要重新看原始碼找變數
這也是為什麼我說 post 方式範例，比較適合用“抄、copy”的

ie object、web query，只要資料還在原來的位置，通常還可以抓下來
所以一般初學者用ie object、web query比較好，程式碼不用一直改
不過，如果資料量大，資料來源網頁太多，就算是初學者，還是不建議使用
有超長資料擷取時間的缺點

有興趣也可以去學python，比較容易上手
缺點是速度略輸 get post，還要另外裝軟體
沒辦法只用一個excel檔案，到處通用
phyton請自行找資料
phyton能辦到的，我全部都能用vba做到，所以沒興趣深入了解

bioleon69

bioleon69
個人積分：63分
文章編號：64520819

63分

106樓

2017-05-28 19:51

snare wrote:
不是，因為沒有點擊的...(恕刪)

了解,原來是在練習過程中
print的位置擺在錯誤地方
-----

感謝師傅指導,看來小弟用死背的
直接串起來就對了!!
這招好用!

目前只學vba 跟著師傅腳步

snare

snare
個人積分：3046分
文章編號：64521923

3046分

樓主

2017-05-28 22:06

因為71樓、75樓犯了一個低級錯誤 orz

，雖然不影響程式執行
不過會多了一列、一欄，的空白資料

所以補一篇用陣例的簡單範例，可以和21樓的程式碼，比較看看有什麼不同

範例網址：
臺灣證券交易所
三大法人買賣超日報(不含權證、牛熊證、可延展牛熊證)
http://www.twse.com.tw/zh/page/trading/fund/T86.html

照慣例，我會先google一下，還是那2種常見的寫法 sorry

這個網頁用 get 方法就可以了(21樓範例)，瞬間就可下載完成
因為很簡單，有看21樓範例的大概可以自己寫的出來

所以就拿這個網站當這次的範例，跟21樓只差在寫入儲存格方式的不同而已

'============================
Sub getstock()

Cells.Clear
Dim Url, HTMLsourcecode, XMLget, temparray() As Variant
Set HTMLsourcecode = CreateObject("htmlfile")
Set XMLget = CreateObject("msxml2.xmlhttp")

daytext = InputBox("日期(7碼數字)", , Format(Date, "yyyymmdd") - 19110000) + 19110000
Url = "http://www.twse.com.tw/fund/T86?response=html&date=" & daytext & "&selectType=ALLBUT0999"

ttt = Timer

With XMLget
.Open "GET", Url, False
.setRequestHeader "Cache-Control", "no-cache"
.setRequestHeader "Pragma", "no-cache"
.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
.send

HTMLsourcecode.body.innerhtml = .responsetext
If HTMLsourcecode.all.tags("table").Length = 0 Then
Cells(1, 1) = "很抱歉，沒有符合條件的資料!"
Exit Sub
End If

Set Table = HTMLsourcecode.all.tags("table")(0).Rows

ReDim temparray(Table.Length - 1, Table(1).Cells.Length - 1)

For i = 0 To Table.Length - 1
For j = 0 To Table(i).Cells.Length - 1
temparray(i, j) = Table(i).Cells(j).innerText
Next j
Next i
End With

With Sheets("sheet1")
.Range(.Cells(1, 1), .Cells(Table.Length, Table(1).Cells.Length)) = temparray()
End With

Set HTMLsourcecode = Nothing
Set XMLget = Nothing
Erase temparray()

Debug.Print Timer - ttt

End Sub

'=================================
這邊解釋一下，網頁上下載的表格長、寬，是從0開始計算
為了不浪費記憶體空間，所以定義陣列時要-1
ReDim temparray(Table.Length - 1, Table(1).Cells.Length - 1)
或是改成
ReDim temparray(1 to Table.Length,1 to Table(1).Cells.Length)
其它起始數值i=1，j=1，Table(i-1).Cells(j-1).innerText，也要跟著改
喜歡怎麼用就看個人喜好，只要注意範圍要一樣大就好

另外要特別注意一點，就是最大寬度(欄)
因為表格第1、2列，有時候會是標題，有時候會合併表格，所以最大寬度(欄)的數值會錯誤
最好從第2列Table(1).Cells.Length，或第3列Table(2).Cells.Length，取最大寬度(欄)

'=================================
其它日報代號，自動化請自行處理
修改位置 url 內的 selectType=??????
(注意大小寫)
ALL 全部
ALLBUT0999 全部(不含權證、牛熊證、可展延牛熊證)
0049 封閉式基金
0099P ETF
019919T 受益證券
0999 認購權證(不含牛證)
0999P 認售權證(不含熊證)
0999C 牛證(不含可展延牛證)
0999B 熊證(不含可展延熊證)
0999X 可展延牛證
0999Y 可展延熊證
0999GA 附認股權特別股
0999GD 附認股權公司債
0999G9 認股權憑證
01 水泥工業
02 食品工業
03 塑膠工業
04 紡織纖維
05 電機機械
06 電器電纜
07 化學生技醫療
21 化學工業
22 生技醫療業
08 玻璃陶瓷
09 造紙工業
10 鋼鐵工業
11 橡膠工業
12 汽車工業
13 電子工業
24 半導體業
25 電腦及週邊設備業
26 光電業
27 通信網路業
28 電子零組件業
29 電子通路業
30 資訊服務業
31 其他電子業
14 建材營造
15 航運業
16 觀光事業
17 金融保險
18 貿易百貨
23 油電燃氣業
9299 存託憑證
19 綜合
20 其他
CB 可轉換公司債
'========================

附加壓縮檔: 201712/mobile01-3e3039b391d66fe7d0266936704898c6.zip