Creating a custom text export file (inc. header & footer) from a q

[efandango]

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer into a
standard text file, and then save it with a ‘.kml’ extension, for example
‘Addresses.kml’

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Tom Ventouris]

Search for this text in this group: "Export to File.Dat"
You will find the helpful answer I got from Ken Snell on renaming your
exported text file.

 

[John Nurick]

There are many ways of skinning this cat. I'd probably do something like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer into a
standard text file, and then save it with a ‘.kml’ extension, for example
‘Addresses.kml’

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

John,

thanks for your reply. I must confess it seems a little 'hardcore' for a
novice such as me. Can you walk me through the broad points of the syntax
used?

There are many ways of skinning this cat. I'd probably do something like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer into a
standard text file, and then save it with a ‘.kml’ extension, for example
‘Addresses.kml’

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

John,

I have 'filled in' your code to thus far:

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDb.Open("Generate KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

End Sub


But I am stuck on the Header and Footer:

What do I call them?; just header.txt footer.txt. do i put the full path in?

D:\folder\header.txt

and how do i get them to insert into the 'Addresses.kml' file?

There are many ways of skinning this cat. I'd probably do something like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer into a
standard text file, and then save it with a ‘.kml’ extension, for example
‘Addresses.kml’

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on the word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I was worried
that the query it was calling was two seperate words, so I changed it to one
whole_word. But it still reports the same error message.

Can you help me with this?

There are many ways of skinning this cat. I'd probably do something like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer into a
standard text file, and then save it with a ‘.kml’ extension, for example
‘Addresses.kml’

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[John Nurick]

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on the word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I was worried
that the query it was calling was two seperate words, so I changed it to one
whole_word. But it still reports the same error message.

Can you help me with this?

There are many ways of skinning this cat. I'd probably do something like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer into a
standard text file, and then save it with a ‘.kml’ extension, for example
‘Addresses.kml’

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on the word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I was worried
that the query it was calling was two seperate words, so I changed it to one
whole_word. But it still reports the same error message.

Can you help me with this?

There are many ways of skinning this cat. I'd probably do something like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text strings for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text to be
inserted. Does anyone know how I can pre-insert the Header and Footer into a
standard text file, and then save it with a ‘.kml’ extension, for example
‘Addresses.kml’

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Douglas J. Steele]

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I was
worried
that the query it was calling was two seperate words, so I changed it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text
to be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...). The .kml
file only contains the first address in the query, and I think it's because
of my badly worded sentence 'I have an address query which ouputs a single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What I really
meant was it is a single field (among other fields in the query), but that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that field to
the kml file?

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I was
worried
that the query it was calling was two seperate words, so I changed it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text
to be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

help?...

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...). The .kml
file only contains the first address in the query, and I think it's because
of my badly worded sentence 'I have an address query which ouputs a single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What I really
meant was it is a single field (among other fields in the query), but that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that field to
the kml file?

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I was
worried
that the query it was calling was two seperate words, so I changed it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer text
to be
inserted. Does anyone know how I can pre-insert the Header and Footer
into a
standard text file, and then save it with a '.kml' extension, for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Douglas J. Steele]

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

help?...

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...). The
.kml
file only contains the first address in the query, and I think it's
because
of my badly worded sentence 'I have an address query which ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What I
really
meant was it is a single field (among other fields in the query), but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that field to
the kml file?

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I
was
worried
that the query it was calling was two seperate words, so I changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer
text
to be
inserted. Does anyone know how I can pre-insert the Header and
Footer
into a
standard text file, and then save it with a '.kml' extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas,

that worked, i now have a full list of records from the field, though i have
a slight problem at the Google Earth end which I think is just a syntax
problem with the header text.

Meanwhile, I am very grateful for your help.

much appreciated.

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

help?...

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...). The
.kml
file only contains the first address in the query, and I think it's
because
of my badly worded sentence 'I have an address query which ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What I
really
meant was it is a single field (among other fields in the query), but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that field to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I
was
worried
that the query it was calling was two seperate words, so I changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer
text
to be
inserted. Does anyone know how I can pre-insert the Header and
Footer
into a
standard text file, and then save it with a '.kml' extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas,

