Ushbu maqolaga amal qiling va manzil uchun kenglik va uzunlik koordinatalarini olishingiz mumkin bo'lgan o'z geografik vositangizni yarating. Bu kabi konvertorlar odatda ko'chmas mulk brokerlari tomonidan qo'llaniladi.
Hozir yuklab olmoq
Agar siz dasturiy ta'minotdan iloji boricha tezroq foydalanishni boshlamoqchi bo'lsangiz, unda quyidagilarni qilishingiz mumkin:
Dasturiy ta'minotni hozir yuklab oling
Aks holda, agar siz DIY qilishni istasangiz, quyidagi tarkibni o'qishingiz mumkin.
Keling, GUI ni tayyorlaylik
Sizga kerak bo'lgan yagona narsa - bu bitta Excel varag'i va siz varaqni o'zingizning ehtiyojingizga qarab nomlashingiz mumkin. Ushbu misolda men standart varaq nomi "1-varaq" dan foydalanmoqdaman. Keyingi qadam bu varaqqa kerakli sarlavhalarni qo'shishdir. Agar kirish ma'lumotlari shahar atrofi, shtat, pochta indeksi va mamlakatni o'z ichiga olsa, kenglik va uzunlik konvertori biz o'tkazadigan manzil uchun aniq natija beradi. Rasmda ko'rsatilgandek, sarlavhalarni tayyorlang. Keling, oxirgi ikkita ustun sifatida kenglik va uzunlikni qo'shamiz. Shuningdek, foydalanuvchiga konvertatsiya qilish imkonini beradigan tugma kerak. Shunday qilib, keling, shaklni kiritamiz va uni tugma sifatida ko'rsatish uchun rang bilan to'ldiramiz.
Keling, uni funksional holga keltiraylik
Bu erda taqdim etilgan skript yangi modulga ko'chirilishi kerak. Ish daftaringizni so'l bilan faollashtirilgan ish kitobi fayli sifatida saqlashni unutmang. "FindThis" pastki qismi biz yaratgan tugmachaga biriktirilgan bo'lishi kerak.
Keling, sinab ko'raylik
Manzilni boshqa ma'lumotlar bilan birga tegishli ustunlarga qo'shing. Varaqda ko'rsatilgan barcha manzillar uchun kenglik va uzunlik koordinatalarini ko'rsatadigan makroni ishga tushirish uchun tugmani bosing. Makro 2-qatordan boshlanadi va bo'sh qatorga yetguncha ishlashda davom etadi.
U qanday ishlaydi?
Skript yordamida biz ikkita funktsiyani yaratdik. Biri lat qiymatini olish uchun, ikkinchisi esa Long qiymatini olish uchun. FOR siklidan foydalanib, biz har bir manzilni ushbu funksiyalarga o'tkazamiz va natijani ekranda ko'rsatamiz.
stsenariy
Function GETLAT(v_address As String, v_suburb As String, v_state As String, v_postcode As Long)
Dim URl As String, lastRow As Long
Dim xmlHttp As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
URl = "https://maps.googleapis.com/maps/api/geocode/xml?address=" & Application.WorksheetFunction.Substitute(v_address, " ", "+") & Application.WorksheetFunction.Substitute(v_suburb, " ", "+") & Application.WorksheetFunction.Substitute(v_state, " ", "+") & Application.WorksheetFunction.Substitute(v_postcode, " ", "+") & ",Australia"
Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
xmlHttp.Open "GET", URl, False
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send
Set html = CreateObject("htmlfile")
html.body.innerhtml = xmlHttp.ResponseText
v_string = html.body.innerhtml
x = InStr(1, v_string, "<LAT>")
If x <> 0 Then
y = InStr(x + 5, v_string, "</LAT>")
GETLAT = Mid(v_string, x + 5, y - (x + 5))
End If
End Function
Function GETLNG(v_address As String, v_suburb As String, v_state As String, v_postcode As Long)
Dim URl As String, lastRow As Long
Dim xmlHttp As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
URl = "https://maps.googleapis.com/maps/api/geocode/xml?address=" & Application.WorksheetFunction.Substitute(v_address, " ", "+") & Application.WorksheetFunction.Substitute(v_suburb, " ", "+") & Application.WorksheetFunction.Substitute(v_state, " ", "+") & Application.WorksheetFunction.Substitute(v_postcode, " ", "+") & ",Australia"
Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
xmlHttp.Open "GET", URl, False
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send
Set html = CreateObject("htmlfile")
html.body.innerhtml = xmlHttp.ResponseText
v_string = html.body.innerhtml
x = InStr(1, v_string, "<LNG>")
If x <> 0 Then
y = InStr(x + 5, v_string, "</LNG>")
GETLNG = Mid(v_string, x + 5, y - (x + 5))
End If
End Function
Sub FindThis()
For r = 2 To 5
Range("F" & r).Value = GETLAT(Range("A" & r).Value, Range("B" & r).Value, Range("C" & r).Value, Range("D" & r).Value)
Range("G" & r).Value = GETLNG(Range("A" & r).Value, Range("B" & r).Value, Range("C" & r).Value, Range("D" & r).Value)
Next r
End Sub
Agar siz skript yordamida kerakli natijalarni olmagan bo'lsangiz, excelning buzilganligi ehtimoliy sabab bo'lishi mumkin. Keyin foydalanishingiz mumkin Excel faylni tiklash vositasi kabi DataNumen Excel Repair Excelni tuzatish uchun.
Muallif kirish:
Nik Vipond - ma'lumotlarni qayta tiklash bo'yicha mutaxassis DataNumenMa'lumotlarni qayta tiklash texnologiyalari bo'yicha jahon yetakchisi bo'lgan , Inc doc muammosini tuzatish va istiqbolni tiklash dasturiy mahsulotlar. Qo'shimcha ma'lumot olish uchun tashrif buyuring www.datanumen.com