请问告诉如何自动抓取网页数据,然后读写到access数据库
http://www.czce.com.cn/portal/exchange/2011/datadaily/20111013.htm由于工作需要,需要对交易所的每日行情进行相关统计,之前是复制到excel里面,但是由于手工,非常低效,现在想学习vb将其自动抓取到数据库里面存储,并实现可以导出到excel进行图形绘制,希望高手能指导下, --------------------编程问答-------------------- xmlhttp+adodb --------------------编程问答-------------------- '此代码由“正则测试工具 v1.1.35”自动生成,请直接调用TestReg过程
Private Sub TestReg()
Dim i%, strData As String
Dim reg As Object
Dim matchs As Object, match As Object, submatch As Object
strData = getHtmlStr("http://cache.baidu.com/c?m=9f65cb4a8c8507ed4fece763105392230e54f732748687027fa3c215cc735b36163afef97a6256578dd82b2d50f51a00bfa72b72350023aacd9bce198be8c97664952736731f844a11d40eafbc1c639e34d507a9f916f0cbf125e3dac5a2d54325cd44040a9786894d7616dd1ef50346e8b19838022f13ad9a46&p=c03eda0a85cc41fb08e2957c4f52&user=baidu&fm=sc&query=site%3Awww%2Eczce%2Ecom%2Ecn+%C3%BF%C8%D5%D0%D0%C7%E9%B1%ED&qid=81b460f8008f66b7&p1=1")
Set reg = CreateObject("vbscript.regExp")
reg.Global = True
reg.IgnoreCase = False
reg.MultiLine = True
reg.Pattern = "<tr >\r\n<td >(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n"
Set matchs = reg.Execute(strData)
For Each match In matchs
For i = 0 To match.SubMatches.Count - 1
Debug.Print match.SubMatches(i),
Next
Debug.Print
Next
End Sub
Public Function getHtmlStr(strUrl As String) As String
Dim XmlHttp As Object
Set XmlHttp = CreateObject("Microsoft.XMLHTTP")
XmlHttp.Open "GET", strUrl, False
On Error GoTo Err_net
XmlHttp.send
getHtmlStr = BytesToBstr(XmlHttp.ResponseBody, "UTF-8")
Set XmlHttp = Nothing
Err_net:
End Function
CF109 20,235 20,250 20,250 20,250 20,250 20,250 15 15 64 0 -2,176 646.68 20,195
CF111 20,810 20,895 20,905 20,740 20,810 20,840 0 30 348 2,238 162 3,625.82
CF201 21,345 21,400 21,500 21,315 21,395 21,420 50 75 31,570 121,260 -4,606 338,081.89
CF203 21,540 21,560 21,690 21,470 21,555 21,595 15 55 478 3,966 -150 5,161.13
CF205 21,895 21,920 22,080 21,840 21,945 21,970 50 75 500,810 404,168 12,806 5,500,951.38
CF207 21,920 22,005 22,130 21,895 22,000 22,045 80 125 1,966 28,808 336 21,671.56
С 535,236 560,440 6,372 5,870,138.46
ER109 2,470 0 0 0 0 2,470 -2470 0 0 5,670 0 0.00 2,484
ER111 2,520 0 0 0 0 2,520 -2520 0 0 718 0 0.00
ER201 2,575 2,574 2,580 2,571 2,576 2,577 1 2 7,494 68,028 -832 19,308.73
ER203 2,642 0 0 0 0 2,639 -2642 -3 0 52 0 0.00
ER205 2,675 2,669 2,679 2,669 2,675 2,676 0 1 1,014 15,802 -84 2,713.06
ER207 2,672 2,670 2,679 2,670 2,673 2,673 1 1 16 172 -2 42.77
С 8,524 90,442 -918 22,064.56
RO109 10,280 0 0 0 0 10,280 -10280 0 0 0 -8,302 0.00 10,280
RO111 10,420 0 0 0 0 10,420 -10420 0 0 36 0 0.00
RO201 10,406 10,400 10,414 10,380 10,386 10,396 -20 -10 6,344 88,342 -114 32,972.03
RO203 10,570 0 0 0 0 10,570 -10570 0 0 2 0 0.00
RO205 10,696 10,700 10,710 10,670 10,670 10,688 -26 -8 5,870 21,970 1,644 31,366.68
RO207 10,828 0 0 0 0 10,828 -10828 0 0 2 0 0.00
С 12,214 110,352 -6,772 64,338.71
SR109 7,289 7,292 7,292 7,182 7,280 7,230 -9 -59 60 0 -2,548 433.79 7,415
SR111 7,195 7,156 7,170 7,130 7,157 7,148 -38 -47 1,712 11,858 -36 12,237.46
SR201 6,910 6,888 6,906 6,842 6,886 6,881 -24 -29 227,558 426,858 -10,410 1,565,898.44
SR203 6,889 6,846 6,860 6,808 6,839 6,841 -50 -48 484 2,864 -224 3,310.92
SR205 6,774 6,740 6,778 6,688 6,749 6,739 -25 -35 1,003,770 435,060 -28,954 6,764,546.83
SR207 6,810 6,758 6,802 6,741 6,782 6,782 -28 -28 88 1,242 4 596.82
SR209 6,818 6,799 6,815 6,734 6,790 6,779 -28 -39 9,788 67,606 -74 66,349.39
SR211 6,835 6,791 6,810 6,778 6,802 6,800 -33 -35 34 872 -4 231.21
SR301 6,697 6,680 6,719 6,650 6,690 6,689 -7 -8 770 1,464 10 5,150.70
С 1,244,264 947,824 -42,236 8,418,755.56
TA109 10,740 10,650 10,650 10,650 10,650 10,650 -90 -90 2,768 0 -8,798 14,551.78 10,512
TA110 10,350 10,300 10,342 10,300 10,342 10,306 -8 -44 36 3,930 28 185.48
TA111 10,364 10,316 10,316 10,246 10,246 10,288 -118 -76 192 1,034 -124 987.64
TA112 10,170 10,138 10,170 10,078 10,116 10,146 -54 -24 308 1,698 50 1,562.46
TA201 10,140 10,136 10,170 10,066 10,106 10,128 -34 -12 1,207,002 368,632 -25,048 6,111,709.68
TA202 10,136 10,132 10,138 10,102 10,102 10,126 -34 -10 12 302 12 60.75
TA203 10,190 10,148 10,148 10,072 10,072 10,124 -118 -66 14 140 10 70.86
TA204 10,032 0 0 0 0 10,016 -10032 -16 0 58 0 0.00 --------------------编程问答-------------------- 重发下,应该不需要用utf8模式的。
'此代码由“正则测试工具 v1.1.35”自动生成,请直接调用TestReg过程
Private Sub TestReg()
Dim i%, strData As String
Dim reg As Object
Dim matchs As Object, match As Object
strData = getHtmlStr("http://cache.baidu.com/c?m=9f65cb4a8c8507ed4fece763105392230e54f732748687027fa3c215cc735b36163afef97a6256578dd82b2d50f51a00bfa72b72350023aacd9bce198be8c97664952736731f844a11d40eafbc1c639e34d507a9f916f0cbf125e3dac5a2d54325cd44040a9786894d7616dd1ef50346e8b19838022f13ad9a46&p=c03eda0a85cc41fb08e2957c4f52&user=baidu&fm=sc&query=site%3Awww%2Eczce%2Ecom%2Ecn+%C3%BF%C8%D5%D0%D0%C7%E9%B1%ED&qid=81b460f8008f66b7&p1=1")
Set reg = CreateObject("vbscript.regExp")
reg.Global = True
reg.IgnoreCase = False
reg.MultiLine = True
reg.Pattern = "<tr >\r\n<td >(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n<td class=""td-right"">(.+?)</td>\r\n"
Set matchs = reg.Execute(strData)
For Each match In matchs
For i = 0 To match.SubMatches.Count - 1
Debug.Print match.SubMatches(i),
Next
Debug.Print
Next
End Sub
Private Function getHtmlStr(strUrl As String) As String
Dim XmlHttp As Object
Set XmlHttp = CreateObject("Microsoft.XMLHTTP")
XmlHttp.Open "GET", strUrl, False
XmlHttp.send
getHtmlStr = StrConv(XmlHttp.ResponseBody, vbUnicode)
Set XmlHttp = Nothing
End Function
TA109 10,740 10,650 10,650 10,650 10,650 10,650 -90 -90 2,768 0 -8,798 14,551.78 10,512
TA110 10,350 10,300 10,342 10,300 10,342 10,306 -8 -44 36 3,930 28 185.48
TA111 10,364 10,316 10,316 10,246 10,246 10,288 -118 -76 192 1,034 -124 987.64
TA112 10,170 10,138 10,170 10,078 10,116 10,146 -54 -24 308 1,698 50 1,562.46
TA201 10,140 10,136 10,170 10,066 10,106 10,128 -34 -12 1,207,002 368,632 -25,048 6,111,709.68
TA202 10,136 10,132 10,138 10,102 10,102 10,126 -34 -10 12 302 12 60.75
TA203 10,190 10,148 10,148 10,072 10,072 10,124 -118 -66 14 140 10 70.86
TA204 10,032 0 0 0 0 10,016 -10032 -16 0 58 0 0.00
TA205 10,070 10,100 10,100 10,000 10,002 10,046 -68 -24 15,464 30,360 1,962 77,678.22
TA206 10,036 10,056 10,056 10,042 10,042 10,050 6 14 6 34 0 30.15
TA207 10,062 0 0 0 0 10,044 -10062 -18 0 10 0 0.00
TA208 10,138 0 0 0 0 10,106 -10138 -32 0 6 0 0.00
小计 1,225,802 406,204 -31,908 6,206,837.02
WS109 2,584 2,576 2,576 2,576 2,576 2,576 -8 -8 20 20,612 -600 51.52 2,584
WS111 2,642 2,637 2,637 2,635 2,635 2,636 -7 -6 40 5,556 -20 105.42
WS201 2,718 2,716 2,716 2,708 2,709 2,711 -9 -7 14,454 112,994 2,990 39,189.02
WS203 2,785 0 0 0 0 2,782 -2785 -3 0 80 0 0.00
WS205 2,827 2,820 2,827 2,817 2,818 2,822 -9 -5 6,758 49,282 196 19,071.60
WS207 2,787 2,780 2,780 2,770 2,780 2,777 -7 -10 18 1,196 -14 49.98
WS209 2,974 2,966 2,975 2,964 2,971 2,972 -3 -2 264 11,250 26 784.57
WS211 3,002 0 0 0 0 3,002 -3002 0 0 38 0 0.00
WS301 3,038 3,025 3,036 3,025 3,030 3,030 -8 -8 24 546 -4 72.72
小计 21,578 201,554 2,574 59,324.83
WT109 2,027 2,020 2,020 2,020 2,020 2,020 -7 -7 12 246 -12 24.24 1,993
WT111 2,091 2,092 2,094 2,090 2,093 2,092 2 1 80 3,438 48 167.37
WT201 2,153 2,156 2,157 2,149 2,156 2,154 3 1 290 5,144 -60 624.72
WT203 2,198 2,191 2,197 2,191 2,192 2,192 -6 -6 16 642 2 35.07
WT205 2,243 2,252 2,256 2,248 2,254 2,254 11 11 304 2,256 -82 685.18
WT207 2,282 0 0 0 0 2,282 -2282 0 0 66 0 0.00
小计 702 11,792 -104 1,536.58
总计 3,048,320 2,328,608 -72,992 20,642,995.72
补充:VB , 数据库(包含打印,安装,报表)