xls2kml (Scripted spreadsheet for creating kml files)

LINK TO FILE

• Creation of grids
• Creation of statistical bars (1 to infinite edges)
• Paths and polygons with their styles
• Nesting folders
• Flexible application of the time stamps
• Ability to tune placemarks, paths, polygons, and other features if there is a shift in the image in GE
• Ground overlays and screen overlay
• DMS support for lats and longs
• Address entry instead of lats and longs
• Use of formulations on the spreadsheet

Visit here for former discussions.
Visit here for older discussions (check the pages - it is 7 pages long).
Visit here for discussions on the previous forum.

[Hello everybody, I am new here and this is my first post   ]

Dear Tek, first of all MANY THANKS for sharing this wonderful tool.
I am a zoologist and I use it since some time to map records of my subjects of study.

I have a question: I would like to produce kml files that, once opened, will produce a fixed, user-defined view of the map, I mean in terms of position and zoom level.
Or, anyway, I would like to get images from my different kml files that have always the very same background map, regardless of the placemark position.
Exactly as if I was putting dots with a pencil on photocopies of the same blank map (sorry for "stupid"  example, but... to be sure my request is understood...)

Can you give me some help?


Thanks, Marco

Hi Marco,
I have added one more setting for your need under the "Document LookAt" heading. I hope I have got your request correct.
Download page: sites.google.com/site/tekgergedan/home/xls2kml

Dear Tek, it's exactly what I needed... thanks a lot !!!
Actually, I get a bug as soon as I activate the content of the sheet (see picture below), however I managed to copy the whole code in my old file and was able to solve it and get the new function working.

GREAT!!

Thanks a lot, Marco

Yes, that's an attribute for 64-bit users. 32-bit users should delete the "ptrsafe" attribute.
Thanks

Hello Tek - I am having trouble with the time slider built in to your sheet. Is there a user guide that I can follow to see what I am doing wrong?

Hi,

In this page, it is.
I have checked it, found a problem in time calculations and another in styles. Does the attached fixed file work correct? If yes, I'll upload it for the public.

Thanks for the heads up

attachment removed

Hy everybody. I'm new so I apologize if I do something in wrong way. I need help to remove or not to display a frame around icons.
I have used the fantastic file "xls2kml.xls" to generate a *kml file. I imagine everybody here know. The problem is that when I create the file writting the adress in the file "xls2kml.xls", the icon appears with a frame around it.

I send a picture to undestard it.

Thank you.

example

Oct 6, 2016 7:25:53 GMT yrsp said:

Hy everybody. I'm new so I apologize if I do something in wrong way. I need help to remove or not to display a frame around icons.
I have used the fantastic file "xls2kml.xls" to generate a *kml file. I imagine everybody here know. The problem is that when I create the file writting the adress in the file "xls2kml.xls", the icon appears with a frame around it.

I send a picture to undestard it.

Thank you.

example

Hi



as you should know that this is community is about sharing kmz files from Google Earth.



Its not a community to get support for issues with Google Earth, The top of the page shows a link to a



support group that deals with Google Earth.



Google Maps and Earth Help Forum

I apologize but I had thought that this subject was related to concerns with the "xls2kml.xls how the title shows.

Hi yrsp,



I've never used xls2kml software, but I'm very familiar with the square surrounding a placemark created by a GE search and saved as a KML file.



I usually just create a new placemark over the one created by the search, but you seem to have created a great many. Another way of removing the box is to copy the KML file from GE and put it in a text editor. In the editor, delete these lines of code:




<LinearRing>

<coordinates>

-73.9771080114477,40.75994289141422,0 -73.9771080114477,40.76491390858578,0 -73.9704799885523,40.76491390858578,0 -73.9704799885523,40.75994289141422,0 -73.9771080114477,40.75994289141422,0

</coordinates>

</LinearRing>


Copy the remaining code and paste it back into my places.



I suspect this would work with your file as well. You'd need to do a Search and Replace for <LinearRing>*</LinearRing> and replace it with nothing. I'm not sure how to make this work, but if you want to post your file, I'll see if removing those lines of code will solve your problem.



If that doesn't work, tek, the creator of xls2kml and the very busy administrator and technical master for this forum will find time to log on and help you to a solution.



I've removed the <LinearRing> sequence from a copy of a file created by Search and saved. It's the second file in the attached folder.

Attachments:

Trump Tower New York NY United States.kmz (2.54 KB)

