tags:

views:

286

answers:

3

I am using this code to make a KML file:

Sub Create_KML()
' Builds KML from spread sheet
' Keyboard Shortcut: Ctrl+c
'
'Trash = Application _
'    .GetOpenFilename("Text Files (*.txt), *.txt")
'Open File For Random As #1 ' "c:\Users\mwilson\Desktop\Trash.txt
'Open "c:\Users\mwilson\Desktop\Test Google\Trash.txt" For Output As #1
Open "C:\Documents and Settings\eecoop\Desktop\10 day avg rssi Google kml\Trash.kml" For Output As #1
y = 9
Address = Range("B" & y) ' "203 Jordan, Tallassee, AL"
While Address <> ""
'**************
Print #1, "<?xml version=""1.0"" encoding=""utf-8""?>"         '1
Print #1, "<kml xmlns=""http://www.opengis.net/kml/2.2""&gt;"     '2
Print #1, "   <Placemark>"                                        '3
Print #1, "     <name>George</name>"                           '4
Print #1, "        <Style>"                                     '5
Print #1, "           <IconStyle>"                              '6
Print #1, "               <scale>.3</scale>"                    '7
Print #1, "               <Icon>"                               '8
Print #1, "                  <href>Green.png</href>"             '9
Print #1, "               </Icon>"                             '10
Print #1, "            </IconStyle>"                            '11
Print #1, "        </Style>"                                     '12
Print #1, "     <address>" & Address & "</address>"    '13
Print #1, " </Placemark>"                                        '14
Print #1, "</kml>"                                               '15
Print #1, " "
'********
'    Range("A2").Select
        ID = ActiveCell.FormulaR1C1
'    Range("B2").Select
        Address = ActiveCell.FormulaR1C1
'    Range("C2").Select
'        RSSI = ActiveCell.FormulaR1C1
'    Trash.Write ("hi")

'    Print #1, ID
y = y + 1
Address = Range("B" & y)
Wend
    Close #1
End Sub

For the most part it works just fine, except for the fact that when I try to set Address equal to Range("B" & Y) which has the string:

203 Jordan, Tallassee, AL

When I do this, it prints it off the screen of Google Earth whereas if I use the quoted address directly, i.e.: Address = "203 Jordan, Tallassee, AL"

It works just fine and plots where and how it is supposed to. I have tried to make sure Address is formatted as text when pulling from the Excel cell, but the error keeps on happening.

What am I doing wrong?

A: 

Try to open the KML with a text editor and see if the <address> line is formatted differently in your 2 cases.

Igor Krivokon
Oh trust me i've tried that repeatedly, There is absolutely no difference as seen in Notepad from the version that does work verses the version that doesn't work. i'm fixing to open them up in microsoft word to try and see if there is a font difference or something.
Bryan
A: 

I would try using

Address("B" & y).Value
cstr(Address("B" & y))
Nathan Fisher
A: 

I figured it out. There was a difference in the two items being entered.

In the Excel file I got from my boss, he spelled Tallassee wrong. He used only one s. I also had to change:

> & Address & <

to:

>; Address; "<

Now it works fine. Also, when you are plotting a lot of points at once, points that Google Earth has not gotten around to yet will be shown as though they are off screen.

Bryan
@Rich B: Um, "Tallassee, AL" is a perfectly valid city name (http://maps.google.com/maps?q=Tallassee,+AL). Any reason you're making unnecessary edits?
Adam V
@Adam: YOU ARE ONTO MY EVIL PLAN!!!
Geoffrey Chetwood