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

alfidpan wrote:
但是利用您上次Barrons網站的方法，找出 "length"的方法，但找不出"length"

        





Set temp = Jsondata.GetKeys(json1)
keys = Split(temp, ",")
        
For i = 0 To UBound(keys)
      Set json2 = Jsondata.GetProperty(json1, keys(i))
      Debug.Print keys(i), Jsondata.GetProperty(json2, "date"), Jsondata.GetProperty(json2, "amount")
      '數字轉日期方式，請參考274樓的計算方式
 Next i

rainbowsperm wrote:
改版後 感覺好像table改成用span的方法 所以所有資料都會一次匯出在同一列裡
請問該怎麼修改迴圈的地方 讓它顯示正常

Sub moneydj_no_table()
    
    Dim Url As String, HTML As Object, GetXml As Object, temp, i As Integer

    Set HTML = CreateObject("htmlfile")
    Set GetXml = CreateObject("msxml2.xmlhttp")

    Url = "http://5850web.moneydj.com/z/zc/zcr/zcra/zcra_6203.djhtm"

    With GetXml
        .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

        HTML.body.innerhtml = .responsetext
        
        Set Table = HTML.all.tags("table")(1).Rows
        
        temp = Split(Replace(Table(0).Cells(0).innertext, " / ", "/"), vbNewLine)
        For i = 0 To UBound(temp)
            temp(i) = Trim(temp(i))
        Next i
        
    End With
    
    With Sheets("工作表1")
        .Cells.Clear
        .Range("a1").Resize(UBound(temp) + 1, 1) = Application.Transpose(temp)
        .Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True, TrailingMinusNumbers:=True
        .Columns.AutoFit
    End With
    
 
    Set HTML = Nothing
    Set GetXml = Nothing


End Sub

strainny wrote:
S大，不好意思,附上連結：http://5850web.moneydj.com/z/zc/zca/zca_3034.djhtm(恕刪)

 Set Table = HTMLsourcecode.getElementById("SysJustIFRAMEDIV")
 Call Clipboard_Past("temp", Table)
        
 Sheets("temp").Cells.ColumnWidth = 15
 Sheets("temp").Rows("1:1").Delete Shift:=xlUp
 Sheets("temp").Columns("A:A").Delete Shift:=xlToLeft
 Sheets("temp").Cells(1, 1).Select

Set Table = HTMLsourcecode.all.tags("table")(2).Rows
Call Cell_by_Cell("temp", Table)
        
Set Table = HTMLsourcecode.all.tags("table")(3).Rows
Call Cell_by_Cell("temp", Table)

alfidpan wrote:
網址可能是https://tachansec.moneydj.com/z/zc/zca/zca.djhtm?a=1101(恕刪)

strainny wrote:
1.可以請教下兩個分別所代表的涵義嗎(網路似乎找不到解釋的資料)
Set Table = HTMLsourcecode.all.tags("table")(tabrow).Rows
Set Table = HTMLsourcecode.getElementById("SysJustIFRAMEDIV")

strainny wrote:
Set Clipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
==>引號裡面的那一串更是看不懂,和網頁資訊有關嗎?

strainny wrote:
Clipboard.settext Table.innerhtml
Clipboard.putinclipboard