「每日Excel」用Excel实现网页填表和返回数据到Excel

目地：打开网页按要求从服务器查询数据并填入制定格式表格

步骤：1.新建用户窗体

2.打开VBE—视图—工具箱，选取Webbrowser控件1个，Lable控件2个，Textbox控件2个，CommandButton控件2个。

先声明延时API

Private Declare Function GetTickCount& Lib "kernel32" ()

在登录按钮写入以下代码，用作登录使用

Private Sub CommandButton1_Click()

On error Resume Next

Dim vDoc As Object

Set vDoc=Webbrowser1.Document

With vDoc

. all("username"). value=Textbox1

. all("password"). value=Textbox2

.all("submit"). Click

End with

Set vDoc=Nothing

End With

End Sub

在查询按钮写下以下代码：

Private Sub Search_Click()

On error resume next

Dim CellsR as long,CellsC as Byte,i as integer,ii as integer

If Msgbox("请把第一列存放识别码，准备好了请选择“是”，否则请点击“否”",vbYesNo+vbQuestion,"操作前提示")=vbYes Then

CellsR=Cells(Rows.count,1).End(xlup). Row

With Webbrowser. Document

For i = 2 to CellsR

.getelementbyid("nsrxxForm_nsrsbh"). Focus

.getelementbyid("nsrxxForm_nsrsbh"). Value=ActiveSheet. Cells(i,1)

.getelementbyid("nsrxxForm_djzclxMc"). Focus

.getelementbyid("nsrxxForm_nsrsbh"). Click

While Webbrowser. ReadyState<> 4 Or Webbrowser. Busy=True

delay 200

Wend

[B1:I1]=[{"名称","法人姓名","法人联系电话","财务姓名","财务联系电话","办事员姓名","办事员联系电话","注册类型"}]

ActiveSheet.Cells(i,2)=.getelementbyid("nsrxxForm_nsrmc"). Value

ActiveSheet.Cells(i,3)=.getelementbyid("nsrxxForm_fddbrxm"). Value

ActiveSheet.Cells(i,4)=.getelementbyid("nsrxxForm_ryddryddh"). Value

ActiveSheet.Cells(i,5)=.getelementbyid("nsrxxForm_cwfzrxm"). Value

ActiveSheet.Cells(i,6)=.getelementbyid("nsrxxForm_cwfzryddh"). Value

ActiveSheet.Cells(i,7)=.getelementbyid("nsrxxForm_bsyxm"). Value

ActiveSheet.Cells(i,8)=.getelementbyid("nsrxxForm_bsyyddh"). Value

Next

Msgbox "操作全部完成"

End With

End If

End Sub

'延时函数

Private Function delay(ms as long)

Dim starttim

starttim=GetTickCount

Do

DoEvents

Loop Until GetTickCount>=starttim+ms

End Function

引用数据表

执行后的效果图

编者按：

手机“扣”字太不容易了，能不能给个赞[呲牙][呲牙][呲牙]，原创文章不容易[捂脸][捂脸][捂脸]，给个关注行不行？还是老话，有啥需求或功能，笔者若能力范围的尽量给予帮助！