Excel vba 读取上证指数

Public Sub 抓上海指数全部()
'删除上次结果sheet
delectsh



    Dim ie, dmt, an, selectONE
    Set ie = CreateObject("InternetExplorer.Application")    '创建一个IE对象
    With ie
        .Visible = False    '显示它
        .navigate "http://vip.stock.finance.sina.com.cn/corp/go.php/vMS_MarketHistory/stockid/000001/type/S.phtml"    '加载某个页面
        Do Until .ReadyState = 4    '等待页面加载完毕
            DoEvents
        Loop
        Set dmt = .document    '将IE浏览器加载的页面文档，赋予dmt变量
        Set selectONE = dmt.getElementsByName("year")
     
        With selectONE
        Set option_year = .Item(0)
           For i = 0 To option_year.Length - 1
           Dim year
           year = option_year.Item(i).innerText
              '创建年份sheet
              newsheet (year)
              抓上海指数按年 (year)
           Next i
        End With
        
        
    End With
    Set ie = Nothing
    
End Sub

Function 抓上海指数按年(year As String)
Dim URL, last_row, i
last_row = 0
Cells.Clear
For i = 1 To 4
'http://vip.stock.finance.sina.com.cn/corp/go.php/vMS_MarketHistory/stockid/000001/type/S.phtml?year=2012&jidu=2
URL = "http://vip.stock.finance.sina.com.cn/corp/go.php/vMS_MarketHistory/stockid/000001/type/S.phtml?year=" & year & "&jidu=" & i

Set sheet_year = Worksheets(year)
With sheet_year.QueryTables.Add(Connection:= _
                                     "URL;" & URL, _
                                     Destination:=sheet_year.Range("A" & last_row + 1))
                .WebTables = "FundHoldSharesTable"
                .Refresh BackgroundQuery:=False
            End With
            last_row = sheet_year.[A65536].End(xlUp).Row
Set sheet_year = Nothing

Next
End Function

Public Sub delectsh()
Dim c As Worksheet
For Each c In Worksheets
 If Left(c.name, 3) <> "set" Then '这里的“set”区分大小写
 Application.DisplayAlerts = False '删除时不用确认
 c.Delete
 End If
Next c
End Sub


Function newsheet(name As String)
    sname = name
    'Sheets(Sheets.Count).name
    'abc = DateSerial(Left(sname, 4), Mid(sname, 5, 2), Right(sname, 2)) + 1
    'sname = year(abc) & Format(Month(abc), "00") & Format(Day(abc), "00")
    '===========================================================
    Sheets.Add
    ActiveSheet.name = sname
    '===========================================================
End Function