Excel data to kml (full support of international languages)

[tekgergedan]

http://www.emaltd.net/google/gec/utilities/index.asp

[tekgergedan]

Thanks to Simon with his ideas and initial questionings.

And another appreciation for the GEC moderators who have tried the
script so that it has come to the public after try-outs in different
computers.*

The sheet contains a script. Please do not doubt and and activate it.
You will see a sample data (by Simon and Phil).
Cells that do not have any functionality have been locked. Feel free
to remove the protection; it has no password.
Row 3 is the row that specifes which columns are going into the kml.
If you dont want that tag, keep its Row no.3 empty. The red columns
are necessary for a healthy kml (so it wont make any sense whether you
have it empty or not there).
Row 4 corresponds to the kml tags (Folder, Altitude, Range, Tilt,
Heading, Icon, and Description). You can remove any of them (except
the red ones) and insert any number of new ones which will go into the
description tag. Try it, you will find it easy.

It is strongly recommended to save the kml as a kmz file from GE. This
step will optimize the style tags and decrease the file size.

Any feedback here will be appreciated. Please be careful at first
sight because the script is new and needs more critics.
Enjoy.

---
*: Only one problem may trouble you. It is the "Run-time error '429'"
the solution of which seems beyond my experience. If you have any idea
about it, please thankfully help.

[Brendan]

I tried this script the other day - v.1. I entered a set of
coordinates for my office and when loaded in GE, it threw me across
the globe. Looks like Lat and Lon are reveresed?! As I see no other
comments, I am questioning my sanity at this point. I checked the
website and saw no updated script. Please confirm.

Other than that, a very useful tool.

thanks,

Brendan

[tekgergedan]

THE SITE WILL BE OFF THIS WEEK

[Autumn]

Hi Tek,

The macro is really powerful!

by the way, i realised also that the Lat and Lon are reversed, as
mentioned by Brendan. Will there be any updated version available? if
not, could you pls let me know how i can change the macro in order to
solve this? I tried changing the title at Row 4, but apparently it
didn't work.

Thanks.

Autumn

On Jun 8, 11:56 pm, tekgergedan wrote:
> Thanks for your notice. Was a simple mistake. :)
>
> tek

[tekgergedan]

Hi Autumn,

Upon request, I have uploaded a copy of the whole stuff to another
"temporary" folder which can be reached here:

http://www.samanevi.com/utilities/index.asp

The lat and lon order was corrected.
I strongly recommend people using it to check the versions there since
I can randomly update or evolve it when I have time and enough fun to
create new features.

(Again, that is a temporary link and will be removed today or
yesterday to go back to the original site)

The macro is a "pure" vb code and any row can be a preparation of
something in the following rows. Therefore, changing it should need a
complete understanding of the whole things done there.

Thanks a lot
tek

[Autumn]

Hi Tek,

Thanks for the prompt response. However i can only find v1.01 there
and in this version the Lon and Lat is not yet corrected. Pls advise.

Thanks.

Chow Foon

[tekgergedan]

Hi again and thanks again.

Now, it should be working in the expected way.

[Autumn]

Yes i tried once and it worked perfectly fine! Great! Thanks a lot!

[tifferooni]

Hello,

I checked out your xls->kml conversion tool and I was really
impressed... do you know of a way to generate boxes/grids (in GE)
using a similar technique? I have an excel file that contains
bounding coordinates for grids... it is in the format 'XMIN XMAX
YMIN YMAX' as the column headings. Do you know of a way to do what
you did for individual ordered coordinate pairs but transform them
into grid cells?

Thanks in advance for any help you might provide--
-Tiffany

[tekgergedan]

You catched me at the time leaving for home after 3 days of working
full time. So, sorry to be late to reply.

Do you mean 3-D boxes? Or regions (http://code.google.com/apis/kml/
documentation/kml_tags_21.html#region)? What kind of grids? I am not
able to figure it out. Need some examples. Maybe you can create a
sample placemark and post in Open Forum of the bbs to let me see.

But whatever you mean, you need to know scripting. I can do that if I
know what exactly you mean. Hopefully, it will not make the present
script complex.

Waiting...

[tifferooni]

Hi again,

I suppose what I meant was a ground overlay made up of grid-like lines
(similar to latitude-longitude lines) but with specific locations...
essentially these lines would be superimposed over an area in GE so
that I could see the regions inside each grid-box. (More
specifically, the grid-lines correspond to satellite pixel data that I
have). So there are a few ways I've thought of doing this: (1) I
created a grid layer in a mapping software (ESRI ArcGIS) and turned it
into a kml layer in that software, but the grid it creates has a gray
background, and I wanted it to be transparent) (2) use some sort of
tool like the one you put together where the bounding coordinates for
each gridcell are transformed from an excel file to either points or
(even better) lines that can be displayed in GE. (In excel, my grid
coordinate boundaries look like this:
Id XMIN XMAX
YMIN YMAX
0 -121.25071375800 -121.24238075800 47.90017886160
47.90851186160
1 -121.25071375800 -121.24238075800 47.90851186160
47.91684486160
2 -121.25071375800 -121.24238075800 47.91684486160
47.92517786160

I can post an example of the kml that I created on the bbs Open Forun,
except I don't know how to do that! And unfortunately, I also don't
know scripting. But I'll try an figure out how to post my kml file...
I will call it Cascade_mountain_grid. Thanks for your attempt to
help!!! -Tiff

[tifferooni]

Dear Tekergedan:

I have posted my sample kml grid file in the 'files' portion of this
site:

http://groups.google.com/group/kml-support/files?&sort=date (it's
called Cascade_mountain_grid)

