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

Dylan67

7分

831樓

Dylan67

個人積分：7分

文章編號：78369215

特來致歉，關於twse網址部分，版主813F已經做了很詳細的介紹了，這樓實在蓋得太高了，哈哈

Dylan67

7分

832樓

Dylan67

個人積分：7分

文章編號：78371095

版主求救

我用您425樓中油-汽、柴油公告牌價下載csv的範例來改，可是出現亂碼，想麻煩您給指導一下

Sub Post_REV()

Dim Xmlhttp As Object, Clipboard As Object
Set Clipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

ttt = Timer

Set Xmlhttp = CreateObject("Microsoft.XMLHTTP")

URL = "https://mops.twse.com.tw/server-java/FileDownLoad?step=9&functionName=show_file&filePath=/home/html/nas/t21/otc/&fileName=t21sc03_109_3.csv"

With Xmlhttp
.Open "POST", URL, False
.send
End With

With Clipboard
.SetText Xmlhttp.responsetext
.PutInClipboard
End With

With Sheets("Rev")
.Select
.Cells.Clear
.Cells(1, 1).Select
.PasteSpecial NoHTMLFormatting:=True
.Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True, TrailingMinusNumbers:=True
.Columns.AutoFit
.Cells(1, 1).Select
End With

MsgBox "下載時間" & Round(Timer - ttt, 2) & "秒", vbOKOnly, "Report"

Set Xmlhttp = Nothing
Set Clipboard = Nothing

End Sub

點我看大圖

snare

3150分

樓主

snare

個人積分：3150分

文章編號：78371309

Dylan67 wrote:
可是出現亂碼

一、
.SetText convertraw(Xmlhttp.responsebody)
utf-8
function請至其它樓層找

二、
200樓+793樓
URLDownloadToFile + ADODBStream

三、
其它樓層不下載(存檔)直接開啟範例

Dylan67

7分

834樓

Dylan67

個人積分：7分

文章編號：78425652

感恩版主,經提點已可順利下載,遲隔多日才表示謝意,是因為知道在學習上可能還會遇到問題,果不其然,應該是很簡單的字典運用,我搞了2天弄不出來,再煩請各位大德提點(詳附件註釋),問題是希望工作表Fs的股票代號,跟Rev工作表的股票代號經比對後,將Rev對應的數據粘貼到Fs表,謝謝

[點擊下載]

Dylan67

7分

835樓

Dylan67

個人積分：7分

文章編號：78427129

打擾版主,我試出來了,Fs及Rev工作表的股票代碼只要前面都加上字符(例如A)其他程式碼都不改(詳附件:字典-解決),就可以了,可是這是為什麼呢?能否請版主幫忙看一下程式碼及我寫的註釋,我是不是對字典有什麼不理解的地方,感謝,感謝

[點擊下載]

snare

3150分

樓主

snare

個人積分：3150分

文章編號：78431080

Dylan67 wrote:
Fs及Rev工作表的股票代碼只要前面都加上字符(例如A)其他程式碼都不改(詳附件:字典-解決),就可以了,可是這是為什麼呢?

一、對於scripting.dictionary的key，"文字"和"數字"是不同的，文字1不等於數字1
二、物件只需建立一次
三、如果只是copy資料，可用一行程式碼代替，不需用到字典物件
例如：Sheets("a").Range("d1:f3").Value = Sheets("b").Range("a1:c3").Value






Sub test()

    Dim arr As Range, sd As Object, i As Integer, ttt As Double
    Set sd = CreateObject("scripting.dictionary")
    Set arr = Sheets("Rev").Range("ac100:an" & Sheets("Rev").Range("A100").End(xlDown).Row)
    
    ttt = Timer
    Application.ScreenUpdating = False
    
    For i = 1 To arr.Rows.Count
        sd.Add Trim(arr(i, 1).Value), arr(i, 2).Resize(1, 11).Value
    Next i
    
    With Sheets("fs")
        .Range("is100:jc" & .Range("A100").End(xlDown).Row).Clear
        For i = 100 To .Range("A100").End(xlDown).Row
            .Cells(i, 253).Resize(1, 11) = sd.Item(Trim(.Cells(i, 252).Value))
        Next i
    End With
    
    Application.ScreenUpdating = True
    Debug.Print Timer - ttt
    Set sd = Nothing
    Set arr = Nothing
    
End Sub

Dylan67

7分

837樓

Dylan67

個人積分：7分

文章編號：78451622

感謝版主的協助，看您的代碼真是種享受，比起自己網路上參考模仿的，更容易學習及理解，

另外，想請教[公開資訊觀測站-彙總報表-法人說明會一覽表.csv]這個網頁，

我參考了778F-twse-庫藏股統計彙總表的代碼，但想用[另存CSV]的方式下載，