Hi yrsp
I guess you create placemarks with their Address locations and not their lat-lon coordinate values. Am I correct?

First of all, thank you for the fantastic application xls2kml.
You´re right Tek, the problem is when I create placemarks with address.

In the second place, thanks Washi. Your solution works. I edited the code after opening it with Google Earth, but I only replaced the word "LinearRing" by "Linear". In this way "LinearRing" doesn`t work and I can keep the coordenates for the future. You don´t need to delete all coordinates.

It`s a pitty that when somebody create a placemark with address appears a square surrounding this placemark. Washi solution is good but you need to manipulate the code *kml after saving in GE.

Thanks a lot.

Hello

Fantastic tool - thank you for developing.

Is there a way to create a KML with just line strings and no points?

Regards, Phil

Hello

is it possible to transfer this vba macro from excel to access?

May 18, 2016 14:59:29 GMT tek said:

Hi,

In this page, it is.
I have checked it, found a problem in time calculations and another in styles. Does the attached fixed file work correct? If yes, I'll upload it for the public.

Thanks for the heads up

Hi! Found this xls2kml and would like to thank the creator. Many thanks. Is the version 1.16.07 the latest one available?

Jan 5, 2021 23:21:08 GMT hmcosta said:

Many thanks. Is the version 1.16.07 the latest one available?

Not yet. Time to time I rewrite the codes partially that prevent advancing the script. But it is still far from being usable.
Thank you.

Dear Tek, again Marco - see above on this thread.
After 5 years still I'm still using with pleasure your tool for zoological studies and maps :-)

I would like to report an issue that occurred after I updated my Google Earth version to the last one.
As I am based in Italy, my system uses comma as radix point, and kml files produced by your spreadsheet do use comma as radix point in specification of icon size.
It turned out that previous versions of GE were able to handle comma as radix point in icon size (up to v7.1.8.3036), while newer versions (7.3.1) are unable to do this, and nothing will appear on the map (icons size treated as zero).

So, icons from my old kml are not visible in new GE.
Even if I set Excel to use dot as radix point, the resulting kml will have comma.

I raised the issue in the GE community (https://support.google.com/earth/thread/93422533?hl=en#), for what concern the changed capability of GE to handle the radix point but problem was not solved (and to a certain degree, I am not even sure I was believed...).

Can you lend any help, or suggestion?

thanks in advance, Marco

Hi,
Nice to know it makes a stable base for you for so many years.

I too had that problem in my KML works. In some cases it work, in some it does not. When the cell format is set as numeric, or if the whole the cell format is text and the value contains something else where the number is to be extracted by the script, it works. In other cases, having the application using the comma is a trouble.
But I could not figure out why it depended on your GE version. Possibly, your try-outs coincided with something else.

I was to finish a new version but it looks like I need some more time. Meanwhile, I edited the script of the current one and forced it to approve commas. Can you try it?

This can increase the process time for large data. But no matter, smooth work is more important.
Thanks

Hi Tek, thanks for your assistance.
About relationship with GE version, this is what happened.

• upgrade to last version of GE (no other change to my system, at least with my consent/awareness)
• the problem emerged
• downgrade of GE versions until I found one where the problem disappeared, that is the last still available for download (v7.1.8.3036). I suppose this was my version before the upgrade.

Try on your own, if you like, although it's annoying testing different versions as it is not possible to install two at the same time (maybe changing the default folder?... I should try).
I attach such as file and an image of how it should look like.

Sure I would love to test your updated version.
If the code that address the comma/dot problem is just a single, entire block (and so, easy t handle) I'd like to add/edit that part only, since along the years I made some other "custom" changes to my spreadsheet.

Thanks




Map Pygopleurus 12.09.2017.kml (2.39 MB)

I will check with the old GE version as soon as the Google Earth web page gets live again.
I was worried about something else but, as expected, the scale values are not valid.

If you have not modified the script codes of your workbook, there is no risk to switch to version-1 updates. There are three ways to get updated, explained in the page (https://sites.google.com/site/tekgergedan/home/xls2kml). For big sheet data, either the sheet or sheets can be moved from the old version to the new version or the old script can be replaced with the new one.

If you have modified the script, let me know them and I will check if they may influence the outcome or not.

Thanks.
Unfortunately, the page you linked seems not working (I get error 404).

Sorry, my mistake. Fixed now.
Please, keep a copy of your current workbook safe to eliminate worries.

Hi Tek, downloaded!

I had to fix the 32/64 bit code issue like as follows (solution copied from stackoverflow.com/questions/5506912/how-should-i-make-my-vba-code-compatible-with-64-bit-windows).
 
Option Explicit

#If VBA7 Then

   Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _

   ByVal hWnd As Long, _

   ByVal Operation As String, _

   ByVal Filename As String, _

   Optional ByVal Parameters As String, _

   Optional ByVal Directory As String, _

   Optional ByVal WindowStyle As Long = vbMinimizedFocus _

    ) As Long

#Else

   Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _

 ByVal hWnd As Long, _

 ByVal Operation As String, _

 ByVal Filename As String, _

 Optional ByVal Parameters As String, _

 Optional ByVal Directory As String, _

 Optional ByVal WindowStyle As Long = vbMinimizedFocus _

 ) As Long

