抓CSV的三種方法--台灣證券交易所

Sub test1()

Dim t: t = Timer

Workbooks.Open Filename:= _

"http://www.twse.com.tw/exchangeReport/FMNPTK?response=csv&stockNo=2330"

Debug.Print Format(Timer - t, "0.00秒")

MsgBox (Format(Timer - t, "0.00秒"))

End Sub

Sub test2()

Dim t: t = Timer

With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.twse.com.tw/exchangeReport/FMNPTK?response=csv&stockNo=2330", Destination:=Range("A1"))

    .WebSelectionType = xlAllTables

    .WebFormatting = xlWebFormattingNone

    .Refresh BackgroundQuery:=False

    .Delete

End With

Debug.Print Format(Timer - t, "0.00秒")

End Sub

Sub test3()

Dim t: t = Timer

Cells.Clear

Dim myXML As Object

Set myXML = CreateObject("Microsoft.XMLHTTP")

With myXML

    .Open "GET", "http://www.twse.com.tw/exchangeReport/FMNPTK?response=csv&stockNo=2330", False

    .send

    myText = convertraw(.responseBody)

    Debug.Print myText

   

    myText1s = Split(myText, Chr(10))

    i = 1

    For Each myText1 In myText1s

        myText2s = Split(myText1, """,""")

        j = 1

        For Each myText2 In myText2s

            Cells(i, j) = Replace(Replace(Replace(myText2, """,", ""), Chr(13), ""), Chr(34), "")

            j = j + 1

        Next

        i = i + 1

    Next

End With

Set myXML = Nothing

Debug.Print Format(Timer - t, "0.00秒")

End Sub

Function convertraw(rawdata)

Dim rawstr

Set rawstr = CreateObject("adodb.stream")

With rawstr

.Type = 1

.Mode = 3

.Open

.Write rawdata

.Position = 0

.Type = 2

.Charset = "Big5"  '通常這裡不是放UTF-8就是Big5

convertraw = .ReadText

.Close

End With

Set rawstr = Nothing

End Function

Sub getContent()  ' 這程式用抓CSV用不到(但我不知道原先應該用在哪裡，先存起來)

Dim t: t = Timer

Cells.Clear

Dim myXML As Object

Set myXML = CreateObject("Microsoft.XMLHTTP")

Dim myHTML As Object

Set myHTML = CreateObject("HTMLFile")

Set oWindow = myHTML.parentWindow

With myXML

    .Open "GET", "http://www.twse.com.tw/exchangeReport/FMNPTK?response=csv&stockNo=2330", False

    .send

    'Debug.Print .responseText

    oWindow.execScript "var a=" & .responseText & ";"

    arrayLength = oWindow.eval("a.fields.length")

    dataLength = oWindow.eval("a.data.length")

    For i = 0 To arrayLength - 1

        Cells(1, i + 1) = oWindow.eval("a.fields[" & i & "]")

        For j = 0 To dataLength - 1

            Cells(j + 2, i + 1) = oWindow.eval("a.data[" & j & "][" & i & "]")

        Next

   

    Next

End With

Set myXML = Nothing

Set myHTML = Nothing

Debug.Print Format(Timer - t, "0.00秒")

End Sub