Also, see my explantation in the following message. Thanks!

[tekgergedan]

I think it is possible to do. But I need some time. I am very busy
nowadays.

Thanks for you interest. :)

[tifferooni]

Hello,

Don't put too much effort into this; I do have an alternate way of
importing grids into GE using ESRI software; I just thought it would
be neat to try it another way... Cheers,
-Tiff

[tekgergedan]

Now, enjoy the timespan, path, polygon, image overlay, screen overlay
and the grid features.

The grid feature does not necessarily need straight horizontal or
straight vertical edges.

tifferooni, I hope it is what you need. Though you can r-click any GE
folder and change its placemarks' styles, using this file might be
cheaper.

[tascha]

Hello,

I was trying to use the marco but was not able to upload anything to
KML? Are there certain steps that I have to follow inorder for the
marco to work?

> > tek- Hide quoted text -
>
> - Show quoted text -

[tekgergedan]

Hi,

Just click the button named "Create kml". And drag-and-drop the file
on your harddisk to GE. :)

[tascha]

Hello,

I tried to play with your excel to kml file but was not able to upload
anything to KML? I am sureI am not doing it right are there certain
steps that I have to do in order for me to upload the file to KML?

[tascha]

Silly me. This is an awesome application. Great Job Tekgergedan.

[tekgergedan]

Thank you. There was a mistake I corrected after your message. It is
not your fault. Thanks for the feedback.
There can be rare weirdnesses. For instance, I had not time to check
the grid output when it crosses zero and max. lats and lons. But
theoretically, should work.

[leszekp]

Hi,

I've tried the GE Path to GPS Plots utility on your site, and run into
a quirk. When I paste a path into that box, convert it, and then put
it into Google Earth, it takes the Start point with a negative
longitude (e.g. 35,-110), removes the negative sign and plots it as
35,110, all the way across the world. Thanks.

Leszek Pawlowicz

[tekgergedan]

Hi leszekp,

I see what you mean. Please give me some time. I will reply in its
thread ( http://groups.google.com/group/kml-support-third-party-applications/browse_thread/thread/51d4b968d335d1e5/#
), not here.

Thanks for heads up :)

[tascha]

Hello,

what the GOverlay and GO.Bound, GO.Noth, GO.South, Go, East, GO.West
and GO.Rotation do in this marco? Is it necessary?

[tekgergedan]

No, nothing is necessary except for the red columns. Just keep the
cells (which are marked with "x"s in the sample dataset) empty if you
don't want it.

GOverly stands for the Ground Overlay feature of kml, which means an
image overlay over GE.
See: http://code.google.com/apis/kml/documentation/

When you use the colored columns (one of which is for Ground Overlay),
they are placed beneath the placemark defined in the same row.

Only, the Grid is not a kml tag. It creates a grid made of polygons.

[tekgergedan]

I was preparing some illustrative explanations. That will help a lot
to understand what it really does. But I need some time. :)

[tascha]

Hello tekgergedan,

I figured my last question out but I have one more.
In this application (macro) I am not sure if I understand the logic
of
the (Grid.downleft Grid.downright Grid.upright Grid.upleft
G.horizontalblend G.verticalblend G.LineWidth
G.LineColor G.FillColor
10,3,0 41,7,0 40,30,0 11,20,0 3 2 2.3
ffffffff 9cffaa00)
what does it mean 10,3,0? are they associated with lat and long?
Could
you please explain?

> > cheaper.- Hide quoted text -

[tekgergedan]

Hi leszekp,

It was the first I thought about. But I should find after which one is
deleted it goes on working. Of course, there is a limit. I will check
them again.

[tekgergedan]

Hi tascha,

They are the four corners of a rectangle. I should type, for instance,
"SW" instead of "downleft" but user preference to keep the earth's
angle might be different.

10,3,0 stands for Lon 10, Lat 3, Altitude 0.
Within the given box, it creates a grid. Entering 2 and 3 respective
blends creates 6 polygons within the defined rectangle - 3 in
horizontal direction and 2 in vertical direction. :)

[tascha]

Thank you.

[craigha...@hotmail.com]

HI tekgergedan,

Can you add a piece that will enable height dimension of the polygon?
so for example I wanted to see over time the sales of my local shops
the polygon (over time) would ascend and decend with the sales
pattern? I cold liknk sales volume to height value? any thoughts?

thanks

Craig

[tekgergedan]

Hi craighazledine,

Nice idea that should have been included. There it is now for use in
the "Po.Height" column.

tek :-)

[craigha...@hotmail.com]

Tek,

Thanks, Ive lots of new questions

1. Is there a way of mapping the poylgon to the placemark? by Lat/
Long and in doing so set the placemark height above the height of the
polygon?
2. The polygon is set to 3 sides, can it be more than four side to
creat any shape polygon
3. am i asking to many questions : -).

Ive seen GE Graph but this of course does not offer the flexbility
such as time variable and multi value variables? is this something you
were thinking of developing in your makro?

thanks

Craig H

[tekgergedan]

I like questions. Please, anyone feel free to ask anything you have.

I was preparing a text with explanations of its use as it is now. I
will try to finish it up this week.

