国家社科基金项目清理表

『壹』 国家社科基金项目申报数据导出，

Sub mydata()

Dim Html As Object, XmlHttp As Object, URL$, i%, arr(), t

Dim m%, n%, k%

Set Html = CreateObject("htmlfile")

Set XmlHttp = CreateObject("MSXML2.XmlHttp")

Application.ScreenUpdating = False

t = Timer

'If Not IsEmpty(ActiveSheet.UsedRange) Then ActiveSheet.UsedRange.ClearContents

'[A1:T1] = Array("项目批准号", "项目类别 ", "学科分类 ", "项目名称 " _

, "立项时间 ", "项目负责人 ", "专业职务 ", "工作单位 ", "单位类别", _

"所在省区市", "所属系统", "成果名称", "成果形式", "成果等级", _

"结项时间", "结项证书号", "出版社", "出版时间", "作者", "获奖情况")

For i = 1 To 2425 '这里抓了第1页到2425页

DoEvents

URL = "http://fz.people.com.cn/skygb/sk/index.php/Index/seach?&p=" & i

With XmlHttp

.Open "GET", URL, False

.Send

Html.body.innerhtml = .ResponseText

End With

Set tb = Html.all.tags("table")(2).Rows

For m = 1 To tb.Length - 1

k = k + 1

ReDim Preserve arr(1 To 20, 1 To k)

For n = 0 To tb(m).Cells.Length - 1

arr(n + 1, k) = tb(m).Cells(n).innertext

Next n

Next m

If i Mod 250 = 0 Then

Set Rng = Cells(Rows.Count, 1).End(3)(2)

Rng.Resize(k, 20) = WorksheetFunction.Transpose(arr)

Erase arr: k = 0

End If

Next i

If k > 0 Then

Set Rng = Cells(Rows.Count, 1).End(3)(2)

Rng.Resize(k, 20) = WorksheetFunction.Transpose(arr)

End If

Application.ScreenUpdating = True

MsgBox "耗时" & Timer - t & "秒!"

End Sub

运行这段代码