VBA网抓教程.docx
- 文档编号:25996306
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:32
- 大小:25.32KB
VBA网抓教程.docx
《VBA网抓教程.docx》由会员分享,可在线阅读,更多相关《VBA网抓教程.docx(32页珍藏版)》请在冰豆网上搜索。
![VBA网抓教程.docx](https://file1.bdocx.com/fileroot1/2023-6/14/8c1ef31a-7cbe-4616-926e-98ab7eb3f6bf/8c1ef31a-7cbe-4616-926e-98ab7eb3f6bf1.gif)
VBA网抓教程
vba网抓常用方法:
1、xmlhttp/winhttp法:
用xmlhttp/winhttp模拟向服务器发送请求,接收服务器返回的数据。
优点:
效率高,基本无兼容性问题。
缺点:
需要借助如fiddler的工具来模拟http请求。
2、IE/webbrowser法:
创建IE控件或webbrowser控件,结合htmlfile对象的方法和属性,模拟浏览器操作,获取浏览器页面的数据。
优点:
这个方法可以模拟大部分的浏览器操作。
所见即所得,浏览器能看到的数据就能用代码获取。
缺点:
各种弹窗相当烦人,兼容性也确实是个很伤脑筋的问题。
上传文件在IE里根本无法实现。
(有实现方法?
请一定告诉我)
3、QueryTables法:
因为它是excel自带,所以勉强也算是一种方法。
其实此法和xmlhttp类似,也是GET或POST方式发送请求,然后得到服务器的response返回到单元格内。
优点:
excel自带,可以通过录制宏得到代码,处理table很方便。
代码简短,适合快速获取一些存在于源代码的table里的数据。
缺点:
无法模拟referer等发包头(如果你有在QT中模拟referer的方法,请一定告诉我)
SubMain()
DimstrTextAsString
WithCreateObject("MSXML2.XMLHTTP")'CreateObject("WinHttp.WinHttpRequest.5.1")'
.Open"POST","",False
.setRequestHeader"Content-Type","application/x-www-form-urlencoded"
.setRequestHeader"Referer",""
.Send
strText=.responsetext
Debug.PrintstrText
EndWith
EndSub
拷贝剪切板:
SubCopyToClipbox(strTextAsString)
'文本拷贝到剪贴板
WithCreateObject("new:
{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.SetTextstrText
.PutInClipboard
EndWith
EndSub
DongYu作业1.rar
(18.29KB,下载次数:
88)
2014-10-2117:
05上传
下载次数:
88
SubHomerWork1_1()
'新手:
DongYu
'作业:
1、网站:
'操作:
点击“今日在售产品”,获取今日在售产品第一页的数据。
DimxmlAsNewMSXML2.XMLHTTP,urlAsString,StAsString
Dimarr,brr,ar,i,c
url="
Withxml
.Open"GET",url,False
.send
St=.responseText
EndWith
St=Split(Split(St,"")
(1),"
")(0)
arr=Split(St,"")
ReDimbrr(1ToUBound(arr),1To9)
Fori=1ToUBound(arr)
ar=arr(i)
brr(i,1)=Split(Split(ar,"value='")
(1),"'")(0)+Split(Split(ar,"")
(1),"")(0)
brr(i,2)=Split(Split(ar,"")
(1),"")(0)
brr(i,3)=Split(Split(ar,"")
(1),"")(0)
brr(i,4)=Split(Split(ar,"")
(1),"")(0)
brr(i,5)=Split(Split(ar,"")
(2),"")(0)
brr(i,6)=Split(Split(ar,"")(3),"")(0)
brr(i,7)=Split(Split(ar,"")(4),"")(0)
brr(i,8)=Split(Split(ar,"")(5),"")(0)
brr(i,9)=Split(Split(Split(ar,"")(5),"")
(1),">")
(1)
Nexti
WithActiveSheet
.Cells.Clear
.Columns("D:
E").NumberFormatLocal="yyyy-m-d"
.[a1].Resize(1,10)=[{"对比","产品名称","银行","起售日","停售日","币种","管理期(月)","产品类型","预期收益(%)","收益"}]
.[b2].Resize(UBound(brr,1),9)=brr
EndWith
EndSub
Sub按钮2_单击()
Dimurl,html
url="
url=url&"&OC=PEK"'北京首都机场
url=url&"&DC=SHA"'上海虹口机场
url=url&"&dstDesp=GUANGZHOU%B9%E3%D6%DD"
url=url&"&dst2=CAN"
url=url&"&DD=2014-10-22"'查询日期
url=url&"&DT=7"
url=url&"&BD="
url=url&"&BT=7"
url=url&"&AL=ALL"'全部航空
url=url&"&DR=true"
url=url&"&image.x=33"
url=url&"&image.y=9"
url=url&"&Sn=87bf24142bc0c78727610871f373e0a7"
Sethtml=CreateObject("htmlfile")
WithCreateObject("msxml2.xmlhttp")
.Open"get",url,False
.send
html.body.innerhtml=.responsetext
Settb=html.all.tags("div")
Fori=0Totb.Length-1
Iftb(i).classname="menu_layout2"Ortb(i).classname="listone_layout"Ortb(i).classname="listtwo_layout"Ortb(i).classname="menu_content_small2"Then
n=n+1
Forj=0Totb(i).childnodes.Length-1
Cells(n,j+1)=tb(i).childnodes(j).innertext
Next
EndIf
Next
EndWith
EndSub
Sub作业1_2_获取航班信息数据()
'网站:
'操作:
点击“查询”,获取航班信息数据。
DimStAsString,Url$,arr,brr,Crr
DimS1$,S2$,i%,j%,rngAsRange
Url="
WithCreateObject("WinHttp.WinHttpRequest.5.1")
.Open"GET",Url,False
.Send
St=.responsetext
EndWith
'
IfInStr(St,"")<1Then
Cells(1,1)="抱歉!
没有满足条件的航班,请重新输入查询条件!
"
Else
St=Split(Split(St,"")
(1),"
")(0)
WithActiveSheet
Cells(1,1)=Split(Split(St,"")
(1),"")(0)
arr=Split(St,"")'航空公司分组
Fori=1ToUBound(arr)
S1=arr(i)
Crr=Split(S1,"")
ReDimbrr(1ToUBound(Crr)+2,1To5)'班次UBound(S1)+1,航空公司及机行+1,航线+1
'航空公司
brr(1,1)=Trim(Split(Split(S1,"")
(1),"
")(0))'中国东方航空公司
brr(1,2)=Trim(Split(Split(S1,"")
(1),"
")(0))'航班
brr(1,2)=Trim(Split(Split(brr(1,2),"font"">")
(1),"")(0))
brr(1,3)=Trim(Split(Split(S1,"")
(2),"
")(0))''机型:
333
'飞行线路
brr(2,1)=Trim(Split(Split(S1,"")
(1),"
")(0))'北京首都机场
brr(2,2)=Trim(Split(Split(S1,"")
(1),"
")(0))'(22:00)
brr(2,3)=Trim(Split(Split(S1,"")
(1),"")(0))'经停:
0
brr(2,4)=Trim(Split(Split(S1,"")
(2),"")(0))'上海虹桥机场
brr(2,5)=Trim(Split(Split(S1,"")
(2),"")(0))'(23:
55)
'飞行班次
Forj=1ToUBound(Crr)
S2=Crr(j)
'Debug.PrintS2
brr(2+j,1)=Trim(Split(Split(S2,"")
(1),"")(0))'票价
brr(2+j,2)=Trim(Split(Split(S2,"")
(1),"")(0))'舱位'
brr(2+j,3)=Trim(Split(Split(S2,"")
(1),"")(0))'票数'
'……
Nextj
Setrng=ActiveSheet.Cells(Rows.Count,1).End(xlUp).Offset(1,0)
rng.Resize(UBound(brr,1),5)=brr
Nexti
EndWith
EndIf
EndSub
Sub作业1_2_航空公司获取()
'网站:
'操作:
点击“查询”,获取航班信息数据。
DimstrTextAsString
WithCreateObject("MSXML2.XMLHTTP")
.Open"GET","False
.Send
strText=.responsetext
Debug.PrintByteToStr(.responseBody,"GB2312")
EndWith
EndSub
FunctionByteToStr(arrByte,strCharsetAsString)AsString
WithCreateObject("Adodb.Stream")
.Type=1'adTypeBinary
.Open
.WritearrByte
.Position=0
.Type=2'adTypeText
.Charset=strCharset
ByteToStr=.Readtext
.Close
EndWith
EndFunction
SubMain()
DimstrTextAsString
ConstsaltkeyAsString="oUuXXXX"'请复制你自己的Cookie粘贴到这里。
下同
ConstsidAsString="tXXXX"
ConstauthAsString="a30eEZTXXXXXXXXXXXXXXXXXXXX"
ConstcookiereportAsString="f1fXXXXXXXXXXXXXXXXXXXXXXXX"
ConstulastactivityAsString="84cXXXXXXXXXXXXXXXXXXXX"
ConsttouclickAsString="70a9vPXXXXXXXXXXXXXXXXXXXX"
Constmember_login_uidAsString="218917"
Constmember_login_sidAsString="tXXXX"
WithCreateObject("WinHttp.WinHttpRequest.5.1")
.Open"GET","",False
.setRequestHeader"Cookie",_
"5WOj_b676_saltkey="&saltkey_
&";5WOj_b676_sid="&sid_
&";5WOj_b676_auth="&auth_
&";5WOj_b676_cookiereport="&cookiereport_
&";5WOj_b676_ulastactivity="&ulastactivity_
&";5WOj_b676_touclick="&touclick_
&";5WOj_b676_member_login_uid="&member_login_uid_
&";5WOj_b676_member_login_sid="&member_login_sid
.Send
strText=.responsetext
Debug.PrintstrText
EndWith
EndSub
SubMain()
DimstrTextAsString
ConstsaltkeyAsString="oUuXXXX"
ConstauthAsString="a30eEZTXXXXXXXXXXXXXXXXXXXX"
WithCreateObject("WinHttp.WinHttpRequest.5.1")
.Open"GET","",False
.setRequestHeader"Cookie",_
"5WOj_b676_saltkey="&saltkey_
&";5WOj_b676_auth="&auth
.Send
strText=.responsetext
Debug.PrintstrText
EndWith
EndSub
SubMain()
DimstrTextAsString
WithCreateObject("MSXML2.XMLHTTP")'CreateObject("WinHttp.WinHttpRequest.5.1")
.Open"POST","False
.setRequestHeader"Content-Type","application/x-www-form-urlencoded"
'.setRequestHeader"Referer",""
.send"etpsId=150000012002040300047"
strText=.responseText
Debug.PrintstrText
EndWith
EndSub
SubMain()
DimstrTextAsString
WithCreateObject("WinHttp.WinHttpRequest.5.1")'CreateObject("MSXML2.XMLHTTP")'
.Open"POST","False
.setRequestHeader"Content-Type","application/x-www-form-urlencoded"
.setRequestHeader"Referer","
.send"etpsId=150000012002040300047"
strText=.responseText
Debug.PrintstrText
EndWith
EndSub
SubMain()
DimstrTextAsString
WithCreateObject("WinHttp.WinHttpRequest.5.1")
.Open"GET",":
8080/costRegulatory/project.do?
method=showProjectList&isVisitor=1&f_id=11011&t1413902083242",False
.setRequestHeader"Referer",":
8080/costRegulatory/user.do?
method=changeIndex&fareaId=1"
.setRequestHeader"Cookie","E0685A9F6B708A1F1039BF2322B82A35"
.Send
strText=.responsetext
Debug.PrintstrText
EndWith
EndSub
SubMain()
DimstrTextAsString
DimstrCookieAsString
WithCreateObject("WinHttp.WinHttpRequest.5.1")
.Option(6)=False'禁止重定向,以获取原网页信息
.Open"GET",":
8080/costRegulatory/user.do?
method=changeIndex&fareaId=1",False
.Send
strText=.getAllResponseHeaders'获取所有的回应头信息
Debug.PrintstrText:
Stop'在立即窗口里查看头信息
strCookie=Split(Split(strText,"Set-Cookie:
")
(1),";")(0)'取出Cookie值
EndWith
'在同一个winhttp对象里能保留cookie,为了体现设置cookie的作用,启用一个新的winhttp对象
WithCreateObject("WinHttp.WinHttpRequest.5.1")
.Open"GET","
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
-
VBA
教程
![提示](https://static.bdocx.com/images/bang_tan.gif)
冰豆网所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。