1. A row will always contain the pushpin placemark. If you enter say a
polygon data, the pushpin will also appear above the polygon with its
own descriptions and other features in the MyPlaces folder. Though I
am attempting to include all the kml features, a height value is among
the ones that I did not think of for a short term. It is easy to
include it. I'll do.

2. I did not understand the question. There is no limit to the sides.
But the spreadsheet cannot create polygon coordinates. A polygon or
path should first be drawn in GE or with any other tool and, then, the
coorditanes should be copied from the NotePad.

3. No...

Yes, I am developing it. Currently, before going on too far, I am
thinking of doing something on the net which can be shared (including
other features in mind). But I have a limited time. Therefore, I have
to refrain any local test attempts before deciding on everything both
problematic and potential. It will be alright to share what it is
going to be when the time has come. I will need your views and critics
then. But for the time being, I am not clear how to achieve that in a
most effective way.

Your ideas are valuable. Thanks for them. :-)

[craigha...@hotmail.com]

Tek,

thanks for the quick response and the answers, im always happy to
share my views, test an critique. In my first question Im happy to
draw the polgon ,it was the auto placement of the polygon depending on
the placemark lat/ long I was trying to achieve, sorry if Im just
misunderstanding the point my self maybe i need I better version GE to
us the polgon placements..

regards

Craig H

[tekgergedan]

No problem, Craig. You don't have a reading yet to understand
everything or refer to anything. That's my fault.

BTW, there is a problem. To have people use the script who have
already done collections, I am aware of need to a reverse way; ie a
kml2xls script. That may hopefully ease the work to insert the polygon
and path coordinates as well.

Best
tek

[craigha...@hotmail.com]

tek,

Great help guide I see attached on the site. Do you have a source of
hexdecimal colours or a simple calc from RGb to hexidecimal that you
show, e.g in your guide: "The color of the polygon in "aabbggrr"
format expressed in hexadecimal notation. "aa" stands for alpha
(transparency). Empty values (or the empty row#3) is interpreted as
white. " If you do that would be great.

Also Ive been using the spreadsheet the last couple of weeks is there
a way to generate a polygon based on number of side that will reside
in the same place as the placemark? this will save time on generating
a polygon in GE and then copying out the coords...If not I have to
create a polygon for each data point manually (if I have 300 this
takes a long time) maybe something simple such as just basic shapes
from 1 to 50 side (50 side equating to a circle)?

Let me know what you think if there are any ways to enhance this..

regards

Craig H

[tekgergedan]

Hi,

There are a few pages of color lists/conversions the links of which I
did not saved. But, for me, the best to have is rsgrillo's tool here :
http://www.sgrillo.net/googleearth/index.htm

As to your second paragraph, I had done something where dynamically
one can enter values and the values are converted to statistical bars.
So, I understand what you have as a problem. A hard one. :| But I am
not sure what you technically mean. Do you mean it create the polygon
taking the placemark's coordinates as a base/center? Then (1) a value
of distance between one edge and the placemark and (2) a value of the
number of edges will be needed. Is that what you mean?

Best
tek

[craigha...@hotmail.com]

TEk, thanks, yes create the polygon taking the placemark's coordinates
as a base/center, then use a written valueto create of distance
between one edge of the polygon and the placemark to get the polygon
width and also be able to add a value that determine number of edges
that will be needed. I think you got it 100%

regards

Craig H

[tekgergedan]

Great. Then, it will be a second ability of the script after the grids
(I am trying to think about easy users too, thus refraining from the
basic-kml columns become complex). That feature would help me as well.
I think another value for numbers of polygons (for statistical
presentation) would also be useful.

Best
tek

[craigha...@hotmail.com]

Tek, ive lost the link to your download site, can you repost here so I
can get the latest version

thanks
Craig hazledine

[tekgergedan]

It is in the first post of this thread:
http://www.emaltd.net/google/gec/utilities/

BTW, when a polygon is used as a statistical bar, would it be useful
for you to colorize the one with the highest value with a specific
color? If it'd I will spend some more effort since this is something
important, I think. If not, I may do it some time later.

:)
tek

[craigha...@hotmail.com]

I was thinking of placing a colour dependent on the value of the
polygon to give a specific colour, so if value range 1 0-1000,range 2
1001-2000, etc etc it would be assigned a specific colour, I would put
a formula in the colour field of the polygon gradient polygons, and
via a simple lookup the colour field would autopopulate, maybe there
is better way to do this?

Craig H

[craigha...@hotmail.com]

Tek,

How is all going?

regards

Craig Hazledine

> > tek- Hide quoted text -

[craigha...@hotmail.com]

Tek,
Are you still around? havent heard from you in a long while, let me
know - thanks - Craigha...@hotmail.com

regards

CRaig H

> > - Show quoted text -- Hide quoted text -

[tekgergedan]

Thank you for thinking of me. I am back again after a sudden health
problem. Where were we last? ;)

OK, you caught me. I temporarily forgot why I did the script. It reads
the results of the formulations which include "if" clauses as well.
Your practice is a good example. It makes everything easier.

best
tek

[craigha...@hotmail.com]

Tek,

So will you still look at the development suggestions?

Craig H

[tekgergedan]

Yes, sure. I was working on it but now for some time I have to spend
my full time for my business.

Before that, I was trying on styles, regions, etc and on an easier
mentality, as well as on an integrated kml2xls tool which will be able
to extract path and polygon coordinates. But that is going to be
delayed. If the suggestion is not for something urgent, and I can
delay rather the suggestion, you will have saved a lot of time for
me. :)