Can you help advise on this VBA problem please?

When I paste or type this line:

Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"

I get

Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"

the 1.0 becomes 1#

I can't seem to get the VBA window to accept John's code, weird or some kind
of switch that is turned off?

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

help?...

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...). The
.kml
file only contains the first address in the query, and I think it's
because
of my badly worded sentence 'I have an address query which ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What I
really
meant was it is a single field (among other fields in the query), but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that field to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I
was
worried
that the query it was calling was two seperate words, so I changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and footer
text
to be
inserted. Does anyone know how I can pre-insert the Header and
Footer
into a
standard text file, and then save it with a '.kml' extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Douglas J. Steele]

Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"

Is the expected string supposed to have two double quotes on either side of
UTF-8? If so, you need

Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

(The rule is everytime you want a quote to appear inside a quoted string,
you need to double the quotes.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Can you help advise on this VBA problem please?

When I paste or type this line:

Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"

I get

Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"

the 1.0 becomes 1#

I can't seem to get the VBA window to accept John's code, weird or some
kind
of switch that is turned off?

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

help?...

:

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...). The
.kml
file only contains the first address in the query, and I think it's
because
of my badly worded sentence 'I have an address query which ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What I
really
meant was it is a single field (among other fields in the query), but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that field
to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I
was
worried
that the query it was calling was two seperate words, so I
changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and
footer
text
to be
inserted. Does anyone know how I can pre-insert the Header
and
Footer
into a
standard text file, and then save it with a '.kml' extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas,

When I use your line:
Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

I get: (2 pairs of double quotes)

<?xml version="1.0" encoding=""UTF-8""?>

If I remove 1x" from either side of """"UTF-8"""" the VBA puts spaces
between the characters, like this:

UTF - 8

For the record, this is the line google earth needs

<?xml version="1.0" encoding="UTF-8"?>

Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"

Is the expected string supposed to have two double quotes on either side of
UTF-8? If so, you need

Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

(The rule is everytime you want a quote to appear inside a quoted string,
you need to double the quotes.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Can you help advise on this VBA problem please?

When I paste or type this line:

Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"

I get

Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"

the 1.0 becomes 1#

I can't seem to get the VBA window to accept John's code, weird or some
kind
of switch that is turned off?

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


help?...

:

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...). The
.kml
file only contains the first address in the query, and I think it's
because
of my badly worded sentence 'I have an address query which ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What I
really
meant was it is a single field (among other fields in the query), but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that field
to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I
was
worried
that the query it was calling was two seperate words, so I
changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and
footer
text
to be
inserted. Does anyone know how I can pre-insert the Header
and
Footer
into a
standard text file, and then save it with a '.kml' extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Phew!

I managed to do it!!!

Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"

gives me the correct: <?xml version="1.0" encoding="UTF-8"?>

Thanks so much Douglas (and John...)

happy days!

Douglas,

When I use your line:
Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

I get: (2 pairs of double quotes)

<?xml version="1.0" encoding=""UTF-8""?>

If I remove 1x" from either side of """"UTF-8"""" the VBA puts spaces
between the characters, like this:

UTF - 8

For the record, this is the line google earth needs

<?xml version="1.0" encoding="UTF-8"?>

Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"

Is the expected string supposed to have two double quotes on either side of
UTF-8? If so, you need

Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

(The rule is everytime you want a quote to appear inside a quoted string,
you need to double the quotes.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Can you help advise on this VBA problem please?

When I paste or type this line:

Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"

I get

Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"

the 1.0 becomes 1#

I can't seem to get the VBA window to accept John's code, weird or some
kind
of switch that is turned off?



:

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


help?...

:

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...). The
.kml
file only contains the first address in the query, and I think it's
because
of my badly worded sentence 'I have an address query which ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What I
really
meant was it is a single field (among other fields in the query), but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that field
to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I
was
worried
that the query it was calling was two seperate words, so I
changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and
footer
text
to be
inserted. Does anyone know how I can pre-insert the Header
and
Footer
into a
standard text file, and then save it with a '.kml' extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas, John.

Now that I have the code working, how can I adapt it to take a criteria from
the underlying query.

Each set of addresses (16 addresses per set) within the query is based on a
Main Record field called [Run_No]. But when I try to use this criteria in the
query and then run your code, I get the following error message:

Run-time error '3061';
Too few parameters. Expected 1.

and the VBA throws a highlighted yellow error:

Set rsR = CurrentDb.OpenRecordset("Generate_KML", dbOpenSnapshot)

Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"

Is the expected string supposed to have two double quotes on either side of
UTF-8? If so, you need

Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

(The rule is everytime you want a quote to appear inside a quoted string,
you need to double the quotes.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Can you help advise on this VBA problem please?

When I paste or type this line:

Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"

I get

Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"

the 1.0 becomes 1#

I can't seem to get the VBA window to accept John's code, weird or some
kind
of switch that is turned off?

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


help?...

:

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...). The
.kml
file only contains the first address in the query, and I think it's
because
of my badly worded sentence 'I have an address query which ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What I
really
meant was it is a single field (among other fields in the query), but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that field
to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found' on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference, so I
was
worried
that the query it was calling was two seperate words, so I
changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and
footer
text
to be
inserted. Does anyone know how I can pre-insert the Header
and
Footer
into a
standard text file, and then save it with a '.kml' extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[Douglas J. Steele]

http://www.mvps.org/access/queries/qry0013.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas, John.

Now that I have the code working, how can I adapt it to take a criteria
from
the underlying query.

Each set of addresses (16 addresses per set) within the query is based on
a
Main Record field called [Run_No]. But when I try to use this criteria in
the
query and then run your code, I get the following error message:

Run-time error '3061';
Too few parameters. Expected 1.

and the VBA throws a highlighted yellow error:

Set rsR = CurrentDb.OpenRecordset("Generate_KML", dbOpenSnapshot)

Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"

Is the expected string supposed to have two double quotes on either side
of
UTF-8? If so, you need

Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

(The rule is everytime you want a quote to appear inside a quoted string,
you need to double the quotes.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Can you help advise on this VBA problem please?

When I paste or type this line:

Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"

I get

Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"

the 1.0 becomes 1#

I can't seem to get the VBA window to accept John's code, weird or some
kind
of switch that is turned off?



:

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


help?...

:

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...).
The
.kml
file only contains the first address in the query, and I think it's
because
of my badly worded sentence 'I have an address query which ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What
I
really
meant was it is a single field (among other fields in the query),
but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that
field
to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct
though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found'
on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference,
so I
was
worried
that the query it was calling was two seperate words, so I
changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to
text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and
footer
text
to be
inserted. Does anyone know how I can pre-insert the Header
and
Footer
into a
standard text file, and then save it with a '.kml'
extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>

Output Field: [kml Address]

This is the Footer:

</Folder>
</Document>
</kml>

 

[efandango]

Douglas,

thanks for that article, I understand what it is implying, but don't really
get how to implement the suggestions. It seems to me that it relates more to
forms controls than underlying query criteria. I tried this: (you'll probably
smile at my naivety...), but i can't figure out what or even how to
contextualise the code to reference my criteria field:

Set rsR = CurrentDb.OpenRecordset("Generate_KML", dbOpenSnapshot)
qdf![Generate_KML.Run_No] = [Run No]

but it didn't work.

Unlike that articles suggestion, I am not accessing a form's control, but a
criteria control with the criteria 'Run No' based on a field called [Run_No]

http://www.mvps.org/access/queries/qry0013.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas, John.

Now that I have the code working, how can I adapt it to take a criteria
from
the underlying query.

Each set of addresses (16 addresses per set) within the query is based on
a
Main Record field called [Run_No]. But when I try to use this criteria in
the
query and then run your code, I get the following error message:

Run-time error '3061';
Too few parameters. Expected 1.

and the VBA throws a highlighted yellow error:

Set rsR = CurrentDb.OpenRecordset("Generate_KML", dbOpenSnapshot)

