Opening a KML file through VBA

[Vinod]

Hi,
I have a added the latitude and longitude fields to my database and
have code to generate a kml file for a particular record in question.
My code works fine, it creates a KML file in my C drive and when i
manually double click it, it take me to the area of my interest. but I
want the file to be opened automatically once it is created.
I working with VBA access. I am able to open google earth by using
shell command but I actually open the kml file, when I try opening it
it gives me a error message that file not found. but actually the file
exists and I can open it manually.

here my code the

Private Sub cmdaerial_Click()
'DoCmd.OpenQuery "qryIntEncumberedTrips"

Dim db As Database
Dim rstAerial As Recordset
Dim fld As Field
Dim qry As String
Dim strtext As String
Dim id

id = Form_TMC_Dairy.Intersection_ID.Value
Set db = CurrentDb()

Set rstAerial = db.OpenRecordset("SELECT
tblcmsintersections.globalID,tblcmsintersections.Latitude,tblcmsintersections.Longitude
FROM tblCMSIntersections WHERE (tblCMSIntersections.GlobalID = " & id
& " );")
strtext = " <coordinates>" & rstAerial.Fields(1) & "," &
rstAerial.Fields(2) & ",0</coordinates>"

Open "C:\KMLFile.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>"

With rstAerial
Do Until .EOF
Print #1, " <Placemark>"
Print #1, " <Point>"
strtext = " <coordinates>" & rstAerial.Fields(1) & "," &
rstAerial.Fields(2) & "</coordinates>"
Print #1, strtext
Print #1, " </Point>"
Print #1, " </Placemark>"
.MoveNext
Loop
End With

Print #1, "</Document>"
Print #1, "</kml>"
Close #1
rstAerial.Close


Set rstAerial = Nothing
Set db = Nothing
dim retval
retval = shell("C:\PROGRAMFILE\GOOGLEEARTH.EXE",1) ' THIS WORKS

if I try shell("c:\kmlfile.kml",1) it give me a error that file not
found.

[rsgrillo]

Hi Vinod

You can use the file protocol handler:

retval = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " &
yourFilePath, vbNormalFocus)

Cheers

rsgrillo
www.sgrillo.net

On Nov 4, 11:14 am, Vinod wrote:
> Hi,
> I have a added  the latitude and longitude fields to my database and
> have code to generate a kml file for a particular record in question.
> My code works fine, it creates a KML file in my C drive and when i
> manually double click it, it take me to the area of my interest. but I
> want the file to be opened automatically once it is created.
> I working with VBA access. I am able to open google earth by using
> shell command but I actually open the kml file, when I try opening it
> it gives me a error message that file not found. but actually the file
> exists and I can open it manually.
>
> here my code the
>
> Private Sub cmdaerial_Click()
> 'DoCmd.OpenQuery "qryIntEncumberedTrips"
>
> Dim db As Database
> Dim rstAerial As Recordset
> Dim fld As Field
> Dim qry As String
> Dim strtext As String
> Dim id
>
> id = Form_TMC_Dairy.Intersection_ID.Value
> Set db = CurrentDb()
>
> Set rstAerial = db.OpenRecordset("SELECT

> tblcmsintersections.globalID,tblcmsintersections.Latitude,tblcmsintersectio­ns.Longitude