tek

[ckg009]

Has anyone run into problems using the GOverlay feature of the xls2kml
feature?

Utilized the following values:

GO.BoundScale = 0 (tried 50 and 100 as well. Same results.)
GO.North = 51.1982268603816
GO.South = 51.1863457849046
GO.East = -114.451795600368
GO.West = -114.480610399632

The results in the kml file are:

<LatLonBox>
<north>52688722</north>
<south>49688722</south>
<east>-112966203</east>
<west>-115966203</west>
</LatLonBox>

Notice, (i) decimal point is missing, and, (ii) the values are always
+/- 1.5 degrees off of the original data point. From the manual, I
understand +/- 1.5 is a default value used to create the bounds for
the overlay. What cell(s) am I not properly filling in that is
triggering the macro to utilize the default offsets of the overlay?

Thanks,
Kevin

[tekgergedan]

Hi ckg,

No, you are not doing any wrong. There is a problem you've discovered.
Please, go on with your work as if it works. I'll re-reply here when
done the correction.

Thanks for the information
tek

[tekgergedan]

Hi again ckg,

Please, ignore my previous reply. The error was not affecting overlay
results as I just thought.

Instead, it seems you have the old version. Cell [A1] must say v.
1.12.01. If not, you need to redownload the link.

If you have it already, can you give me the Latitude and Longitude
data? And, to be sure, is your system set to use dot or comma to be
used as the decimal point?

tek

[tekgergedan]

Hi Craig,

I hope the new file is creating the bars as you wish. :)

After the 88th latitudes, it cannot work properly.

It is not a distance calculation but rather one based on results of
GE's measurement tool.

While testing, I experienced errors of 3 to 8-9 meters.
Here:
http://kml-support.googlegroups.com/web/test%20for%20xls2kml%20-%20diameters%20of%20statistical%20bars.kmz?gda=I40K8WUAAAAQOPUo9cIPFC3oe36kFBUS5h3GiGAK9JDhFeLaFIgRDGG1qiJ7UbTIup-M2XPURDQLDeG2hfkXLnvnqs3tCUXUpSTEft4wx5deSJ1aVfVebrfx9DMEFWgczUCrvJxixTh6-JV3gdl2nASkqVAeECel
are some placemarks to check the precision. Each folder contains two
placemarks for the N and S. They are the ends of 600k-meter distances
from the placemark whose coordinates have been typed in the name of
the folder.
To create circles, you increase the number of edges.


Another new thing is that it understands the DD MM SS format.

Critics welcome :)
tek

[gps...@googlemail.com]

first off exelent application i am using it for creation of KML's of
several locations and i need the <lookat> editable options

only down side is that i now have a time line displayed in GE i tried
to remove it but it caused errors to be displayed

i have cleared all fields that i dont use but it still shows

[tekgergedan]

Hi gpsfox,

Thanks for the comments. I am on things one of which is the LookAt. It
seems it will take some more time because there are many features I am
attempting to include.

However, I could not reproduce your problem. A sample kml will help
me.
BTW, if you clear the x marks (or any other string you entered) in
row3, no info from that column will be read. You don't have to remove
the cells or the contents of the cells.

:)

[AG]

Hi tek,

The script is really excellent but it fails when I try to process more
than 50 rows of data (upto Excel row 54 it works).

Is there any option to extend the range of processed rows. In my
application (water resources inventory) I may often need upto 1000 row
of data.

My most sincere congratulation.

Albert

[Starter]

Hi tek

I am using your script since september last year (V1.12.01) , it is
really excellent.
I have done some changes here is the result http://avex.org.free.fr/gresac/gresac.zip

One thing I believe does not work is the style when key is "normal"
and only when using Folder.
What I want to do to use this option when <key normal /key>l:
<LabelStyle>
<scale>0</scale>
</LabelStyle>

Meaning placemark Name not shown in the map, only when cursor is on
the placemark.

Do you see what I mean ?

Starter

[tekgergedan]

Hi AG and Starter,

Thanks for the feedback and idea.

AG, it must be working. Can you zip and attach the data with a few
rows not being processed? You don't need to send all fields but any
set of data that does not work with you. I need to check out how the
variables and loops are performing with the data.

Starter, I see what you mean. At this stage, I am on including every
kml feature (one more step and all will be doable so to get on the
next ideas). I'll try not to forget to check your wish to be sure it
is working well because the new version can potentially have some
problems although I am trying to predict every possible outcome. That
is, there are lots of combinations and I have to create something fast
and memory- and user-friendly . Please, continue as you do right now
and the new version will not need any editions in the database other
than including a few IDs for the features (style, region, extended
data, etc etc) a user needs to employ (if it gets impossible to
convert it to the new version, another easy script can do it -no
problem).

So nice to see so excellent collections are being created by this
tool. That encourages me to do more and more stuff. The bad thing is
that I have to survive too while enjoying people's use of the tool.

Before finishing it, I will publish a prototype when it is ready to be
criticised and argued.

Best
tek

[Meesh]

Hi there,
I am looking at using your script to post weather/storm reports from
CSV to Google Earth. However, the "Create kml" button does not seem
to be active. Am I missing a step needed to active this function?

Thanks!
Meesh

[tekgergedan]

Hi meesh,

