Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Mr Skrunts]

I create a heck of a lot of spread sheets for various reasons etc, but these are fairly simple ones.


Decided to get adventerous but not sure if this can be done yet.  My better half does a lot of moble work and they have altered the way they do the paper work (hence more time wasted) One of the jobs is to manually write the post code of the call and add the mileage.  Could this be done on the spread sheet to automatically calculate the mileage with help from google earth/maps etc as every thing else seems to link to it.


Also converting a 5 couloum spread sheet into a data base for working out total time spent to individuals, copy from orange site, to note pad then to spread sheet not probs, but then need to convert that to access, and be able to update sread sheet daily/weekly.

TIA. 

[JesterRT]

http://www28.zippyshare.com/v/88280802/file.html

You'll need to enable Macros and be running MS Office 2007 (or greater), and maybe ie9.

Essentially though, look at the way Google maps allows you to do a search between two postcodes, reduce the url down to something sensible, do that in VBA, parse the object for a particular CSS class, rip the number, pop it into the excel sheet.

I wrote that as a function so you can just type the postcodes into the spreadsheet and it goes away and does the legwork.  Good for 100 rows (but you can simple paste the formulas further if needs be).

Hope that helps.

*bet it doesn't work, as with most VBA stuff - it seems to be 'ok on my machine '

[JesterRT]

Not certain what you're after on the Excel/Access copy/paste/notepad front...

Do you start with an access database that needs updating, and just want to do that in a spreadsheet?


I'm bored, wife & kids are out, and I've finished washing/polishing the car 

[Auto Addict]

Anythings feasible, hyperlink to bring up google, not sure how you could get the post code into the right area of the web page, and then pick up the mileage.

A lot of programming, and slow responses, I would have thought.

[tunnie]

Your better at looking at this the other way around, I assume she has to enter a number of postcosts at a time (wasting time bit?)

Its going to be fun linking a Spreadsheet to something that calculates distance, you would have to use VBA code, looks like its do-able:

http://processtrends.com/pg_google_earth.htm

I would maybe think about a web based solution, either PHP or HTML5 where by you enter (or import via .xls) a list of postcode, it then processes them on bulk giving distances. Then export again to a spreadsheet.

As your can be sure Google will alter their API's all the time to make maintaining it tiresome!

[cem_devecioglu]

http://forums.asp.net/t/1332783.aspx/1

must add microsoft web service is not free..

[Jester]

The file link I posted earlier has the VBA script working to just type in the postcodes - it then does the webscrape to grab stuff from googlemaps.  But as tunnie points out - if google change their markup then it'll probably fail (but it's very easy to correct it, and google aren't likely to be changing it very often).

p.s. - I've screwed up my 'other' account (I updated it recently and adjusted my vehicles and was going to update the email address too, but I've obviously screwed up as I can't log into that account).  Any admins fancy helping?  Who's best to PM?