VBA to Open Google Earth and place Marks

Dear,
I have an excel sheet contain data in many columns , in column “H” I have Names , and in column “M” I have Lat , and in column “N” I have Long , and in column “O” I have the Lat & Long together .and I have a command button.and the Header is in Row 4 , and the data begin from Row 5 to more than 1000 Rows
I need when select any Lat Long from column “O” and press to the command button , I want VBA to open google earth and place marks for all selected Lat Long , maybe I will select just one Lat Long like “ O6” and maybe I will select more than one Lat Long from column “O” like “O7:O15”.
Please see attached file as example.
if anyone can help ,please reply the spreadsheet with the Code Macro because I’m new in VBA .

Best Regards

This is an old utility that I wrote that has all the abilities that you're looking for. I did make mods but I didn't adapt it to your workbook format, but perhaps someone else will help you with that. Shameless punt: Google Earth is fun!

It works by creating a kml file (so you'll need to enter a path and filename on your drive), and then launching GoogleEarth via the kml file. Like double-clicking on the file from explorer.

I can't properly test it now as my Google Earth is refusing to run at the moment. I'll try later when I reboot. But hopefully it works for you.

export_kml_cy.xlsm

Dear cyiangou ,
Thanks alot for your help , I creat a command button to copy the selection cells from my sheet and paste them in your sheet , then I generate your KML code . so It works . but I still need somthing. If the names in arabic letters the names does not appear on google earth , just if the names in english letters , can I make google show me the names in arabic language beside placemarks ?

Thanks alot in advance

It shouldn't matter what Google Earth knows, if you post arabic text, it should show if your Windows settings are correct.

But an extra precaution, in the function CreatePlacemark, change the following lines

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Just in case my text 'cleaning' process interferes with arabic text.

Dear cyiangou ,
i replaced the two lines above by copy / paste , but the Arabic language of the cities in google still not appear , the Arabic names beside placemarks appear as "?????" . and there is no problem in English language of the cities.
by the way, what should i do ( or what line of macro should replace ) if i want to change the color of the place marks or the Shape of place marks " i used this order ?

Best Regards

What I suggest is that you create a placemark manually, set all language and colours as you want them, then save it as a kml (not kmz) file, and then examine it in Notepad to see how the attributes you need are set. Then look at the kml that my macro creates, compare them looking at differences, and manually add in the extra attributes in the appropriate places in the generated kml file. Test and modify until it works.

Once you've established what needs to be changed, go into my code where I build the kml string, and make changes so that the generated kml includes your added features.

Tip: If the kml is not well-formatted so that it's hard to read in Notepad, try renaming it to .xml and open it in a browser.

dear cyiangou,
i tried many time to solve the problem but i failed
the only way i found is to open KML file with Notepad and type the name of city in Arabic letters in the code then save the notepad as "Unicode big endian", then double click on KML file again to open google earth with Arabic letters of cities , and this is hard work every time when generate KML file .

is there any idea ?

Please enter some arabic names and locations into the file that I uploaded, and post it here. I'd like to see what your Excel starting point looks like.

see attached file

dear Cyiangou,
thanks for reply and helping me, i attached your file and added the data and command button in sheet1 , when i choose some cells in the column "H" ie the names of city command button will copy the cities names from column "H" and Lat Long from columns "M" and "N" and paste them as it "in any languages" in your sheet in column "B10,C10 and D10" , then i press your command button "Create KML and Launch" so the google will open , the English letters works well , but the problem in Arabic Letters.
see attached file

I had a closer look, and I don't think that it has anything to do with GE. It seems to be entirely to do with vba. I'm not sure I can help you with this.

However, my last suggestion is to try to run this macro on an Excel version higher than 2007. I have a suspicion this problem is solved in later releases.

Very elegant programming. However I am wondering why it ignores name for the placemarks when those names are numerical.