You need to enable macros. If it does not ask whether to load the
macros or not, please select the Medium Level from Excel's
Options>Security window.

Or, if it is Excel 2007, enable the message bar (Options>Trust
Center>Trust Center Settings>Macro Settings), adjust the settings and
close Excel. Then reload the file. But, beware that the setting can
let run all malicious scripts too later on (so, you may need to turn
the settings back -a difficult oparation, true). If you don't want to
change the settings each time you are running Excel 2007, this script
needs a signature to be accepted as trustful. If you can, please stamp
it with your own signature and readjust the settings for your
security. Or, you'd better not to run it in Excel 2007.

Thanks :)

[tekgergedan]

sorry
"enable the message bar" -->> "enable macros"

[tekgergedan]

I have pushed 1.14.01
The version includes OpenOffice Calc application with the same script.
Please, notice some warnings in the Manual for OO because scripts in
OO work a bit different.
See the Version History page for two other editions.

http://www.emaltd.net/google/gec/utilities/

Best
tek

[Mark Reimer]

Hi,

I am very new to both google earth and your excel sheet.
However, I am working on a project where I need images and tables for
points throughout google earth.
I've looked at various spreadsheets, some on googledocs, so maybe this
isn't the best place to start.
However, I like your spreadsheet and it seems relatively
straightforward and customizable.

So, my question would be, is there a way to make the data displayed
through your sheet
appear in a table format? Would this require I write some html code?
As well, if I want an image
inside the pop-up bubble in GE, would I also need to do more html?

Again, I'm sorry if this is the wrong place to ask these questions,
but I just need some direction.
I have all my tables of data and images, I just need to import them
somehow, and make them user friendly.
Such that I can send this excel file to others to edit, create kml,
and view in google earth.

Thanks,

Mark

[tekgergedan]

Hi Mark,

Sure. Just paste your html codes of each placemark into the
Description cells.

You can make it calculate from other cells, too. Thereby, you can also
create templates. Just keep in mind that the columns with empty cells
in row#3 do not go into the balloons and can thereby be used for
formulas and calculations.

:) tek

[Mark Reimer]

Okay, thanks for the confirmation.
I'm still not sure if I can make it do what I want.
But that's just me needing to learn more html.

cheers,

Mark

[tekgergedan]

Attach data for two or three placemarks and I'll try to form the sheet
as you wish.

[Mark Reimer]

http://home.cc.umanitoba.ca/~umrei234/SampleData.xlsx

There is a brief sample of my data, there is a lot more than that, but
it pretty much repeats style.
What I am trying to do, and can write the html code, is display the
columns of data in a GE bubble.
I have it working so that it creates a table and displays the data....
but I have to manually input the information.
What I am trying to learn how to do, unsuccessfully so far, is allow
the user to pick an option from the drop down
list and create a kml file with a table displaying all information.

[tekgergedan]

http://groups.google.com/group/kml-support/web/sample.zip

This must be your need if I understand it correctly.
I directly copied the "values" and did not work with the calculations
and lookups from other sheets.
Notice I named the column headings. Also, double-quotes of HTML
properties must be avoided and single-quotes must be used, instead.

This tool works locally. So, you will need to create the kmls and
upload to the server. Then, the rest stuff with the drop-down list in
your web page must be easy.

I suggest you not to use, in the drop-down list's Option Values (of
the Select tag of HTML), the direct links of the kmls but the links of
NetworkLinks to the kmls. Thereby, your users will always be able to
have the latest versions. You can create them manually in GE, set the
refresh parameters and save-and-upload to your server.

If you want all in the web instead of working locally, then I must
have misunderstood you and you may need to use the Google web sheets.
There, the link of each document is used in GE's NetworkLinks, as you
may know. But I am not sure if it can do all this html balloons.

Please, let me know any further need or confusion.

:) tek

[Mark Reimer]

This is pretty much exactly what I needed, I know what I need to do
and what I can do from here.
Thank you very much for the help, I really appreciate it.

Mark

[Mark Reimer]

Hi Tek,

Sorry to bother you with another question, but is there a way I can
include another kml file into the excel table?

As in, when I Create a kmz from excel, it can link to and include a
past kml I have hosted online?

Would that require including the network link code somewhere in the
"create" script?

Mark

[tekgergedan]

"Sorry to bother you" >> No problem. Any time. I try help whenever I
am free.

Unfortunately, only v2 will be able to do that.

The only way is, as you said, pasting the NetworkLink codes into the
generated kml.
If you are not familiar; to do that, create the NL in GE,
r-click the NL on the Places pane and copy,
paste into NotePad,
copy the NetworkLink codes,
open the generated kml file with NotePad,
paste into it just before a <Placemark> or after a </Placemark> tag
and
save.

I am aware of that this is a long and needless operation. This kind of
cases are to be eliminated in v2.

:) tek

[Mark Reimer]

Alright, good to know it will work in the future.

I have been doing the copy and paste method, unfortunately I need (or
would like) it to be mobile with me
and it would be convenient to have it link up every time I regenerated
the kml from excel.

Oh well, it's only a few extra clicks.

Thanks for the prompt responses.

[Mark Reimer]

Hi Tek,

Me again. Just wondering if it's possible to set placemarks as visible
or not? It's not that important, but I think it would be a nice
feature. I went into your code and set the <visibility> tags to 1
instead of 0 in every tag. However, this seems like an all or nothing
approach. Would it be possible to pick which ones are visible?

