iT邦幫忙

0

陣列要如何取值？寫入Excel工作表內？

excel vba vba
lin520 2021-09-19 11:57:06 ‧ 1950 瀏覽

  • 分享至 

  • xImage

請問大師，陣列要如何取值？
問題：從網路下載資料後，利用Split方法，利用陣列(數組)，於運算視窗呈現A(0)(0)表示都分拆沒問題，但是寫入Excel工作表，卻出現空白，請問大師，不知那出錯，請大師指點！感恩！
https://ithelp.ithome.com.tw/upload/images/20210919/20006591KR6sdqjNXI.png

    Set theWS = Sheets("PRICE")
    With theWS
        .Cells.Clear
    
             Application.Calculation = xlCalculationManual
             Application.ScreenUpdating = False
             
             A = Array("t", "o", "h", "l", "c", "v")
             B = Array("""timestamp"":[", """open"":[", """high"":[", """low"":[", """close"":[", """volume"":[")
             
             t1 = Split(Split(Split(myText, """timestamp"":[")(1), "]")(0), Chr(44))
             ReDim k(UBound(B), UBound(t1))
             For i = LBound(B) To UBound(B)
                For j = LBound(t1) To UBound(t1)
                        If i = UBound(B) Then
                            A(i) = Split(Split(Split(myText, B(i))(1), "]")(0), Chr(44))
                            Cells(j + 2, i + 1) = A(i)
                        Else
                            A(i) = Split(Split(Split(Split(myText, B(i))(1), B(i + 1))(0), "]")(0), Chr(44))
                            Cells(j + 2, i + 1) = A(i)
                        End If
                Next j
             Next i
             Cells(2, 1).Resize(UBound(t1), UBound(B) + 1) = A

陣列(數組)資料如下：
{"data":[{"symbol":"4721.TWO","chart":{"meta":{"currency":"TWD","symbol":"4721.TWO","regularMarketTime":1631856600,"gmtoffset":28800,"timezone":"CST","exchangeTimezoneName":"Asia/Taipei","regularMarketPrice":117.5,"chartPreviousClose":115.5,"previousClose":115.5,"limitUpPrice":127,"limitDownPrice":104,"scale":3,"priceHint":2,"currentTradingPeriod":{"pre":{"timezone":"CST","start":1631840400,"end":1631840400,"gmtoffset":28800},"regular":{"timezone":"CST","start":1631840400,"end":1631856600,"gmtoffset":28800},"post":{"timezone":"CST","start":1631856600,"end":1631856600,"gmtoffset":28800}},"tradingPeriods":[[{"timezone":"CST","start":1631840400,"end":1631856600,"gmtoffset":28800}]],"dataGranularity":"1m","range":"1d","validRanges":["1d","5d","1mo","3mo","6mo","1y","2y","5y","10y","max"]},"timestamp":[1631840400,1631840460,1631840520,1631840580,1631840640,1631840700,1631840760,1631840820,1631840880,1631840940,1631841000,1631841060,1631841120,1631841180,1631841240,1631841300,1631841360,1631841420,1631841480,16318
41540,1631841600,1631841660,1631841720,1631841780,1631841840,1631841900,1631841960,1631842020,1631842080,1631842140,1631842200,1631842260,1631842320,1631842380,1631842440,1631842500,1631842560,1631842620,1631842680,1631842740,1631842800,1631842860,1631842920,1631842980,1631843040,1631843100,1631843160,1631843220,1631843280,1631843340,1631843400,1631843460,1631843520,1631843580,1631843640,1631843700,1631843760,1631843820,1631843880,1631843940,1631844000,1631844060,1631844120,1631844180,1631844240,1631844300,1631844360,1631844420,1631844480,1631844540,1631844600,1631844660,1631844720,1631844780,1631844840,1631844900,1631844960,1631845020,1631845080,1631845140,1631845200,1631845260,1631845320,1631845380,1631845440,1631845500,1631845560,1631845620,1631845680,1631845740,1631845800,1631845860,1631845920,1631845980,1631846040,1631846100,1631846160,1631846220,1631846280,1631846340,1631846400,1631846460,1631846520,1631846580,1631846640,1631846700,1631846760,1631846820,1631846880,1631846940,1631847000,1631847060,16318
47120,1631847180,1631847240,1631847300,1631847360,1631847420,1631847480,1631847540,1631847600,1631847660,1631847720,1631847780,1631847840,1631847900,1631847960,1631848020,1631848080,1631848140,1631848200,1631848260,1631848320,1631848380,1631848440,1631848500,1631848560,1631848620,1631848680,1631848740,1631848800,1631848860,1631848920,1631848980,1631849040,1631849100,1631849160,1631849220,1631849280,1631849340,1631849400,1631849460,1631849520,1631849580,1631849640,1631849700,1631849760,1631849820,1631849880,1631849940,1631850000,1631850060,1631850120,1631850180,1631850240,1631850300,1631850360,1631850420,1631850480,1631850540,1631850600,1631850660,1631850720,1631850780,1631850840,1631850900,1631850960,1631851020,1631851080,1631851140,1631851200,1631851260,1631851320,1631851380,1631851440,1631851500,1631851560,1631851620,1631851680,1631851740,1631851800,1631851860,1631851920,1631851980,1631852040,1631852100,1631852160,1631852220,1631852280,1631852340,1631852400,1631852460,1631852520,1631852580,1631852640,16318
52700,1631852760,1631852820,1631852880,1631852940,1631853000,1631853060,1631853120,1631853180,1631853240,1631853300,1631853360,1631853420,1631853480,1631853540,1631853600,1631853660,1631853720,1631853780,1631853840,1631853900,1631853960,1631854020,1631854080,1631854140,1631854200,1631854260,1631854320,1631854380,1631854440,1631854500,1631854560,1631854620,1631854680,1631854740,1631854800,1631854860,1631854920,1631854980,1631855040,1631855100,1631855160,1631855220,1631855280,1631855340,1631855400,1631855460,1631855520,1631855580,1631855640,1631855700,1631855760,1631855820,1631855880,1631855940,1631856000,1631856060,1631856120,1631856180,1631856240,1631856300,1631856360,1631856420,1631856480,1631856600],"indicators":{"quote":[{"close":[null,116.5,117,116,115.5,115.5,115.5,116,116.5,116.5,116,116,115.5,116,116,116,116,116,116.5,116,116,116,115,114.5,115,115.5,115,115,114.5,114.5,115,115,114.5,114,114,114,113.5,114,114,114.5,114.5,114.5,114.5,114.5,115,114.5,114.5,114.5,114.5,114,114.5,114.5,115,115,115,114.5,11
5,114.5,114.5,114.5,115,114.5,114.5,114.5,114.5,115,114.5,114.5,114.5,114.5,114,114,114.5,114.5,114.5,114.5,114.5,114.5,null,115,115,115,115.5,115.5,116,116,116,116,115.5,115.5,116,116,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,116,116,115.5,115.5,116,116,115.5,116,116,116,115.5,116,115.5,115.5,115.5,115.5,116,116,116,116,116,116,116,115.5,116,116,115.5,115.5,115.5,115.5,115.5,115.5,115,115.5,115.5,115.5,115.5,115.5,115.5,115.5,null,115,115,115.5,115.5,115.5,null,115,115,115,114.5,115,114.5,114.5,114.5,115,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,115,115,115,null,115,114.5,null,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,115,115,115,115,115,115,115.5,115.5,115,115,null,115,115,115,115.5,115,115.5,115.5,115.5,115,115,115,115.5,115,115,115,null,114.5,114.5,115,115,115,114.5,114.5,114.5,114.5,114.5,114.5,115.5,115.5,115.5,115,115.5,115,115,115,115,115,116,117.5,117.5,116.5,117.5,117.5,117,117,116.5,117,117,117,117.5,117,117,117,116.5,116.5,116.5,117,117.5,118,117,117.5,117.5,11
7,117.5,117.5,117.5,117.5,118,118,117.5,118,117.5,117.5,null,null,null,117.5],"high":[null,116.5,117,117,116.5,116,115.5,116,116.5,116.5,116.5,116,116,116,116,116,116,116,116.5,116.5,116,116,116,115.5,115,115.5,115.5,115,115,115,115,115,114.5,115,114.5,114.5,114,114,114,114.5,114.5,115,114.5,114.5,115,115,114.5,114.5,115,114.5,114.5,114.5,115,115,115,115,115,115,115,114.5,115,114.5,114.5,114.5,114.5,115,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,null,115,115,115,115.5,116,116,116,116.5,116.5,116,116,116,116,116,115.5,115.5,115.5,115.5,115.5,116,115.5,116,115.5,116,116,115.5,115.5,116,116,116,116,116,116,116,116,116,115.5,116,116,116,116,116,116,116,116,116,116,116,116,116,115.5,115.5,116,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,null,115.5,115.5,115.5,115.5,115.5,null,115.5,115,115,114.5,115,114.5,114.5,114.5,115,115,114.5,115,114.5,114.5,114.5,114.5,115,114.5,115,115,115,null,115,115,null,114.5,115,114.5,114.5,115,114.5,114.5,114.5,115,115,115,115,115,115.5,115
.5,115.5,115,115,null,115,115,115,115.5,115,115.5,115.5,115.5,115.5,115.5,115,115.5,115,115,115,null,115,114.5,115,115,115,114.5,115,114.5,114.5,114.5,114.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,116,117.5,118,117.5,117.5,117.5,117.5,117,116.5,117,117.5,117.5,117.5,117,117,117,117,116.5,116.5,117.5,117.5,118,117.5,117.5,117.5,117.5,117.5,117.5,117.5,118,118,118,118,118,118,117.5,null,null,null,117.5],"low":[null,115,116,116,115.5,115,115,115.5,115.5,116,115.5,115.5,115.5,115.5,115.5,115.5,116,115.5,116,116,116,115.5,115,114.5,114.5,115,115,115,114.5,114.5,115,114.5,114.5,114,114,114,113.5,113.5,113.5,114,114.5,114,114.5,114.5,114.5,114.5,114,114.5,114.5,114,114,114.5,115,115,115,114.5,115,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114,114,114,114,114.5,114.5,114.5,114.5,null,115,115,115,115,115.5,115.5,115.5,116,116,115.5,115.5,116,116,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115,115.5,116,116,115.5,115.5,115.5,116,115.5,116,115.5,115.5,115.5,116
,115.5,115.5,115.5,115.5,115.5,116,116,115.5,115.5,116,116,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115,115,115,115,115,115,115.5,115.5,115.5,115.5,null,115,115,115.5,115.5,115.5,null,115,115,115,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114,114,114.5,114.5,115,115,null,115,114.5,null,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,115,114.5,115,115,115,115,115,115,115,115,null,115,115,115,115,115,115,115.5,115.5,115,115,115,115,115,114.5,115,null,114.5,114.5,114.5,114.5,115,114.5,114.5,114.5,114.5,114.5,114.5,114.5,115,115,115,115,115,115,115,115,115,115,116,117,116.5,117,117,117,116.5,116.5,116.5,117,117,117,117,117,116.5,116.5,116.5,116.5,116.5,117,117.5,117,117,117.5,117,117.5,117,117,117.5,117.5,117.5,117.5,117.5,117,117,null,null,null,117.5],"open":[null,115.5,116.5,117,116,115.5,115.5,115.5,116,116.5,116.5,115.5,116,115.5,116,115.5,116,116,116,116,116,115.5,116,115,114.5,115.5,115.5,115,114.5,114.5,115,114.5,114.5,114.5,114,114,114,113.5,114,114,114.5,114.5,114.5,114.5,1
14.5,115,114,114.5,115,114.5,114.5,114.5,115,115,115,115,115,115,115,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,null,115,115,115,115,115.5,116,115.5,116,116,116,115.5,116,116,116,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,115.5,116,116,115.5,115.5,115.5,116,116,116,116,116,116,116,116,115.5,115.5,116,115.5,116,116,116,116,116,116,116,115.5,115.5,116,115.5,115.5,115.5,115.5,115.5,115.5,115,115,115,115.5,115.5,115.5,115.5,null,115.5,115,115.5,115.5,115.5,null,115.5,115,115,114.5,114.5,114.5,114.5,114.5,114.5,115,114.5,114.5,114.5,114.5,114.5,114.5,114.5,114.5,115,115,115,null,115,115,null,114.5,114.5,114.5,114.5,115,114.5,114.5,114.5,115,114.5,115,115,115,115.5,115,115,115,115,null,115,115,115,115,115,115,115.5,115.5,115.5,115.5,115,115,115,114.5,115,null,115,114.5,114.5,114.5,115,114.5,114.5,114.5,114.5,114.5,114.5,114.5,115.5,115.5,115.5,115,115,115,115.5,115.5,115,115,116,117.5,117.5,117,117.5,117,117,116.5,116.5,117,117,117,117,117
,117,117,116.5,116.5,117,117,117.5,117.5,117.5,117.5,117.5,117.5,117.5,117.5,117.5,118,118,117.5,118,117.5,117.5,null,null,null,117.5],"volume":[null,441,136,263,256,193,86,18,125,116,244,12,38,14,8,16,37,35,65,45,15,65,238,223,154,47,9,97,24,19,8,28,15,174,34,99,262,191,106,13,33,92,7,11,7,51,9,33,33,54,11,23,10,5,3,6,7,9,6,7,17,12,40,15,24,4,1,3,12,7,9,4,9,8,7,7,22,1,null,3,5,2,294,172,27,58,174,110,12,7,11,4,9,149,3,1,2,13,12,11,8,2,1,1,3,2,14,11,15,1,7,10,13,6,7,2,9,3,5,7,4,5,7,1,10,7,18,8,2,18,35,55,16,25,9,2,4,34,12,13,11,9,null,16,14,1,1,2,null,17,7,130,83,20,17,5,7,24,6,1,5,14,5,10,142,42,13,9,2,2,null,1,13,null,3,13,4,1,6,4,7,13,1,6,116,5,5,3,33,5,6,2,null,6,3,7,7,2,4,4,5,3,17,19,16,52,6,2,null,4,8,4,2,5,12,10,4,3,64,12,183,6,13,13,6,4,5,9,9,15,314,800,157,144,56,22,25,102,46,23,75,27,23,10,31,12,16,6,66,189,109,73,93,47,23,46,23,24,13,30,55,27,14,44,268,80,null,null,null,653]}]}}}],"meta":{}}

登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

1
海綿寶寶
iT邦大神 1 級 ‧ 2021-09-19 23:20:54
最佳解答 
Sub Macro1()
    txtData = getText
    
    arrTimestamp = getArray(txtData, "timestamp")
    Debug.Print arrTimestamp(1), UBound(arrTimestamp)
    
    arrClose = getArray(txtData, "close")
    Debug.Print arrClose(1), UBound(arrClose)
    
    arrHigh = getArray(txtData, "high")
    Debug.Print arrHigh(1), UBound(arrHigh)
    
    arrLow = getArray(txtData, "low")
    Debug.Print arrLow(1), UBound(arrLow)
    
    arrOpen = getArray(txtData, "open")
    Debug.Print arrOpen(1), UBound(arrOpen)
    
    arrVolume = getArray(txtData, "volume")
    Debug.Print arrVolume(1), UBound(arrVolume)

End Sub
Function getArray(ByVal pStr As String, ByVal tag As String) As Variant
    posTag = InStr(1, pStr, tag)
    posLeft = InStr(posTag, pStr, "[")
    posRight = InStr(posTag, pStr, "]")

    getArray = Split(Mid(pStr, posLeft + 1, posRight - posLeft - 1), ", ")
End Function
Function getText() As String
    strRet = ""
    
    strRet = strRet & "{"
    strRet = strRet & " ""data"": [{"
    strRet = strRet & "     ""symbol"": ""4721.TWO"","
    strRet = strRet & "     ""chart"": {"
    strRet = strRet & "         ""meta"": {"
    strRet = strRet & "             ""currency"": ""TWD"","
    strRet = strRet & "             ""symbol"": ""4721.TWO"","
    strRet = strRet & "             ""regularMarketTime"": 1631856600,"
    strRet = strRet & "             ""gmtoffset"": 28800,"
    strRet = strRet & "             ""timezone"": ""CST"","
    strRet = strRet & "             ""exchangeTimezoneName"": ""Asia/Taipei"","
    strRet = strRet & "             ""regularMarketPrice"": 117.5,"
    strRet = strRet & "             ""chartPreviousClose"": 115.5,"
    strRet = strRet & "             ""previousClose"": 115.5,"
    strRet = strRet & "             ""limitUpPrice"": 127,"
    strRet = strRet & "             ""limitDownPrice"": 104,"
    strRet = strRet & "             ""scale"": 3,"
    strRet = strRet & "             ""priceHint"": 2,"
    strRet = strRet & "             ""currentTradingPeriod"": {"
    strRet = strRet & "                 ""pre"": {"
    strRet = strRet & "                     ""timezone"": ""CST"","
    strRet = strRet & "                     ""start"": 1631840400,"
    strRet = strRet & "                     ""end"": 1631840400,"
    strRet = strRet & "                     ""gmtoffset"": 28800"
    strRet = strRet & "                 },"
    strRet = strRet & "                 ""regular"": {"
    strRet = strRet & "                     ""timezone"": ""CST"","
    strRet = strRet & "                     ""start"": 1631840400,"
    strRet = strRet & "                     ""end"": 1631856600,"
    strRet = strRet & "                     ""gmtoffset"": 28800"
    strRet = strRet & "                 },"
    strRet = strRet & "                 ""post"": {"
    strRet = strRet & "                     ""timezone"": ""CST"","
    strRet = strRet & "                     ""start"": 1631856600,"
    strRet = strRet & "                     ""end"": 1631856600,"
    strRet = strRet & "                     ""gmtoffset"": 28800"
    strRet = strRet & "                 }"
    strRet = strRet & "             },"
    strRet = strRet & "             ""tradingPeriods"": ["
    strRet = strRet & "                 [{"
    strRet = strRet & "                     ""timezone"": ""CST"","
    strRet = strRet & "                     ""start"": 1631840400,"
    strRet = strRet & "                     ""end"": 1631856600,"
    strRet = strRet & "                     ""gmtoffset"": 28800"
    strRet = strRet & "                 }]"
    strRet = strRet & "             ],"
    strRet = strRet & "             ""dataGranularity"": ""1m"","
    strRet = strRet & "             ""range"": ""1d"","
    strRet = strRet & "             ""validRanges"": [""1d"", ""5d"", ""1mo"", ""3mo"", ""6mo"", ""1y"", ""2y"", ""5y"", ""10y"", ""max""]"
    strRet = strRet & "         },"
    strRet = strRet & "         ""timestamp"": [1631840400, 1631840460, 1631840520, 1631840580, 1631840640, 1631840700, 1631840760, 1631840820, 1631840880, 1631840940, 1631841000, 1631841060, 1631841120, 1631841180, 1631841240, 1631841300, 1631841360, 1631841420, 1631841480, 1631841540, 1631841600, 1631841660, 1631841720, 1631841780, 1631841840, 1631841900, 1631841960, 1631842020, 1631842080, 1631842140, 1631842200, 1631842260, 1631842320, 1631842380, 1631842440, 1631842500, 1631842560, 1631842620, 1631842680],"
    strRet = strRet & "         ""indicators"": {"
    strRet = strRet & "             ""quote"": [{"
    strRet = strRet & "                 ""close"": [null, 116.5, 117, 116, 115.5, 115.5, 115.5, 116, 116.5, 116.5, 116, 116, 115.5, 116, 116, 116, 116, 116, 116.5, 116, 116, 116, 115, 114.5, 115, 115.5, 115, 115, 114.5, 114.5, 115, 115, 114.5, 114, 114, 114, 113.5, 114, 114],"
    strRet = strRet & "                 ""high"": [null, 116.5, 117, 117, 116.5, 116, 115.5, 116, 116.5, 116.5, 116.5, 116, 116, 116, 116, 116, 116, 116, 116.5, 116.5, 116, 116, 116, 115.5, 115, 115.5, 115.5, 115, 115, 115, 115, 115, 114.5, 115, 114.5, 114.5, 114, 114, 114],"
    strRet = strRet & "                 ""low"": [null, 115, 116, 116, 115.5, 115, 115, 115.5, 115.5, 116, 115.5, 115.5, 115.5, 115.5, 115.5, 115.5, 116, 115.5, 116, 116, 116, 115.5, 115, 114.5, 114.5, 115, 115, 115, 114.5, 114.5, 115, 114.5, 114.5, 114, 114, 114, 113.5, 113.5, 113.5,],"
    strRet = strRet & "                 ""open"": [null, 115.5, 116.5, 117, 116, 115.5, 115.5, 115.5, 116, 116.5, 116.5, 115.5, 116, 115.5, 116, 115.5, 116, 116, 116, 116, 116, 115.5, 116, 115, 114.5, 115.5, 115.5, 115, 114.5, 114.5, 115, 114.5, 114.5, 114.5, 114, 114, 114, 113.5, 114,],"
    strRet = strRet & "                 ""volume"": [null, 441, 136, 263, 256, 193, 86, 18, 125, 116, 244, 12, 38, 14, 8, 16, 37, 35, 65, 45, 15, 65, 238, 223, 154, 47, 9, 97, 24, 19, 8, 28, 15, 174, 34, 99, 262, 191, 106]"
    strRet = strRet & "             }]"
    strRet = strRet & "         }"
    strRet = strRet & "     }"
    strRet = strRet & " }],"
    strRet = strRet & " ""meta"": {}"
    strRet = strRet & "}    "
    
    getText = strRet
End Function
看更多先前的回應...收起先前的回應...
lin520 iT邦新手 4 級 ‧ 2021-09-20 09:15:12 檢舉

先謝謝海綿寶寶的解答，一時之間，尚須一點時間，好好研究大師的精華，若有不懂再請教大師，並祝您佳節愉快！

lin520 iT邦新手 4 級 ‧ 2021-09-20 12:08:57 檢舉

大師：
我研究了半天，還是請大師，說明一下兩個Function？getArray(ByVal pStr As String, ByVal tag As String) As Variant與Function getText()？
如果回到我的問題，該如何取值？

海綿寶寶 iT邦大神 1 級 ‧ 2021-09-20 13:02:28 檢舉

getText 不重要，只是用來產生測試資料
getArray 是用來取代你的「三重 split」的做法
你原程式的寫法最後得到的
是六個陣列
timestamp, close, high, low, open, volume
跟我用 getArray 取得的一樣

至於要如何寫入 Excel
就是用你的 Cells(row, col) 的寫法即可

lin520 iT邦新手 4 級 ‧ 2021-09-21 11:24:14 檢舉

大師
請教位和我使用
Cells(2, 1).Resize(UBound(t1), UBound(B) + 1) = A
卻無法取得值，而即時視窗卻顯示出正確的資料，A(0)(0)=1631840400直到A(5)(269)，Cells(2, 1).Resize(UBound(t1), UBound(B) + 1) = A
的方式有錯誤，但不知道錯在那？

lin520 iT邦新手 4 級 ‧ 2021-09-21 11:40:28 檢舉

我試著用大師的Function getArray，可以取出如附圖資料，發現好像仍需要再加工。請大師解惑？https://ithelp.ithome.com.tw/upload/images/20210921/20006591Z5Z8hUfC2u.png

lin520 iT邦新手 4 級 ‧ 2022-01-11 10:02:51 檢舉

謝謝！

登入發表回應
0
blanksoul12
iT邦研究生 5 級 ‧ 2021-09-24 09:07:12

另一做法好像不那麼複雜.
把你的 data set 放在 [a1] 然後

Sub aaa()

For i = 1 To [a1048576].End(xlUp).Row
    strText = strText & Cells(i, "a")
Next

With CreateObject("msscriptcontrol.scriptcontrol") '32bit 才可用
    .Language = "JavaScript"
    Set items = .eval("(" + strText + ")")
    Set da_ta = CallByName(items, "data", VbGet)
    For Each cha_rt In da_ta
        Set meta = CallByName(CallByName(cha_rt, "chart", VbGet), "meta", VbGet)
        MsgBox CallByName(meta, "chartPreviousClose", VbGet)
    Next
End With

End Sub

以上只是示範吧,我不知你確實要拿什麼資料出來,自己參考一下吧

看更多先前的回應...收起先前的回應...
lin520 iT邦新手 4 級 ‧ 2021-09-24 09:38:17 檢舉

先收下，慢慢研究，有問題再請教大師解惑！
感恩！

lin520 iT邦新手 4 級 ‧ 2021-09-24 10:32:52 檢舉

請教"msscriptcontrol.scriptcontrol"只能用32bit，我的是64bit，該如何？

blanksoul12 iT邦研究生 5 級 ‧ 2021-09-24 13:18:35 檢舉

這個比較複雜了,你試試以下

Public Declare PtrSafe Function PostMessage& Lib "user32" Alias "PostMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any)
Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Const WM_CLOSE = &H10
Sub aaa()

For i = 1 To [a1048576].End(xlUp).Row
    strText = strText & Cells(i, "a")
Next

With CreateObjectx86("MSScriptControl.ScriptControl") '32bit 才可用
    .Language = "JavaScript"
    Set items = .eval("(" + strText + ")")
    Set da_ta = CallByName(items, "data", VbGet)
    For Each cha_rt In da_ta
        Set meta = CallByName(CallByName(cha_rt, "chart", VbGet), "meta", VbGet)
        MsgBox CallByName(meta, "chartPreviousClose", VbGet)
    Next
End With

For Each oShellWnd In CreateObject("Shell.Application").Windows
    hWnd2 = FindWindow(vbNullString, "x86Host")
    If hWnd2 > 0 Then PostMessage hWnd2, WM_CLOSE, 0, ByVal 0&
    DoEvents
Next

End Sub
Function CreateObjectx86(Optional sProgID, Optional bClose = False)

    Static oWnd As Object
    Dim bRunning As Boolean

    #If Win64 Then
        bRunning = InStr(TypeName(oWnd), "HTMLWindow") > 0
        If bClose Then
            If bRunning Then oWnd.Close
            Exit Function
        End If
        If Not bRunning Then
            Set oWnd = CreateWindow()
            oWnd.execScript "Function CreateObjectx86(sProgID): Set CreateObjectx86 = CreateObject(sProgID): End Function", "VBScript"
        End If
        Set CreateObjectx86 = oWnd.CreateObjectx86(sProgID)
    #Else
        Set CreateObjectx86 = CreateObject(sProgID)
    #End If

End Function
Function CreateWindow()

    ' source http://forum.script-coding.com/viewtopic.php?pid=75356#p75356
    Dim sSignature, oShellWnd, oProc

    On Error Resume Next
    sSignature = Left(CreateObject("Scriptlet.TypeLib").GUID, 38)
    'CreateObject("WScript.Shell").Run "%systemroot%\syswow64\mshta.exe about:""about:<head><script>moveTo(-32000,-32000);document.title='x86Host'</script><hta:application showintaskbar=no /><object id='shell' classid='clsid:8856F961-340A-11D0-A96B-00C04FD705A2'><param name=RegisterAsBrowser value=1></object><script>shell.putproperty('" & sSignature & "',document.parentWindow);</script></head>""", 0, False
    CreateObject("WScript.Shell").Run "%systemroot%\syswow64\mshta.exe about:""about:<head><script>moveTo(-32000,-32000);document.title='x86Host'</script><hta:application showintaskbar=no /><object id='shell' classid='clsid:8856F961-340A-11D0-A96B-00C04FD705A2'><param name=RegisterAsBrowser value=1></object><script>shell.putproperty('" & sSignature & "',document.parentWindow);</script></head>""", 0, False
    Do
        For Each oShellWnd In CreateObject("Shell.Application").Windows
             Set CreateWindow = oShellWnd.GetProperty(sSignature)
             If Err.Number = 0 Then Exit Function
             Err.Clear
        Next
    Loop

End Function
blanksoul12 iT邦研究生 5 級 ‧ 2021-09-24 13:21:15 檢舉

MSScriptControl.ScriptControl 這東西在 64bit 時會 error 的,你可以 google 一下,大家一般也是用上面這個方法做,但有人說這有點像寫病毒的作法,只是用途不同.參考參考吧

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22195 篇
完賽人數
600 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