Google Earth- Making KML files (1 Viewer)

[robyholmes]

Hi, I am looking to use this code to generate a KML file containing address of all our customers that visit are cottages. So I want a really simple code that will generate the KML file. I will then change the recordset to match what information I want to pass thought the VBA code and in to the KML file.

Here is the table and field names:
tblCustomer

title
fristName
lastName
addressLine1 = 13 Plain Lane (Sample)
addressLine2
addressLine3
city
county
country
postCode

So could you search via, addressline1, city and country?

Thanks a lot

 

[Fear Naught]

This sounds an excellent uitlity. Any chance of a sample database being posted. Regrettably the blog that was referred to earlier in this thread has now been removed!!

 

[TheWatchStander]

I have read through this topic and applied what I learned. Here is my code:

Private Sub Command33_Click()
DoCmd.OpenQuery "qryCreateKML"
'Export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
QryOrTblDef = "qryCreatKML"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(QryOrTblDef)

Open "D:\KMLTest.kml" For Output Shared As #1

Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #1, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
Print #1, "<Document>"
Print #1, " <name>KMLTest.kml</name>"
Print #1, " <Folder>"
Print #1, " <name>Test</name>"
Print #1, " <open>1</open>"
Print #1, " <Folder>"
Print #1, " <name>KML</name>"
Print #1, " <open>1</open>"
Print #1, " <Snippet maxLines=""2"">Whatever</Snippet>"
Print #1, " <description><![CDATA[]]></description>"
With MyRS
Do Until .EOF
Print #1, " <Placemark>"
'Print #1, " <Point>"
strText = " <description><![CDATA[FIELDNAME0:" & MyRS.Fields(0) & "<br>FIELDNAME1:" & MyRS.Fields(1) &
"]]></description>"
Print #1, strText
strText = " <address> " & MyRS.Fields(0) & "</address>"
Print #1, strText
strText = " <name>" & MyRS.Fields(1) & "</name>"
Print #1, strText
'Print #1, " <Snippet maxLines="; 2; "></Snippet>"
'Print #1, " <description><![CDATA[]]></description>"
'Print #1, " <LookAt>"

'Print #1, " </LookAt>"
'Print #1, " </Point>"
Print #1, " </Placemark>"
.MoveNext
Loop
End With
Print #1, " </Folder>"
Print #1, " </Folder>"
Print #1, " </Document>"
Print #1, "</kml>"
Close #1
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
'Shell "ENTERKMLFILENAMEHERE"
End Sub

I get an error that says "Too few parameters. Expected 1."

Addition info:
When I run the query it gives me a table as follows

GeoLat | GeoLong | EventID |
----------------------------------
12 | 123 | Name |

I also have a query that returns

UpperLeft | EventID |
----------------------------------
12 N , 110 E| Name |

Can anyone please help me format this code to work with my query?

Thanks!

 

[georgedwilkinson]

What line is high-lighted?

What is this doing/not doing that you want it to not do/do?

 

[TheWatchStander]

It never takes me to debugger, just gives me the error I referenced so I cant tell you what line. I want it to take the Lat/Long field and write a KML file that will then open with Google earth and zoom to the Lat/Long. The query I made only displays the Lat/Long and Name of the location I want. And looks like the examples I gave. I am gonna revise it to get rid of the N/S and replace it with " "/"-".

 

[georgedwilkinson]

I got the code to work and got the following result (after fixing the obvious problems):

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.1">
<Document>
 <name>KMLTest.kml</name>
 <Folder>
 <name>Test</name>
 <open>1</open>
 <Folder>
 <name>KML</name>
 <open>1</open>
 <Snippet maxLines="2">Whatever</Snippet>
 <description><![CDATA[]]></description>
 <Placemark>
 <description><![CDATA[FIELDNAME0:12<br>FIELDNAME1:123]]></description>
 <address> 12</address>
 <name>123</name>
 </Placemark>
 <Placemark>
 <description><![CDATA[FIELDNAME0:12<br>FIELDNAME1:123]]></description>
 <address> 12</address>
 <name>123</name>
 </Placemark>
 <Placemark>
 <description><![CDATA[FIELDNAME0:12<br>FIELDNAME1:123]]></description>
 <address> 12</address>
 <name>123</name>
 </Placemark>
 </Folder>
 </Folder>
 </Document>
</kml>

A couple of things I had to do to make it work:
1. Make sure I had a drive I could write to. On my computer, I don't have a writable "D:" drive so I had to change "D:\KMLTest.kml" to "c:\KMLTest.kml".
2. Create the query the data would contain. I just dummied that up with the data you provided above and querying null from one of my own tables. BTW, the query I created was named "qryCreatKML". Please notice the spelling, which you provided in your code, above (this could be your problem or at least, a problem).
3. Remove the unnecessary call to "DoCmd.OpenQuery "qryCreateKML""

When you're troubleshooting stuff like this, don't do it from a command button. Actually open the code in a module and use the F8 key to single step through it. That will allow you to identify which line may give problems.

You'll notice I cleaned up several things:

