今天用EXCEL做了个小工具,自定义公式查询手机归属,打开EXCEL软件,单击菜单栏中的工具,选择"宏","Visua Basic",进入开发界面,在左边的资源管理器上单击右键,插入"模块",在模块中编辑读取网页数据代码
Private Function GetData(ByVal Url As String) As String
Dim xml_http As Object
Set xml_http = CreateObject("Microsoft.XMLHTTP")
xml_http.Open "get", Url, True
xml_http.sEnd
Do Until xml_http.ReadyState = 4
DoEvents
Loop
GetData = xml_http.responseText
Set xml_http = Nothing
End Function
图1
接着编写查询代码:
Public Function GetAttribution(ByVal hPoneNumber As String) As String
Dim tmp, city As String
Dim b
tmp = "这个地址不发,详情看图"
tmp = GetData(tmp)
If tmp = "" Then
GetAttribution = "网络错误"
Else
b = Split(tmp, "Province")
city = Mid(b(1), 4, InStr(b(1), ",") - 5)
b = Split(tmp, "City")
city = city & Mid(b(1), 4, InStr(b(1), ",") - 5)
GetAttribution = city
End If
End Function
图2
编写完了我们来测试一下:在EXCEL的A1单元格中填写手机号,B1中输入自定义公式=GetAttribution(A1),
结果就看大家的网速了,
图3
是不是很简单,