今天用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

是不是很简单,