使用VBA抓取AJAX页面

You may use the below VBA code to parse response and output result. It requires JSON.bas module to be imported to VBA project for JSON processing.Sub GoogleFinanceData() Dim sJSONString As String Dim vJSON As Variant Dim sState As String Dim aData() Dim aHeader() ' Retrieve Google Finance data With CreateObject("MSXML2.XMLHTTP") .Open "GET", "http://finance.google.com/finance/info?q=SHA:000001,INDEXSP:.INX,INDEXNIKKEI:NI225,INDEXHANGSENG:HSI,TPE:TAIEX,INDEXSTOXX:SX5E,INDEXEURO:PX1,INDEXTSI:OSPTX,INDEXASX:XJO,INDEXBOM:SENSEX,INDEXSWX:SMI,INDEXVIE:ATX,INDEXBVMF:IBOV,INDEXBKK:SET,INDEXIST:XU100,INDEXBME:IB,WSE:WIG,TADAWUL:TASI,BCBA:IAR,INDEXBMV:ME,IDX:COMPOSITE", False .Send If .Status <> 200 Then Exit Sub sJSONString = .responseText End With ' Trim extraneous chars sJSONString = Mid(sJSONString, InStr(sJSONString, "[")) ' Parse JSON string JSON.Parse sJSONString, vJSON, sState If sState = "Error" Then Exit Sub ' Convert to table format JSON.ToArray vJSON, aData, aHeader ' Results output With Sheets(1) .Cells.Delete .Cells.WrapText = False If UBound(aHeader) >= 0 Then OutputArray .Cells(1, 1), aHeader Output2DArray .Cells(2, 1), aData .Columns.AutoFit End WithEnd SubSub OutputArray(oDstRng As Range, aCells As Variant) With oDstRng .Parent.Select With .Resize(1, UBound(aCells) - LBound(aCells) + 1) .NumberFormat = "@" .Value = aCells End With End WithEnd SubSub Output2DArray(oDstRng As Range, aCells As Variant) With oDstRng .Parent.Select With .Resize( _ UBound(aCells, 1) - LBound(aCells, 1) + 1, _ UBound(aCells, 2) - LBound(aCells, 2) + 1) .NumberFormat = "@" .Value = aCells End With End WithEnd Sub因此，您需要的数据位于l_fix，c_fix，cp_fix列中.As a result the data you need is located in l_fix, c_fix, cp_fix columns.方法2.此外，您还可以通过类似于CAC 40的URL来制作XHR:Also you can make XHR by the URL like this one for CAC 40: "> https://www.google.co.uk/finance/getprices?q=PX1&x=INDEXEURO&i=120&p=20m&f=d,c,v ，o，h，l尤其是URL用于PX1股票和INDEXEURO股票交易所代码，间隔120秒，周期20分钟，响应数据d，c，v，o，h，l用于DATE(UNIX时间戳)，CLOSE，VOLUME，OPEN ，高，低.Particularly that URL is for PX1 stock and INDEXEURO stock exchange symbols, 120 sec interval, 20 minutes period, response data d,c,v,o,h,l is for DATE (UNIX TimeStamp), CLOSE, VOLUME, OPEN, HIGH, LOW.响应格式如下:EXCHANGE%3DINDEXEUROMARKET_OPEN_MINUTE=540MARKET_CLOSE_MINUTE=1050INTERVAL=120COLUMNS=DATE,CLOSE,HIGH,LOW,OPEN,VOLUMEDATA=TIMEZONE_OFFSET=120a1491405000,5098.75,5099.92,5098.75,5099.92,01,5100.51,5100.51,5098.09,5098.09,02,5099.63,5101.2,5099.29,5100.68,03,5099.83,5100.04,5099.07,5099.28,04,5098.19,5098.9,5097.71,5098.9,05,5098.56,5099.24,5097.99,5099.24,06,5097.34,5098.2,5096.14,5098.2,07,5096.52,5097.38,5095.66,5097.38,08,5093.27,5095.39,5093.27,5095.39,09,5094.43,5094.43,5092.07,5093.17,010,5088.18,5092.72,5087.68,5092.72,0应该对列表中的每个股票代码执行XHR，然后将结果合并到表中.The XHR should be done for each stock symbol in the list, then results should be consolidated into table. 这篇关于使用VBA抓取AJAX页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！