simon_a
Master Guide
Reged: 08/21/05
Posts: 1773
Loc: London, UK
|
|
Hello,
Attached is a simple MS Excel spreadsheet and macro. This tool will turn a list of names, co-ordinates, and descriptions into a KML file.
This should overcome some of the limitations of the default spreadsheet import - no limitation on number of placemarks (within reason), for example - but is still a very basic tool (you will need to enhance the VBA code to create more complex KML files).
There is an 'Instructions' tab but, basically, you simply populate the 'Data' worksheet and then run the 'generateKML' macro (hit [ALT] + F8 then 'Run').
I've included a few lines of sample data to play with...
[Edit: updated to change the default maximum number of placemarks from 5000 to 50000 (editable)]
See also Open GE from MS Excel, which allows you to click on a link in a spreadsheet to open that row's co-ordinate directly in GE:
http://bbs.keyhole.com/ubb/showthreaded.php/Cat/0/Number/774351
Disclaimer: This is provided 'as is' with no support or guarantees etc. Care should be taken opening any file including a macro - I've no reason to suspect that it contains a virus (I should hope not - that means my PC is infected!) but I still suggest scanning with an anti-virus tool just in case.
Simon.
Edited by simon_a (01/28/07 02:52 PM)
|
tekgergedan
Master Guide
Reged: 09/25/05
Posts: 8041
Loc: Turkey GMT+2
|
|
What about inserting a button? 
I have another version of this. My mind differs from yours in managing scripts, thus I'd better not touch it. Is it a good idea to scan the rows until an empty row is found and include all the records upto it?
--------------------
...::: | | | Install 4.3 and be aware you can switch back | | | :::...
...::: | | | what you are is what you eat and what you think | | | :::...
...::: | | | if you want to get something, first look and ask for its very details | | | :::...
|
simon_a
Master Guide
Reged: 08/21/05
Posts: 1773
Loc: London, UK
|
|
Hello,
Hey, I'm no programmer! In fact I find code efficiency and squeezing all your functionality into two-and-a-half lines of code quite boring. I stop when it does what I want - and that includes insertng a button
This was just a quick modification of something else I bodged together because people were finding the csv import restricting - specifically, this post:
http://bbs.keyhole.com/ubb/showthreaded.php/Cat/0/Number/592258
If what you import is simple, it should work just fine but if anyone wants to add more features/checking/functionality etc, be my guest!
I'm sure it's just becasue you're a perfectionist 
Simon.
|
Valery35
Master Craftsman
Reged: 09/02/05
Posts: 2270
Loc: Russia, Perm, Sun
|
|
See interest link http://www.zonums.com/excel2GoogleEarth.html
Also very good site for many GE KML developers.
Thanks to Stefan http://www.ogleearth.com/2006/09/short_news_flic.html#comments
|
simon_a
Master Guide
Reged: 08/21/05
Posts: 1773
Loc: London, UK
|
|
Hello,
Attached is a simple MS Excel spreadsheet and macro. This tool will turn a list of names, addresses, and descriptions into a KML file.
This is more or less the same tool as attached to the main post of this thread, except it allows you to import placemarks from address data rather than latitude/longitude co-ordinates. For example, placing a UK postcode into this field, such as 'SW1H 0BD' (without the quotes of course) will locate the centroid of that post code. Note that, when you open your KML file, it may take a while for GE to contact the servers and locate every placemark.
See the KML documentation for the <address> tag to find out what else to put into the Address field:
http://earth.google.com/kml/kml_tags_21.html#address
This tool should overcome some of the limitations of the default spreadsheet import - no limitation on number of placemarks (within reason), for example - but is still a very basic tool (you will need to enhance the VBA code to create more complex KML files).
There is an 'Instructions' tab but, basically, you simply populate the 'Data' worksheet and then run the 'generateKML' macro (hit [ALT] + F8 then 'Run').
Disclaimer: This is provided 'as is' with no support or guarantees etc. Care should be taken opening any file including a macro - I've no reason to suspect that it contains a virus (I should hope not - that means my PC is infected!) but I still suggest scanning with an anti-virus tool just in case.
Simon.
|
RemKou
Tourist
Reged: 05/22/06
Posts: 10
|
|
Wow, this looks quite the thing i have been looking for. However, if I run your macro (the one in the opening post) the resulting KML makes GE move to a location somewhere in the pacific ocean??.... Whats wrong?
Edited by RemKou (10/25/06 03:02 AM)
|
simon_a
Master Guide
Reged: 08/21/05
Posts: 1773
Loc: London, UK
|
|
Hello,
What it outputs depends on what you've entered. Have you got you latitude and longitude the right way round?
Rather frustratingly, GE decides to change the order it which it wants latitude and longitude depending on what you're trying to do.
The other possibility is that it's getting confused by what's entered and defaulting to co-ordinates of '0,0', which is in the ocean just off the coast of Africa.
Also, the position that GE moves to is not necessarily anything to do with the co-ordinates of placemarks themselves. If you're using <LookAt> tags in your KML (the converter doesn't put these in by the way), it will fly to whatever these tell it to.
If you're still having problems, post the latitudes and longitudes and I'll have a look...
Cheers,
Simon.
|
RemKou
Tourist
Reged: 05/22/06
Posts: 10
|
|
Simon, thanks for your quick reply. I managed to solve the problem myself. The solution had to do with the national settings of windows. My windows is set to Dutch, therefore it automatically sets the decimal to , (comma) instead of . (decimal point). Next it turned out that the macro also automatically used , (comma) as the decimal. I then switched the country settings (in the configuration of windows) to US, and it works smoothly now.
Next thing ide like to figure out is how to add a choice of icon in your macro. Any suggestions?
|
simon_a
Master Guide
Reged: 08/21/05
Posts: 1773
Loc: London, UK
|
|
Hello,
Ah, OK - I would never have thought about it using commas instead of decimal points.
I guess that this is excel 'helpfully' formatting the columns for you. As they are just text strings, you might be able to just change the format of the column - I can't test that easily as mine is set up in a British way. If you find out, let me know and I'll update the post.
Quote:
Next thing ide like to figure out is how to add a choice of icon in your macro. Any suggestions?
Any good at VBA scripting?
You've two options:
Open the KML file in GE as it is, right-click on the folder and select Properties > Style and change the Icon for the whole folder
Edit the macro and include the KML code for Icons in there
I probably won't do that because I don't want this thing to become too complicated but it shouldn't be too difficult to work out - you'll need to add some code at the top for a <Style> to reference and a <styleUrl> in the code for each placemark. Have a look at the KML documention for more information:
http://earth.google.com/kml/
You could also set a style for a whole folder (described above) and then save that folder as KML and have a look at the code in a text editor - that willl show you the structure you need.
Simon.
|
BusterDAN
Tourist
Reged: 12/08/06
Posts: 6
|
|
Excellent Simon....many thanks!
|
Previous
Index
Next
Threaded
View in Google Earth
View in Google Maps
Edit
Reply
Quote