Private Sub Command33_Click()
'DoCmd.OpenQuery "qryCreateKML"
'Export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
Dim iFile As Integer
    QryOrTblDef = "qryCreatKML"
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
    iFile = FreeFile
    
    Open "c:\KMLTest.kml" For Output Shared As #iFile
    
    Print #iFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #iFile, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
    Print #iFile, "<Document>"
    Print #iFile, " <name>KMLTest.kml</name>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Test</name>"
    Print #iFile, " <open>1</open>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>KML</name>"
    Print #iFile, " <open>1</open>"
    Print #iFile, " <Snippet maxLines=""2"">Whatever</Snippet>"
    Print #iFile, " <description><![CDATA[]]></description>"
    With MyRS
        Do Until .EOF
            Print #iFile, " <Placemark>"
            'Print # iFile, " <Point>"
            strText = " <description><![CDATA[FIELDNAME0:" & MyRS.Fields(0) & "<br>FIELDNAME1:" & MyRS.Fields(1) & "]]></description>"
            Print #iFile, strText
            strText = " <address> " & MyRS.Fields(0) & "</address>"
            Print #iFile, strText
            strText = " <name>" & MyRS.Fields(1) & "</name>"
            Print #iFile, strText
            'Print # iFile, " <Snippet maxLines="; 2; "></Snippet>"
            'Print # iFile, " <description><![CDATA[]]></description>"
            'Print # iFile, " <LookAt>"
            
            'Print # iFile, " </LookAt>"
            'Print # iFile, " </Point>"
            Print #iFile, " </Placemark>"
            .MoveNext
        Loop
    End With
    Print #iFile, " </Folder>"
    Print #iFile, " </Folder>"
    Print #iFile, " </Document>"
    Print #iFile, "</kml>"
    Close #iFile
    MyRS.Close
    Set MyRS = Nothing
    Set MyDB = Nothing
    'Shell "ENTERKMLFILENAMEHERE"
End Sub

Some highlights:
1. Always format your code so people can follow it by indenting.
2. Don't open a file with a constant. Use Freefile() to get a free file handle.

 

[TheWatchStander]

Your code looks sound and if you got it to work then great. I might be having an issue with my execution method. I created a Command button on a form that executes the code you are helping me with upon click. But when I click that button I get the error

"Too few parameters. Expected 1."

Maybe there is a better way to do this.

The query I am using (qryCreateKML) queries a table for the Lat/Long field associated with the Name I select on the form. This table has numerous "names" and associated Lat/Longs for each. But when I execute this command I want it to only write a KML for the current selected "name". I am not sure why I am getting this Parameter error. I will go over my code again while I wait to see if anything I have said helps.

Thanks again.

 

[TheWatchStander]

Also something I just thought of, shouldnt it be <coordinate> versus <address>?

 

[TheWatchStander]

Am I correct to assume the following:

strText = " <address> " & MyRS.Fields(0) & "</address>"
Print #iFile, strText
strText = " <name>" & MyRS.Fields(1) & "</name>"
Print #iFile, strText
​

The strText = " <address>" & MyRS.Fields(0) & "</address> puts the data contained in the 1st field of my query into the code at that point?

Since my query results in the following

Lat | Long | Name |
--------------------
XX | XXX | Test |
​

I would need to put both fields 1 and 2 into that address line? If so how would I structure it to put "Field1, Field2" (note the comma and the space)
something like

& MyRS.Fields(0) & , & & MyRS.Fields(1) &
​

Maybe?

 

[TheWatchStander]

Found the parameter error...

Set MyRS = MyDB.OpenRecordset (QrtOrTblDef)

is highlighted

 

[georgedwilkinson]

Set MyRS = MyDB.OpenRecordset (QrtOrTblDef)

What is "QrtOrTblDef"? That was not in the code you posted or in the response I sent you. Please check your spelling.

Also, don't bother troubleshooting this from the command button. Go directly into the Visual Basic editor and put it in a module to troubleshoot it (use F8 to single step). Once you have it right, then just call your subroutine (make it Public) from your button.

I don't know anything about formatting KML files so somebody else will have to answer those questions.

As far as the order of the fields, the first field in the query is "0". You can format them however you want using VBA.

 

[TheWatchStander]

Would it matter if my query has a Criteria in it?

 

[TheWatchStander]

What is "QrtOrTblDef"? That was not in the code you posted or in the response I sent you. Please check your spelling.

Also, don't bother troubleshooting this from the command button. Go directly into the Visual Basic editor and put it in a module to troubleshoot it (use F8 to single step). Once you have it right, then just call your subroutine (make it Public) from your button.

I don't know anything about formatting KML files so somebody else will have to answer those questions.

As far as the order of the fields, the first field in the query is "0". You can format them however you want using VBA.

Correct it was a typo in translation from the PC I am coding on to the one I am messaging you on.

Set MyRS.OpenRecordset(QryOrTblDef)

give me Runtime error 3061
Too few parameters. Expected 1.

​

The format for this is