#End If
Then, I copied my simplified spreadsheet on the new version
It worked almost perfectly, the only different feature I spotted is in the behaviour of labelsize field: I used value -1, to produce a invisible label, that was however showing while hovering the cursor above the icon. Now, -1 value gives fully visible icon labels.

However, as for the problem that I raised, I have to say that the kml obtained has still comma in the value of the scale, so I expect not to work in new versions of GE.
I mean, I did not actually tried to open it on latest GE (I am working with the old version, which I should uninstall...).
But the question is: was kml expected to get out with dot instead of comma (as I understood it), or is it expected to work in the latest GE even with comma?

> But the question is: was kml expected to get out with dot instead of comma (as I understood it), or is it expected to work in the latest GE even with comma?

We cannot expect GE will accept comma as a decimal separator. In a world where still foot and inch size of someone is used for measurement, it would be a too-much optimism.   

----------------------------------------


Whatever, I do not recommend using an old GE. Please, give a try to fix the problem.

For your convenience, I have replaced the download file (I am adding the modifications below too since you may want to do it yourself). I was to leaving as it was because my work of v2 is on its half way. But the comma problem is serious.
I added the 32/64 bit issue solution to the new version, as well. I did not add it because some people use 32 bit Office version on 64 bit OS, or on Mac. I am not able to check all possibilities.
(BTW, I must thank you. Your test gave me a good information.)

----------------------------------------

Fixing the comma problem:

In the script, find the line
Function fSetTheScaleValue(vP1 As String, vP2 As Boolean) As Double
and replace the word "Double" with "String".

Then, some 20 lines below, find the line
End Function
and add before it the following
fSetTheScaleValue = Replace(fSetTheScaleValue, ",", ".")

----------------------------------------

About the minus value stuff: In the old version, actually zero values are expected (no minus value) and minus values are replaced by zero. Can you change -1 values to zero? In the new version minus values enable make use of the StyleMap feature. Nevermind... I too like invisible labels that popup only when the mouse is over the icons. If you cannot change -1 values to zero, just go to the same lines of the code above and change this:
If vP1 = "" Then
to this
If Left(vP1 & "x", 1) = "-" Then

If vP2 Then fSetTheScaleValue = 0 Else fSetTheScaleValue = 1

ElseIf vP1 = "" Then


You may want to set another value here : "fSetTheScaleValue = 1"

Sooo great :-)
Now everything is working as desired, and I am glad that I was able to give a little contribution helping with my test.

> We cannot expect GE will accept comma as a decimal separator. In a world where still foot and inch size of someone is used for measurement, it would be a too-much optimism.

  true, however, my old files had comma as decimal separator and old GE was able to understand it!

By the way, if I can "suggest" some more features, which I tried (partly succesfully) to update in my personal version:

• I moved in a separate sheet "general settings" the settings that are now specified in the yellow cells in rows 1-2 (I just find it more "ordered", but this is personal taste)
• I added in a separate sheet a color palette, that I find very useful to choose/copy color codes (see attachment)
• I tried to add automatic saving of the kmz to the same folder where is the spreadsheet.
• I tried to append automatically the time (year.month.day.hour.minute) to the name of the file

The last feature is useful to me as, in each session of work, I may produce more several versions of the same kml (due to continuous check-update of the content), but I prefer to keep the old ones rather overwriting.
Maybe others can find it useful, perhaps as option to choose.

Thanks once more! Marco


[filelink type="xls" href="https://drive.google.com/file/d/1SbWihGBe5CJAeJmQ7bP-DL_u-W_LQZL1/view?usp=sharing"]Hoplia material.2021.02.23[/filelink]