Microsoft Project Run Multiple Instances Of Excel' title='Microsoft Project Run Multiple Instances Of Excel' />Geocoding using Microsoft Excel 2.Google Maps APIGeocoding is the process of converting addresses to the x and y coordinate or longitude and latitude.There are various option available in the market to perform geocoding, some are paid and some are free.You can use The Google Maps Geocoding API to retrieve the longitude and latitude for free.Later you can convert this longitude and latitude to your desire coordinate values, there are number of online tools for doing that.So it will be hassle to perform each addresses one at time in google, for that you can use Excel software to perform large number of addresses.You create excel function to perform the geocoding for multiple addresses.For this task I am using Microsoft Excel 2.First task is to turn on the Developer tab.To find this tab in 2.Go to the Microsoft File Options Customize Ribbon and check Developer.You will notice Developer tab is added on the ribbon.Microsoft Offices Teen Champions Share Their Best Tricks.Each year, hundreds of thousands of American teenagers compete to be the best at Word, Excel, and.Microsoft. The fiercely competitive software giant is positioning its wares for cloud computing with software and services.The companys two cash cows operating.Now click the Visual Basic button on the far left corner which will open the new window.Now we want a new module to insert which will store our new function.For that click on Insert New Module.Copy the below code and paste into the empty area code source policeanalyst.Function My. Geocodeaddress As String As String.Dim str. Address As String.Dim str. Query As String.Dim str. Latitude As String.Dim str. Longitude As String.Address URLEncodeaddress.Assemble the query string.Query http maps. Query str.Query address str.Address. str. Query str.Query sensorfalse.XML and HTTP components.Dim google. Result As New MSXML2.DOMDocument. Dim google.Service As New MSXML2.XMLHTTP. Dim o. Nodes As MSXML2.IXMLDOMNode. List.Dim o. Node As MSXML2.IXMLDOMNode. create HTTP request to query URL make sure to have.False there for synchronous operation.Service. Open GET, str.Query, False. google.Service. send. google.Result. Load. XML google.Service. response.Text. Set o. Nodes google.Result. get. Elements.By. Tag. Namegeometry.If o. Nodes. Length 1 Then.For Each o. Node In o.Nodes. str. Latitude o.Node. Child. Nodes0.Child. Nodes0. Text.Longitude o. Node.Child. Nodes0. Child.Nodes1. Text. My.Geocode str. Latitude, str.Longitude. My. Geocode Not Found try again, you may have done too many too fast.Public Function URLEncodeString.Val As String, Optional Space.As. Plus As Boolean False As String.Dim String. Len As Long String.Len LenString. Val.If String. Len 0 Then.Re. Dim resultString.Len As String. Dim i As Long, Char.Code As Integer. Dim Char As String, Space As String.If Space. As. Plus Then Space Else Space 2.For i 1 To String.Len. Char MidString.Val, i, 1. Char. Code AscChar.Select Case Char.Code. Case 9. 7 To 1.To 9. 0, 4. 8 To 5.Char. resulti Space.Case 0 To 1. 5. resulti 0 HexChar.Code. Case Else. HexChar.Code. End Select.URLEncode Joinresult,.End Function. This code make use of Microsofts XML library, so you have to add references to it.To add the reference go to Tools References.You will see list of references with the check box, select the Microsoft XML v.Ok button. Finally save your Visual basic window by pressing save button.Then from the drop down menu of Save as type select Excel Add In, assign the file name of your desire.Once the file in saved in.Close all the open excel files and open the new one.Open the new excel file and then go to file Options Add Inns.From the Manage drop down box select the Excel Add Ins.After selecting then press the GO button, this will list all the saved Add ins files, as I have saved as Book.Xlam I will check that box.Then press Ok after selecting,4. Sw Windows Media Player Dolby Surround Ii Plugin Firefox . As everything is done to perform the geocode, we are going to use the function to retrieve the longitude and latitude value.Let us suppose I have address 6.Hawthorn way, Olds, Alberta, Canada then you type that value in excel and use the function My.Geocode as you type my it will show function on the box.It will take few seconds to run the function because it has to pull the information from the google server and then result will display.Now if you have number of addresses and you want to run the geocode then this function will help you.Just keep in mind few things while using this function, after the execution process is done then you can copy and paste the values because each time you open the file then it will processing each cells which might take up few minutes to perform it.Second things as it is free they will not allow you to do perform thousands of geocode at once, so best way is to have a 1.If Google stop you for retrieving large number of addresses, you will see error saying Not Found try again, you may have done too many too fast.See below example, I have tried few number of addressee without following 1.I hope you liked this tutorial, please comment if you have any question regarding this.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |