|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
试用了一下批量查询,可能会卡死。- Sub test()
- Dim url, html
- url = "http://www.cde.org.cn/transparent.do?method=spxlList&tasktype=xb&nowYearM=2014-04&acceptid=&applyTypeCde=IND&isTimetag=0&pageMaxNumber=360&pagenum=1"
- Set html = CreateObject("htmlfile")
- n = 2
- ar = [{"序号","受理号","进入中心时间","审评状态","药理毒理","临床","药学","备注","受理号","企业名称","办理状态","状态开始时间","通知时间","标准品回执收到日","收费情况","费用收到日","检验报告收到日","药品批准文号","通知内容"}]
- Cells.ClearContents
- Range("a2").Resize(1, UBound(ar)) = ar
- With CreateObject("msxml2.xmlhttp")
- .Open "get", url, False
- .send
- html.body.innerhtml = .responsetext
- Set tb = html.all.tags("tr")
- For i = 0 To tb.Length - 1
- If tb(i).bgcolor = "#f5fafe" Then
- n = n + 1
- For j = 0 To 3
- Cells(n, j + 1) = tb(i).Cells(j).innertext
- Next
- Cells(n, 8) = tb(i).Cells(7).innertext
- For j = 4 To 6
- If InStr(tb(i).Cells(j).innerhtml, "lamp_shut.gif") > 0 Then
- Cells(n, j + 1) = "灯灭"
- ElseIf InStr(tb(i).Cells(j).innerhtml, "lamp_y.jpg") Then
- Cells(n, j + 1) = "黄灯"
- ElseIf InStr(tb(i).Cells(j).innerhtml, "lamp.gif") Then
- Cells(n, j + 1) = "绿灯"
- End If
- Next
- strNO = Cells(n, 2)
- ar = get_url(strNO)
- Cells(n, 9).Resize(1, 11) = ar
- End If
-
- Next
- End With
- End Sub
- Public Function get_url(strNO)
- Dim url, html, postData, arr(1 To 12)
- url = "http://app1.sfda.gov.cn/datasearch/schedule/search.jsp?"
- url = url & "tableId=43&tableName=TABLE43&columnName=COLUMN464,COLUMN475"
- url = url & "&title=%E8%8D%AF%E5%93%81%E6%B3%A8%E5%86%8C%E8%BF%9B%E5%BA%A6%E6%9F%A5%E8%AF%A2"
- url = url & "&code=" & strNO
- Set html = CreateObject("htmlfile")
- With CreateObject("msxml2.xmlhttp")
- .Open "get", url, False
- .send
- html.body.innerhtml = .responsetext
- Set tb = html.all.tags("tr")
- For i = 24 To 34
- n = n + 1
- arr(n) = tb(i).Cells(1).innertext
- Next
- End With
- get_url = arr
- End Function
复制代码 |
评分
-
3
查看全部评分
-
|