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

是不是很簡單,