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.

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?

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.

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

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.

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?

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.

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?

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.

Excellent Simon....many thanks!