I also made the first row of data into a second row of headings (thus
I can make major and minor headings) but of course you still need
coordinates for this. Regardless, it works, but maybe there is a way
to have a second row of headings without needing to add coordinates?

Furthermore, I really appreciate that you have made this sheet and
it's really helped me out, so thanks a lot! Keep up the good work. And
as I continue with my project, I'll continue to give feedback and
hopefully it'll be of some use to you.

Mark Reimer

Also, just recently, when I click on "create kml" it no longer
includes custom icons or the Path variables, it just happened
randomly. I just created the paths in a seperate kml file and linked
to it, but any idea why this would happen?

[Mark Reimer]

Please disregard the last "Also" paragraph. I solved my problem, I had
empty cells in the header row,
which, I assume tell the script to stop looking for "x"s. Anyways,
sorry about that.

[Taurin]

Tek-


You seem to be a good person to chat with about this...

I'm doing fine with the importing of the spreadsheets but now I'm
trying to figure a way to make the template look good without having
to individually rework the properties on every place mark...

I've got roughly 5,800 shopping centers to upload and the info looks
horrible with just a basic blank template. I'd like to apply one of
the bubble template designs similar to these

http://earth.google.com/intl/en/outreach/tutorial_balloon.html

I'm just importing them via Excel and the .csv file. I can play with
the templates a bit and the ican color but I'm looking for a nicer
overall bubble than just plain white with my text in it....

Thoughts?

-Taurin

[Mark Reimer]

Well, I'm not Tek, but I did something similar to using those balloon
templates.

What I did was load up the template you want into google earth. Then
look at the description, copy and paste the
code from within there into a blank word document, or Nvu (open source
html program), or dreamweaver.

Then edit to how you want it to look and paste into description column
of the spreadsheet.
Problem is, code must go into a cell like this:

="your code here"

which has a max of 255 characters, so you have to break up your code.
To reference cells use this:

="<table>"&B4&"</table>"

which would create a table in your balloon that holds whatever is in
cell B4, you need the & symbols.
Furthermore, you can ONLY have double quotation marks at the beginning
and end, and around your cell references, everywhere else
must be single quotations.

Sorry if this doesn't make sense, I'm rushed and at work, ha ha, when
I get home from work I'll throw together a sample excel sheet with
code.

Mark

[Ralph]

Hi Tek

First of all: great job! That was excactly what I was looking for!

I'm working in the swiss telecom industry and tried to convert my
excel data (all build mobile antennas) into kml by using your
spreadsheet. It all works pretty well unless you don't exceed a
certain amount of rows. There's an error msg with a code nr. 9 or so.
I know you have discussed this issue briefly with another guy but
still I would like to hear you again: a) is there any way to solve
this problem because I would really like to go on using your
spreadsheet for my needs, b) if - due to whatever limitations - it is
not working, do you know of any other program that does basically the
same job as your spreasheed (this question goes to anyone reading this
msg).

Thanks a lot for helping and keep going!

Ralph

[Fuad]

Merhaba Tek,

just to tell you your excel is great. We are developing a Atlas of
Genocide to mark places and victims of genocide in Bosnia committed by
Serbian military forces. I will have some questions along the way.

I have read all the posts but I still do not understand solution with
latitude and longitude. In my excel I have 43.85695 18.4088 in excel
but actual locations are 43°51'25.02"N 18°24'31.68"E why is this. I
want 43°85'69.50"N 18°40'88.00"E.

Can you help me on this.

Fuad

[tekgergedan]

Thank you all.

I was away for some time.
I'll recheck the questions carefully and reply. I can't tell the time.
So please, make e-mail subscription to this topic.

But, honestly, I am not planning to concentrate on this version again.
I will try to find out some tricks if I can.

:) tek

[tekgergedan]

Please download the new file. BalloonStyle, visibility and Snippet has
been added.

>>> Just wondering if it's possible to set placemarks as visible or not?

Yes, there it is. But only the pushpin placemarks can be set as
visible.

>>> maybe there is a way to have a second row of headings

Actually yes but it can confuse other users. It is already included in
v.2 which is much more flexible than this version.

>>> I'm trying to figure a way to make the template look good

What you mean is the BalloonStyle. That was a long-time wish. I could
not resist and spent some time on it. As Mark stated, there is an
Excel limitation to which care must be given.

Also, remember that it is a calculation sheet. To create a template,
employ that feature. Thereby, you can create conditional rich balloon
styles. You can insert an empty row and, below that row, you can make
the calculations needed. The link to the documentation about
BalloonStyle is here: http://code.google.com/apis/kml/documentation/kmlreference.html#balloonstyle

>>> works pretty well unless you don't exceed a certain amount of rows

I have to look more into this. Sorry if it creates inconvenience.

>>> In my excel I have 43.85695 18.4088 but actual locations are 43°51'25.02"N 18°24'31.68"E why is this. I want 43°85'69.50"N 18°40'88.00"E.

But they are different formats. The way it works is correct. I think
you mean the values shown by GE when you right-click on a placemark
and select Properties. You can switch to other formats via
Tools>Options in GE.

Thank you all again for the kind words. I am here and wait for wishes
and reports.
:) tek

[tekgergedan]

Something forgotten to tell: In the BalloonStyle, I did not include
the text color in order to refrain from increasing the number of the
columns and because text colors can already be set with the use of the
html tag (<font color="...">...</font>).

[Mark Reimer]

Hi Tek,