這是瀏覽網址：https://mops.twse.com.tw/mops/web/t100sb02_1

這是[另存CSV]按鈕的真實網址：https://mops.twse.com.tw/server-java/t105sb02?firstin=true&step=10&filename=t100sb02_1_20200724_14224091.csv

我的問題是，14224091.csv，這種以時間作為命名方式，有沒有克服的方法，請您提點，

或者，有那個樓層的範例可供學習，感謝

snare

3150分

樓主

snare

個人積分：3150分

文章編號：78466666

Dylan67 wrote:
這是[另存CSV]按鈕的真實網址：https://mops.twse.com.tw/server-java/t105sb02?firstin=true&step=10&filename=t100sb02_1_20200724_14224091.csv
我的問題是，14224091.csv，這種以時間作為命名方式，有沒有克服的方法(恕刪)

這是“暫時性”的網址

一、chrome(or other)

二、
*******
F12
*******

三、

四、

五、
POST查詢後，才會產生暫時性檔名


'……………
'……………
'……………        
        Url = "https://mops.twse.com.tw/mops/web/ajax_t100sb02_1"
        .Open "POST", Url, False
        '.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        '.setRequestHeader "Referer", "https://mops.twse.com.tw/mops/web/t100sb02_1"
        '.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        .send ("encodeURIComponent=1&step=1&firstin=1&off=1&TYPEK=sii&year=109&month=07&co_id=2412")
        
        HTMLsourcecode.body.innerhtml = .responsetext
 
        Set Table = ………
 建議不要管檔名，這裡下載table後，就結束程式碼，可以少一次查詢



'……………
'……………
'……………

'為了使用檔名，產生的第2次post查詢
        msgbox Split(Split(.responsetext, "'filename' value='")(1), "'>")(0)
        Url = "https://mops.twse.com.tw/server-java/t105sb02?firstin=true&step=10&filename=" & 暫時性檔名
        
        .Open "POST", Url, False
        '.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        '.setRequestHeader "Referer", "https://mops.twse.com.tw/mops/web/t100sb02_1"
        '.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        .send
        
        整理方式，833樓二 or 三
'……………
'……………
'……………

joehuang

20分

839樓

joehuang

個人積分：20分

文章編號：78477965

snare wrote:
這是“暫時性”的網址(恕刪)

你好樓主,
近日看到你的VBA教學,小弟獲益良多,謝謝!
以你最新的案例,我試著下了看看,發現下來的結果有1個地方與樓主下的不同,樓主的與網站直接存的CSV一樣,但不知這樣的網頁是要如何處理.

'方法一,我是用以你001樓的方式匯入資料到Excel
Set Table = HTMLsourcecode.all.tags("table")(0).Rows
For i = 0 To Table.Length - 1
For j = 0 To Table(i).Cells.Length - 1
ActiveSheet.Cells(i + 1, j + 1) = Table(i).Cells(j).innertext
Next j
Next i

'方法二,我是用CSV檔案直接用Clipboard貼上
CsvTempFile = Split(Split(.responsetext, "'filename' value='")(1), "'>")(0)
Url = "https://mops.twse.com.tw/server-java/t105sb02?firstin=true&step=10&filename=" & CsvTempFile

Set Xmlhttp = CreateObject("Microsoft.XMLHTTP")
With Xmlhttp
.Open "GET", Url, False
.send
End With

With Clipboard
.SetText convertraw(Xmlhttp.responsebody, "big5")
.PutInClipboard
End With

With Sheets("CSV方式")
.Select
.Cells.Clear
.Cells(1, 1).Select
.PasteSpecial NoHTMLFormatting:=True
.Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True, TrailingMinusNumbers:=True
.Columns.AutoFit
.Cells(1, 1).Select
End With

以上還請大師有空幫忙解惑一下.
謝謝!

snare

3150分

樓主

snare

個人積分：3150分

文章編號：78479934

joehuang wrote:
發現下來的結果有1個地方與樓主下的不同,樓主的與網站直接存的CSV一樣,但不知這樣的網頁是要如何處理.
'方法一,我是用以你001樓的方式匯入資料到Excel
(恕刪)
'方法二,我是用CSV檔案直接用Clipboard貼上

方法一
因為網頁上的空格、合併，是用html語法處理
所以用.tags("table")時會自動跳過html語法
但通常只有特定幾行而且有固定格式，用簡單的vba重新處理就好

方法二
沒什麼問題，直接下載csv後，再打開，也是一樣的排版
很多網站提供的csv檔，和網頁上的排版是不一樣的

如果不想另外用vba整理，要一模一樣，把table使用html格式貼上就好
但有些網頁因原始碼的關係，可能需要適當的增、減html語法
才能正確被excel識別為html格式