Print #lngFN, "<?xml version=""1.0"" encoding=""UTF-8""?>"

Is the expected string supposed to have two double quotes on either side
of
UTF-8? If so, you need

Print #lngFN, "<?xml version=""1.0"" encoding=""""UTF-8""""?>"

(The rule is everytime you want a quote to appear inside a quoted string,
you need to double the quotes.)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

Can you help advise on this VBA problem please?

When I paste or type this line:

Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"

I get

Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"

the 1.0 becomes 1#

I can't seem to get the VBA window to accept John's code, weird or some
kind
of switch that is turned off?



:

Replace

Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

with

Set rsR = CurrentDb.Open("Generate_KML")
Do Until rsR.EOF = True
Print #lngFN, rsR.Fields("KML_Address")
rsR.MoveNext
Loop
Close rsR

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


help?...

:

Douglas/John,

that did the trick...

but I have a secondary problem (that i think is of my making...).
The
.kml
file only contains the first address in the query, and I think it's
because
of my badly worded sentence 'I have an address query which ouputs a
single
field to text strings for
Google Earth .kml file format parameters.' in my opening post. What
I
really
meant was it is a single field (among other fields in the query),
but
that it
contains multiple records/data.

eg:

Main St, London, W1
South St, London, W2
East St, London, SE1

Is it possible to get the routine to print All the data for that
field
to
the kml file?





:

That's supposed to be rsR.Close (the Close #lngFN is correct
though)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
John,

I now get this error in the line: Close rsR:
(highlighting the word 'Close')

Compile error:
Type mismatch:



:

My fault - though I warned you it was air code. Should be
CurrentDB.OpenRecordset("Generate_KML", dbOpenSnapshot)
not
CurrentDB.Open("Generate_KML")




On Thu, 10 May 2007 16:41:00 -0700, efandango

John,

Just ignore the previous two posts from me.

This is how far i have got in adapting your code

Private Sub Test_Click()
Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "W:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="; 1#; " encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
Print #lngFN, "<Document>"
Print #lngFN, "<name>Address List</name>"
Print #lngFN, "<Folder>"
Print #lngFN, "<name>Locations</name>"
Print #lngFN, "<open>1</open>"
'...

'Output address
Set rsR = CurrentDb.Open("Generate_KML")
Print #lngFN, rsR.Fields("KML_Address")
Close rsR

'Output footer
Print #lngFN, "</Folder>"
Print #lngFN, "</Document>"
Print #lngFN, "</kml>"
'...

Close #lngFN

End Sub


But i get an error message: 'Method or Data Member not found'
on
the
word
'Open' in the following line:

Set rsR = CurrentDb.Open("Generate_KML")

It suggests that something is mispelled or wrong reference,
so I
was
worried
that the query it was calling was two seperate words, so I
changed
it to
one
whole_word. But it still reports the same error message.

Can you help me with this?

:

There are many ways of skinning this cat. I'd probably do
something
like
this (air code):

Dim rsR As DAO.Recordset
Dim strLine As String
Dim lngFN As Long

'Create empty text file
lngFN = FreeFile()
Open "D:\Folder\Addresses.kml" For Output As #lngFN

'Output header
'NB: need to double quotes in literal strings
Print #lngFN, "<?xml version="1.0" encoding=""UTF-8""?>"
Print #lngFN, "<kml
xmlns=""http://earth.google.com/kml/2.0"">"
'...

'Output address
Set rsR = CurrentDB.Open("My Query")
Print #lngFN, rsR.Fields("kml Address")
Close rsR

'Output footer
Print #lngFN blah blah

Close #lngFN

On Thu, 10 May 2007 10:35:01 -0700, efandango

I have an address query which ouputs a single field to
text
strings
for
Google Earth .kml file format parameters.

The problem is that the .kml file requires a header and
footer
text
to be
inserted. Does anyone know how I can pre-insert the Header
and
Footer
into a
standard text file, and then save it with a '.kml'
extension,
for
example
'Addresses.kml'

This is the Header:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Address List</name>
<Folder>
<name>Locations</name>
<open>1</open>