这很新,请耐心等待。我需要从嵌入式Google地图中提取标记坐标-示例链接为http://www.picknpay.co.za/store-search,我想在搜索时提取在地图中生成的所有标记位置。考虑使用诸如ParseHub之类的服务,但在走那条路线之前,我想我会通过SO /我自己介绍一下。
与手动遍历所有标记并分别搜索其坐标相比,必须有一种更简便的方法来查找地图中存储的标记的坐标?
通过http://www.picknpay.co.za/store-search提供的链接提供的网页源HTML不包含必要的数据,它使用AJAX。网站http://www.picknpay.co.za提供了sorta API。响应以JSON格式返回。导航页面(例如在Chrome中),然后打开“开发人员工具”窗口(F12),“网络”选项卡,重新加载(F5)页面并检查记录的XHR。最相关的数据是URL返回的JSON字符串:
http://www.picknpay.co.za/picknpay/json/picknpay/zh-CN/modules/store_finder/findStores.json
您可以使用下面的VBA代码来检索信息,如上所述。将JSON.bas模块导入VBA项目以进行JSON处理。
Option Explicit
Sub Scrape_picknpay_co_za()
Dim sResponse As String
Dim sState As String
Dim vJSON As Variant
Dim aRows() As Variant
Dim aHeader() As Variant
' Retrieve JSON data
XmlHttpRequest "POST", "http://www.picknpay.co.za/picknpay/json/picknpay/en/modules/store_finder/findStores.json", "", "", "", sResponse
' Parse JSON response
JSON.Parse sResponse, vJSON, sState
If sState <> "Array" Then
MsgBox "Invalid JSON response"
Exit Sub
End If
' Convert result to arrays for output
JSON.ToArray vJSON, aRows, aHeader
' Output
With ThisWorkbook.Sheets(1)
OutputArray .Cells(1, 1), aHeader
Output2DArray .Cells(2, 1), aRows
.Columns.AutoFit
End With
MsgBox "Completed"
End Sub
Sub XmlHttpRequest(sMethod As String, sUrl As String, arrSetHeaders, sFormData, sRespHeaders As String, sContent As String)
Dim arrHeader
'With CreateObject("Msxml2.ServerXMLHTTP")
' .SetOption 2, 13056 ' SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
With CreateObject("MSXML2.XMLHTTP")
.Open sMethod, sUrl, False
If IsArray(arrSetHeaders) Then
For Each arrHeader In arrSetHeaders
.SetRequestHeader arrHeader(0), arrHeader(1)
Next
End If
.send sFormData
sRespHeaders = .GetAllResponseHeaders
sContent = .responseText
End With
End Sub
Sub OutputArray(oDstRng As Range, aCells As Variant)
With oDstRng
.Parent.Select
With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
.NumberFormat = "@"
.Value = aCells
End With
End With
End Sub
Sub Output2DArray(oDstRng As Range, aCells As Variant)
With oDstRng
.Parent.Select
With .Resize( _
UBound(aCells, 1) - LBound(aCells, 1) + 1, _
UBound(aCells, 2) - LBound(aCells, 2) + 1)
.NumberFormat = "@"
.Value = aCells
End With
End With
End Sub
对我来说输出如下:
顺便说一句,类似的方法适用于其他答案。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句