The new version is FANTASTIC! It would have saved me so much work on
htm coding, ha ha, I have about 15 or 16 columns of html code right
now.
However, this is going to make my sheet way easier to follow and edit,
thank you so much for this, keep up the great work!

Mark

[jeffb]

Tek,

I have a question for you. I am trying to use your awesome tool to
import several pieces of information. Is there a way to create
circles for each record? I don't want to have to create a circle
manually for each one of several thousand records. I am looking for
something that I can specify the center coordinates and a range, ie 25
nmi, and have your tool plot a 40 nmi circle around the center
coordinates that are used as the placemark coordinates. Is there a
way to do that?

Jeff

[tekgergedan]

Yes, I think. Use the columns for the statistical bars. They may seem
many in number but that's for useful manipulations. Please check out
the manual:
http://www.emaltd.net/google/gec/utilities/003.asp

They are at the far right. Remember, you can move the columns by
selecting from row#3(inclusive) downwards and dragging with the SHIFT
key pressed.

:) tek

[jeffb]

Thanks! It indeed does, although a little confusing for a beginner,
but I managed. I appreciate the quick response. One more question,
if you can help. Is there a way to automatically embed that into the
placeholder without creating another placeholder? I want to tie it
into the original placeholder, so that the circle will appear/
disappear with the named placeholder as you adjust the time bar.
Thanks! You have a truly awesome program and have already saved me
countless hours of work.

Jeff

[tekgergedan]

If I understand you correct, you have created the circle in another
row. Instead, try to create it in the same row with the
placemark(=placeholder?).

One row "means a set" of kml features (placemark, path, polygon,
overlay), grid and statistical bars. All these has the same name, goes
into the same folder, are positioned at the same coordinates/reference-
to-coordinates, and appear/disappear within the same timeline.

That's one of the powers of this tool. See the sample data, for
instance (but the bar coefficient is 1. it is too low and must be set
for instance to 5).

If it saves your hours, it is a gain for all of us.

:) tek

[tekgergedan]

Mark, now you can enter extra heading rows. Check out the first row to
the right.

The ID column is also effective anymore. See the manual.

>>> The reported error after a certain amount of rows
I have not been able to produce any error. There are people working
with a long list of records without any error. Whoever has this
problem, please, provide an example. There must be a data-type error
which is impossible to reproduce if you do not help.

Thanks
:) tek

[Mark Reimer]

I like the ID feature, before hand I Was using this:

<td><a href='#layer p42;balloonFlyto'>previous site</a>&nbsp;||
&nbsp;<a href='#layer p2;balloonFlyto'>next site</a></td>

Which was easy... once I figured out how the balloons were being
numbered. The ID feature is much easier.

As for extra headings row, I like that feature a lot. It still
requires me to put something in the "Name" field though, which is
fine, but if I leave it empty I get
runtime error '9' saying subscript is out of range. This is still so
much better than having extra placemarks that are doing nothing. Thank
you very much Tek.

Mark

[tekgergedan]

Yes, it was always indexing the IDs but I had not enough time to test
this feature.

Thanks for the bug report. I see where the bug hides. Clearing that
corner for the next minor update.

tek

[jeffb]

Tek,

Thanks for the help. I am kinda new at this, so I apologize for the
questions. I was wondering if there is a certain way that I have to
format the column name to add things like "relative to ground" and to
specify the altitude of the circle. Also, if there is a way to make a
dome, I would also be interested in that if it is pretty easy. I am
pretty new to programming and macros and such, and I am trying to
understand how the KML structure works. I appreciate the help.

Jeff

[tekgergedan]

>> apologize for the questions

Here, many people are volunteers for questions. No need to apologize
for anything, please.

>> column name...like "relative to ground"

This was a subject to decide before the start. Either it was to
include those non-defined labels as extra-data tables in the balloons
or was to include xml specifics. The prefered one was the former since
the former was being employed by people at those times while the
latter needs extra coding and extra user-effort to build up.

But there is a solution in v2 in whose development I have been
intervened a lot. It needs some more time to concentrate and work on.

>> column name...like... altitude of the circle

Unfortunately, I have never thought of this. Since it is statistics,
it has an altitude which can be set in the "Stats.Value" column. But
it is a bar. I think you wish a mere circle in the air.

Press ALT-F11 and when the codes come up, find the first appearance of
"<tessellate>1</tessellate>" and change that value to 0 (zero).

>> way to make a dome

Honestly, I don't think I can do it at this stage. Maybe, after the
new version is done. But valery has achieved something similar here:
http://bbs.keyhole.com/ubb/showflat.php?Cat=0&Number=1204388
I have not yet tried it myself. There are too many things to catch
up. :p

:) tek

[kirst]

Thanks Tek

I did the following mod;


Changed the name in the s/sheet range to scale
Changed the name in the s/sheet tilt to colour
Added a scale of 0.4 to my data
Added a colour of ffff80ff (pink) to my data

Changed the variable "range" to "scale"
Changed the variable "tilt" to colour
Commented out the following

'A.WriteLine ("<range>" & varArr1(varX1, 5) & "</range>")
'A.WriteLine ("<tilt>" & varArr1(varX1, 6) & "</tilt>")

Inserted the following lines

