使用JavaScript网页源Excel Web查询

我在这里是第一次,我在下面的代码,它的工作原理。

有一次,我更换下面的Java脚本的URL,它不会工作。 任何人都可以帮我吗? 非常感谢

http://q.stock.sohu.com/app2/history.up?method=history&code=cn_000099&sd=2012-2-14&ed=2012-3-14&t=d&res=js

strQuery =“htt​​p://q.stock.sohu.com/app2/history.up?method=history&code=”&strCode&“&SD =”&格式(dDate,“YYYY-MM-DD”)&“&ED = “&格式(dDate,”YYYY-MM-DD“)&”&T = D&解析度=​​ JS“

Sub GetData() Dim QuerySheet As Worksheet Dim DataSheet As Worksheet Dim EndDate As Date Dim StartDate As Date Dim Symbol As String Dim qurl As String Dim nQuery As Name Dim LastRow As Long Dim X As Range Dim B4 As String Dim i As Long, endRow As Long, j As Integer Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual complete = False bSymbolNotFound = False 'Greg Lovern StartDate = Sheets("Sheet3").Range("B2").Value EndDate = Sheets("Sheet3").Range("B3").Value Symbol = Sheets("Sheet3").Range("B4").Value Set DataSheet = ActiveSheet Range("C7").CurrentRegion.ClearContents 'Google qurl = "http://finance.google.com/finance/historical?q=" & Symbol qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _ "+" & Day(StartDate) & "+" & Year(StartDate) & _ "&enddate=" & MonthName(Month(EndDate), True) & _ "+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv" QueryQuote: 'Web query With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, _ Destination:=DataSheet.Range("C7")) .BackgroundQuery = True .TablesOnlyFromHTML = False On Error GoTo BadSymbol 'Greg Lovern .Refresh BackgroundQuery:=False On Error GoTo 0 'Greg Lovern .SaveData = True End With Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, other:=False Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm d/yy" Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00" Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000" Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00" 'If Google doesn't return "Adjusted Close", fill col I with "Close" values endRow = Range("G65536").End(xlUp).Row If DataSheet.Cells(endRow, "I") = "" Then For i = 7 To endRow DataSheet.Cells(i, "I").Value = DataSheet.Cells(i, "G").Value Next End If With ThisWorkbook For Each nQuery In Names If IsNumeric(Right(nQuery.Name, 1)) Then nQuery.Delete End If Next nQuery End With End Sub

分类:JavaScript的 时间:2015-03-15 人气:0
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 2.079 (s). 10 q(s)