OpenRecordset(name as string, [type], [options] [lockedit] As Recordset

​

I just cant figure out how to get past the parameter issue. I have tried using the name of the field from the query. I was wondering too if it had to do with the criteria in my query. I used a criteria to specify which Lat/Long to display based on the Selected Name on the form.

 

[TheWatchStander]

If I change it to

Set MyRS.OpenRecordset(QryOrTblDef, [UpperLeft])

Where UpperLeft is the name of the first field in the query I get Runtime error 3421: Data type conversion error.

 

[rexmorgan]

I was finally able to stumble my way through the code posted. I have included my slight differences. Mostly deleting commented out code and added the code to launch explorer and subsequently google earth.

Private Sub Command5_Click()

'export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
Dim iFile As Integer
    QryOrTblDef = "******"  'where the *s are your query name
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
    iFile = FreeFile
    
    Open "*******" For Output Shared As #iFile 'where *s are the .kml file name
    
    Print #iFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #iFile, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
    Print #iFile, "<Document>"
    Print #iFile, " <name>KMLTest.kml</name>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Spotters</name>"
    Print #iFile, " <open>1</open>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Daniels County</name>"
    Print #iFile, " <open>1</open>"
    'Print #iFile, " <Snippet maxLines=""2"">Whatever</Snippet>"
    Print #iFile, " <description><![CDATA[]]></description>"
    With MyRS
        Do Until .EOF
            Print #iFile, " <Placemark>"
            strText = " <description><![CDATA[Name: " & MyRS.Fields(0) & "<br>City: " & MyRS.Fields(1) & "<br>Phone: " & MyRS.Fields(3) & "]]></description>"  'where Name, City, & Phone are column name in my query
            Print #iFile, strText
            
            strText = " <name>" & MyRS.Fields(0) & "</name>"
            Print #iFile, strText
            
	    
            
	    Print #iFile, "<Point>"
	    strText = "<coordinates>" & MyRS.Fields(4) & "</coordinates>"
	    Print #iFile, strText 
	    Print #iFile, " </Point>"
            Print #iFile, " </Placemark>"
            .MoveNext
        Loop
    End With
    Print #iFile, " </Folder>"
    Print #iFile, " </Folder>"
    Print #iFile, " </Document>"
    Print #iFile, "</kml>"
    Close #iFile
    MyRS.Close
    Set MyRS = Nothing
    Set MyDB = Nothing
    Call Shell("explorer.exe " & "*****.kml", vbNormalFocus)  'where *s are path and name of .kml file
End Sub

Thanks for all of the help. I do have one other question. Does anyone know how to add custom icons based on a filed in a table. Also is there a folder location that I can store my custom icons in so they are always available. Right now I have several custom icons and can add them, but when I close GE I have to browse to them again. I would like to just drop my icons to wherever the default placemark icons are located. Any ideas? I will post a dummy access file with tables and forms for anyone who has not been able to understand it yet.

 

[rzt]

Hi

I have just stumbled across this discussion, I'm hoping that rexmorgan is still tracking this and will be able to upload a simple access file that demonstrates his vba to kml code, or if anyone else can implement the code so I can play with something that works, before trying to code something myself as I have little vba experience and no kml experience. And was there a solution to using different icons based on a field in the table?

Thanks

 

[rexmorgan]

I am attaching a zipped file of a sample database. It took me a while to delete sensitive info and replace with bogus data. I believe all of the personal info has been scrubbed.


After you unzip go to the form named 'Login'.
Don't forget to enable content on the security Warning thing.
Click the 'KMLtest' button.
This will generate a KML file that you can open with Google Earth.

Another feature you may find handy is the mapping using the Bing mapping.
You can got to the form named 'SpotterForm'.
Click the picture of the world map (just right of 'Map this address')
This will launch your default web browser and bring up the address in Bing Maps. A pretty handy feature.

Some of the things in this database will not work but the KML part you are interested in does work. I have a completed database with all of the things on it working but I have not attempted to scrub all the personal data out of it yet. If you are still interested in some of the other stuff I will make an attempt to get you another sample that you could use. Hope this helps. If you have any questions just ask, I will do my best to answer.

 

[rzt]

Thanks heaps, I'll have a play around with it and get back to you.

 

[outismetis]

Thanks, rexmorgan. Your file has been incredibly useful this afternoon. For some reason an extra ,0 is being amended onto the coordinates--all my points are showing up in Antarctica. Any suggestions as to why that would be the case? I've been thrown into figuring out Access, and I'm not having much luck troubleshooting.

Nevermind, I solved my user error.

 

[Oreoking87]

Re: Multiple Layers

I was wondering if you could give an example of the batch file you use to open the kml file with VBA. I'm using windows 7 and the simple batch file I'm trying to use is:

@Echo off

Start "C:\mycomputer\.......\MyGoogle.kml"


all it does is open the black command window.


Dim RetVal
RetVal = Shell("O:\opengoogleearth.bat", 1)
'Runs a batch file to open the KML file in google earth
End Sub

[/CODE][/QUOTE]