國家社科基金項目清理表

『壹』 國家社科基金項目申報數據導出，

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

運行這段代碼