Map Excel Data 206j2n

MapExcelData.XLS

by

ProcessTrends.Com

and

Introduction to Google Earth (GE) and Excel Google Earth is a powerful and free mapping tool that lets s zoom into any location on earth. Here's a zoomed view of 1600 Pennsylvania Ave., Washington DC. Google Earth (GE) is free, just it here. Go here for free tutorials and advice on how to use GE. Mapping Excel Data in GE

http://earth.google.com/

GE uses latitude and longitude coordinates to map locations on earth. This workbook demonstrates how to get the latitude and longitude of Excel address locations. GE uses Keyhole Markup Language (KML) files to import data into GE for display as overlays to the basemap.. KML files are similar to XML type files, specialized for map display. Excel s can display Excel based data points in GE by creating a KML file with VBA and opening that file in GE. Here's an example of a map of the USA showing the major IT companies: Microsoft, Google and Yahoo. I've included JuiceAnalytics because this workbook uses their geocoding VBA code. This workbook includes VBA code prepared by JuiceAnalytics.Com and A Simon. See Acknowledgements sheet for details. The Configuration Steps sheet explains what you need to do to develop this capability on your desktop. You must with Yahoo to be able to geocode addresses and obtain a working version of Google Earth to be able to run this demo on your desktop.

Acknowledgements MapExcelData.Xls uses two web based tools and two Excel - VBA tools. The web based tools are Yahoo's geocoding service to assign latitude and longitude to addresses and Google Earth to map Excel locations. The two Excel VBA tools were prepared by A Simon and JuiceAnalytics. MapExcelData.Xls combines VBA code from JuiceAnalytics Geocoding tool.Xls and KML file generation code from A Simon's Excel2KML.Xls Simon's and JuiceAnalytics' VBA code has been incorporated into MapExcelData.XLS and modified by ProcessTrends.Com, Oct., 2007 to provide both geocoding capability (assign latitude and longitude based on address) and production of KML files. JuiceAnalytics VBA code is determines the latitude and longitude for locations given location as street address, street intersection or Zip code. A Simon's Excel2KML.XLS VBA code is used to KML files by using File_Details worksheet KML code fragments. Simon's VBA code processes the source data file with latitude and longitude and builds a KML file using the KML fragments from File_Details.

http://bbs.keyhole.com/ubb/showprofile.php?=105548 Original Excel2KML.XLS Created by simon_a:

Google Earth Link http://earth.google.com/index.html

Excel Geocoding Tool v2 - Juice Analytics

Configuration Steps - What you need to make Google Earth Maps with Excel Data Necessary Software and Registration 1. Google Earth - free from Google 2. ed Yahoo - needed to get latitude and longitude for each address. Free, need to Geocode Configuration Complete the Geocode Configuration sheet fields: Geocode Tool Yahoo ID Location of GoogleEarth.exe KML Details Enter output File path and Name Enter Document Name

Geocode Configuaration As Developed by JuiceAnalytics.Com

Settings Geocoder to use: Yahoo Yahoo or geocoder.us

Yahoo Id dkod_02703 To get access to the Yahoo geocoding API, go to: http://api.search.yahoo.com/webservices/_application More info on the Yahoo geocoding API is available here: http://developer.yahoo.net/maps/rest/V1/geocode.html

Geocoder.us name: Geocoder.us : To get a geocoder.us name and go to: http://geocoder.us//

Location of googleearth.exe: C:\Program Files\Google\Google Earth\googleearth.exe optional, to get Google Earth, go to http://earth.google.com

FALSE

Beep? if true, will beep every time an address is geocoded

Instructions Macros must be turned on to use this tool. To enable macros, go to the Tools, Macro, Security menu and change the security level to Medium. Then close and reopen this spreadsheet. When the spreadsheet opens, select "Enable Macros". You can test to see if macros are working by clicking on the button to the right. If macros are working, you will see a message. If not, nothing will happen. Fill in the location of the googleearth.exe and your geocoder.us name and . Enter addresses that you want to geocode in the Street, City, State, Zip columns on the Geocode page. Not all fields must be entered, but there must be a valid address and city+state or zip for geocoding to be performed. Select the rows that you want to geocode. You don't have to select the entire row, any cell in the row will do. Click the geocode selected rows button to start geocoding. About 2-3 rows are geocoded each second, depending on the speed of your Internet connection. If an address is not found, it may be because of a misspelling in the street address or city. To try again, fix the misspelling, then delete the "not found" values and geocode the row again. At any time you can see the geocoded addresses in Google Earth by clicking the "View in Google Earth" button. This will save all the geocoded addresses to a Google Earth "KML" file and open the file in Google Earth. The file will appear in the Temporary Places folder.

BeepOn Geocoder GeocoderToUse Geocodername GoogleEarthExecutableLocation

='Settings and Instructions'!$C$18 ='Settings and Instructions'!$C$12 ='Settings and Instructions'!$C$4 ='Settings and Instructions'!$C$11 ='Settings and Instructions'!$C$15

YahooId

='Settings and Instructions'!$C$7

B

C

KML Details As Developed by A Simon

1 Filepath Document name

C:\Data\google_Earth\kml_IT_co.kml KML File of Major IT Companies

5

File Header Code fragment 1



6

File Header Code fragment 2

2 3 4



7 8 9

Placemark Code fragment 1 Placemark Code fragment 2

10

Placemark Code fragment 3

11

Placemark Code fragment 4

,0 <description><![CDATA[ ]]>

12 13 Footer



14

15 Style

<Style id="a"> http://maps.google.com/mapfiles/ms/icons/red-dot.png

16 Placemark code fragment style

<styleUrl>#a

17 18 19

Step 1: Geocode Determine Lattitude & Longitude: Select Rows, Press Geocode Button

Latitude

38.97 37.42 37.42 47.64 41.13

Longitude

Precision

-77.38 address -122.03 address -122.09 address -122.13 street -73.7 city

Step 2: Generate KML File Update File_Details Sheet: Press Gen KML File Button

City State Address 555 grove street herndon va 701 first ave sunnyvale ca 1600 amphitheatre parkway mountain viewca One Microsoft Way Redmond Wa 1 New Orchard Road Armonk NY

Zip

Step 3: View Map in Google Earth: Press Run Google Earth Button

Name

20170 Juice Analytics 94089 Yahoo 94043 Google 98052-6399 Microsoft Corp 10504 IBM

Description

Great data analysis site. Good address matching service. Home of Google Earth Home of Excel The granddady of computer technology