Automate Excel

Here’s my first attempt at using Google maps in excel (download below). Currently I can input an address and have a Google map of the address displayed in Excel, with most of the cool google functionality.

A couple thumbnails of the Excel Google Map Spreadsheet, click to view the larger images.

Normal View

Zoomed Hybrid View

The Google Map Spreadsheet uses two API’s to work, the Geocoder.us Api and Google Maps Api. I thought about also throwing in the current weather report, but refrained for now.

So how does it work?

Summary:
1. The address is sent to Geocoder.us to be converted to Latitude and Longitude (required to map a location on google maps), and the result is returned to the spreadsheet.

2. Excel sends this geocoded information to the automateexcel.com server, where I have a google map that receives latitude and longitude as variables and displays the respective map via the Google Map API.

3. Finally there’s a web browser control in Excel that navigates to this new address.

A bit more:
1. To experiment with sending an address and having Geocoder.us return the Latitude and Longitude back to Excel, I created a Geocoding workbook to experiment with.

2. Click this link to see my webpage that receives latitude and longitude as variables and returns the respective map (look in the address bar). If you’d like to create a similar page you can view my page code here: googlemap.txt. (Yep, it’s hacked together. Remember to input your own Google API Key in the Head section.)

3.I set margin:0px to remove the whitespace around the map, trying to make it look less like a web browser control and more like a google control.

Requirements
To use the spreadsheet you’ll need Excel 2003. That’s what I’ve tested it on, for Excel 2002 I believe the “import vba code” is slightly different and minor tweaking will be needed.

To create your own solution you’ll need a Google Developer Key, a Website to host the page, and Excel 2003.

Click this link to download the Google Map in Excel Spreadsheet

Update:
A Canadian Google Maps In Excel that works with pre-2003 Excel versions.

Random:
-You can do much more with google maps than just plotting points, I particularly like this example: gMap Workout Tracker

-Microsoft unveiled their new mapping service this week: Virtual Earth (Opens in new window since they have the back button disabled). The satellite imagery in my neighborhood is much nicer than google maps, and the interface has some additional cool tricks. I haven’t tried the virtual earth api yet.

A couple quick links:

Interview with Brian Jones on Office 12 and XML - Office Zealot posted another podcast the other day, this one on the new XML formats in Office - “Brian gives us the inside track on all the exciting new XML capabilities of Office 12.”

Enter the customer template contest on Office Online - The Office Templates site is having a contest for the best templates. The prize is your name on the site next to the template.

Just a quick note that I’ve started another blog at McpBlog.com where I’ll be blogging my notes as I study to take the Microsoft Certification Exam 70306.

I’ll be taking the test on Vb.Net, and my initial “getting the feet wet” with Vb.Net has been extremely comfortable coming from a VBA background. (Honestly, basic form functionality is the same as using VBA, except the IDE is twice as nice)

If the slim chance has it you’re also studying for the exam, stop by and say hi.

In case you missed the trackback on my last post Google for Spreadsheets, ResearchBuzz posted some excellent additional tips on Googling for Spreadsheets, Check it out:

ResearchBuzz: Finding Spreadsheets With Google

BTW, ResearchBuzz is written by Tara Calishain, the lead author of Google Hacks. Thanks.

While reading the article googling up passwords I noticed one of the examples given was a google search for budget spreadsheets.

Here’s the syntax used to search google for Excel Spreadsheets:

search query filetype:xls

Simply append filetype:xls to the end of any google query and you’re returned .xls files.

Note: I’d personally inspect any code before “enabling macros”.

A few example queries for spreadsheets:
Budget
Gantt Chart
Solver

TrimSpreadsheet is a “lightweight open-source JavaScript spreadsheet engine”.

You can read the details and find the download links here.

I’ve been experimenting with the Google Maps API lately and find myself frequently visiting Geocoder.us to geocode addresses to latitude and longitude.

GeoCode: “A geocode is a geographical code to identify a point or area at the surface of the earth.”

In the process I developed a spreadsheet that would look up the latitude and longitude of a given address via geocode.us and return the result to Excel.

Here’s a stripped down version which will return the geocoded results for one address: Download the Geocode Spreadsheet.

Related: Google Maps in Excel

NumSum is a new community for creating online spreadsheets, tagging and sharing them.

I created a test spreadsheet you can view here.

It was rather simple to add a link, some calculating cells, and an image.

The interface mimics Excel pretty well. I did get a “There was a global error processing your request.” error every time I saved a spreadsheet, however the spreadsheet was saved anyway (no problem, just a quick scare

NumSum was created by Metaha, who has a blog I just added to my feedreader, they discuss NumSum in this post.

Via LifeHacker. Here’s a screen grab: