VBA 实现调用地图查询网页，匹配地址查询所属省份

本文链接：https://blog.youkuaiyun.com/qq_41957460/article/details/123131016

这个博客介绍了如何使用Excel VBA进行批量地址查询，通过调用网页接口获取省份信息，并根据查询结果实施物流配送策略。VBA代码实现了两个模块：模块1针对单个地址单元格查询，模块2循环遍历所有地址。在模块3中，根据地址省份及重量总和判断适用的物流公司，如当总重量达到一定阈值且地址为陕西省时选择‘跨越’，否则选择‘顺丰’。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

模块1，选中单个地址单元格查询省份

'查询选中地址
Sub addressAcquisition()
Dim ie As Object
Dim url As String
col = Application.WorksheetFunction.CountA(Columns(1))
'For i = 2 To col


'获取地址网址
url = "https://scorpionfree98.github.io/fetch_address_detail/Geocoder.html"

Set ie = CreateObject("InternetExplorer.Application")
'网页可见性
ie.Visible = False
            
ie.navigate url
'等待网页加载完毕
While ie.readyState <> 4 Or ie.Busy = True
    DoEvents
Wend

'输入搜索框
ie.Document.getElementById("address").Value = Selection.Value
'点击搜索
ie.Document.getElementById("geo").Click

While ie.readyState <> 4 Or ie.Busy = True
    DoEvents
Wend

ro = Selection.Row

'获取结果值
r = ie.Document.getElementById("province").Value
Cells(ro, 2) = r
'关闭网页
ie.Quit


'Next i
End Sub

模块2，循环查找所有地址

'查询所有地址
Sub addressAcquisitions()

Dim ie As Object
Dim url As String
col = Application.WorksheetFunction.CountA(Columns(1))
For i = 2 To col


'获取地址网址
url = "https://scorpionfree98.github.io/fetch_address_detail/Geocoder.html"

Set ie = CreateObject("InternetExplorer.Application")
'网页可见性
ie.Visible = False
            
ie.navigate url
'等待网页加载完毕
While ie.readyState <> 4 Or ie.Busy = True
    DoEvents
Wend

'输入搜索框
ie.Document.getElementById("address").Value = Cells(i, 1)
'点击搜索
ie.Document.getElementById("geo").Click

While ie.readyState <> 4 Or ie.Busy = True
    DoEvents
Wend


'获取结果值
r = ie.Document.getElementById("province").Value
Cells(i, 2) = r
'关闭网页
ie.Quit

Next i
End Sub

模块3，部分判断

Sub totalWeight()

Range("F11") = 0

'判断选择的地址是否相同
Add1 = Cells(Selection.Row, 1)
For Each d In Selection
Add2 = Cells(d.Row, 1)
If Add1 <> Add2 Then
MsgBox ("地址不同")
Exit For
Else
Add1 = Add2
End If
Next


For Each d In Selection

Range("F11") = Range("F11") + d.Value
province = Cells(d.Row, 2)
Next

If province = "陕西省" And Range("F11") >= 80 Then
   Range("F12") = "跨越"
    ElseIf province = "陕西省" And Range("F11") < 80 Then
    Range("F12") = "顺丰"
    ElseIf province <> "陕西省" And Range("F11") >= 30 Then
   Range("F12") = "跨越"
    ElseIf province <> "陕西省" And Range("F11") < 30 Then
    Range("F12") = "顺丰"
    End If


End Sub