A.WriteLine (" <IconStyle>")
A.WriteLine (" <Scale>" & varArr1(varX1, 5) & "</Scale>")
A.WriteLine (" <Colour>" & varArr1(varX1, 6) & "</
Colour>")
A.WriteLine (" <Icon>" & varArr1(varX1, 8) & "</Icon>")
A.WriteLine (" </IconStyle>")

The produced kml however displays the icons at a scale of 1.0 and no
colour change....
sample of code shown below


<Placemark id="layerP1">
<StyleMap id="myicon_1">
<Pair>
<key>normal</key>
<styleUrl>#sn_1_1</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#sh_1_2</styleUrl>
</Pair>
</StyleMap>
<Style id="sn_1">
<IconStyle>
<Scale>0.4</Scale>
<Colour>0</Colour>
<Icon>http://maps.google.com/mapfiles/kml/pal5/icon7.png</Icon>
</IconStyle>
<ListStyle>
</ListStyle>
</Style>
<Style id="sh_1">
<IconStyle>
<Scale>0.4</Scale>
<Colour>0</Colour>
<Icon>http://maps.google.com/mapfiles/kml/pal5/icon7.png</Icon>
</IconStyle>
<ListStyle>
</ListStyle>
</Style>
<TimeSpan id="layer ts1">
</TimeSpan>
<name> </name>
<visibility>0</visibility>
<Snippet maxLines="0" id="s1">
</Snippet>
<description>
<![CDATA[
alocationlatlong: 43 29 51.876 S 172 41 18.492 E C 0<br>aid:
125520<br>ainstalleddate: 10/04/2008<br>alocationlatseconds:
156591.876<br>ameshenabled: True<br>alocationlongseconds: 1269678.492
]]>
</description>
<LookAt>
<longitude>172.68847</longitude>
<latitude>-43.4977433333333</latitude>
<altitude>0</altitude>
<heading>0</heading>
<altitudeMode>relativeToGround</altitudeMode>
</LookAt>
<styleUrl>#myicon_1</styleUrl>
<Point>
<coordinates>
172.68847,-43.4977433333333,0
</coordinates>

Can anybody see what I have done wrong???? I am pretty new to
this ....

Cheers
Kirst

[tekgergedan]

Well, you are lost somewhere. Do the following and it will work:

You can find the 'point placemarks' by searching
"<BalloonStyle" (without quotes). There are two of them each contained
by Styles where the IconStyles are also residing. (OK, one of them
might be unnecessary but it works this way at the moment)

You will see the scales (one is <scale>1.1</scale>, the other is
<scale>1.3</scale>). Remove their lines completely.

See the next lines in both of each:

A.WriteLine (" <Icon>" & varArr1(varX1, 8) & "</Icon>")

Change them to the following:
A.WriteLine (varArr1(varX1, 8))

Now, the Icon column can be used for the whole content of IconStyle.
The documentation is here:
http://code.google.com/apis/kml/documentation/kmlreference.html#iconstyle

That means; as an example, paste the following into the Icon column's
cell:
<color>ffff2500</color><colorMode>normal</colorMode><scale>1</
scale><Icon><href>YOUR ICON URL</href></Icon>

:) tek

[jeffb]

Tek,

I got the circle to go up in altitude just fine by adjusting the
Stats.Value column. One question I do have is when I put in the
coordinates for a location according to the corresponding location in
GE, it plots the location with a different set of coords in a
different location. It is relatively close, however as the location
is along the coastline, I need my point to be accurate, not out in the
ocean somewhere. Any idea why that might be?

Jeff

[kirst]

Tek

You are fantastic!!! This works perfectly - I cannot thank you
enough

Kirst

On Jul 16, 11:47 pm, tekgergedan wrote:
> Well, you are lost somewhere. Do the following and it will work:
>
> You can find the 'point placemarks' by searching
> "<BalloonStyle" (without quotes). There are two of them each contained
> by Styles where the IconStyles are also residing. (OK, one of them
> might be unnecessary but it works this way at the moment)
>
> You will see the scales (one is <scale>1.1</scale>, the other is
> <scale>1.3</scale>). Remove their lines completely.
>
> See the next lines in both of each:
> A.WriteLine ("  <Icon>" & varArr1(varX1, 8) & "</Icon>")
> Change them to the following:
> A.WriteLine (varArr1(varX1, 8))
>
> Now, the Icon column can be used for the whole content of IconStyle.

> The documentation is here:http://code.google.com/apis/kml/documentation/kmlreference.html#icons...

> > Kirst- Hide quoted text -
>
> - Show quoted text -

[tekgergedan]

Thanks, Kirst. You know, it is spreadsheet of formulation. The cell
now contains four kml inputs. You can insert new columns for each with
their row#3 empty and make the Icon cell calculate from their values.
But by inserting columns, I mean inserting cells begining from row#3
downwards. The first and second rows should not be affected or edited
in anyway.

Jeff, I need at least one-record example. If you don't want to share
it here, can you duplicate the problem somewhere else on earth? Or
feel free to e-mail me.

:) tek

[jeffb]

I figured out the problem. The info goes into your spreadsheet in
decimals, and the coordinates don't convert exactly alike. I figured
out how to adjust them so it is all good now. Thanks!

Jeff

[jeffb]

Tek,

I was looking through your macro and I was wondering if you have a
copy of it that is commented out so that I can learn what you did. I
am not trying to take anything from what you did by any means, I just
want to change a little bit and adapt it to my needs. I just don't
know exactly what each thing was that you did. If you have something
that explains it, it would be very beneficial to me. Thanks.

Jeff