A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #41  
Old May 14th, 2007, 09:48 PM posted to microsoft.public.access.externaldata,microsoft.public.access.reports,microsoft.public.access
efandango
external usenet poster
 
Posts: 489
Default Creating a custom text export file (inc. header & footer) from

I'm a little confused (and out of my depth), but are you saying that the data
will come out via a VBA parser different to what is actually held in the
query/table?. I'm not a seasoned coder and find the arcane synatx of xml
character encoding somewhat baffling. Regarding your suggestion that I should
replace "&" with "& for the KML/XML file, though I can understand that it
will allow for the '&' character to be viewed as '&' within google earth, it
would look odd in the MS Access form/table.

eg:
Cable & Wireless Building

Cable & Wireless Building


So for now, I have to settle for 'Cable and Wireless Building' which is not
an ideal state, but I don't know how to resolve it.




"Brendan Reynolds" wrote:


That's a separate issue, the & and characters have to be escaped because
they have special meaning to the parser. See the section "2.4 Character Data
and Markup" at the following URL ...

http://www.w3.org/TR/REC-xml/

In short, you need to replace "&" with "&" and "" with ""

The issue of encoding is described in the same article at section "4.3.3
Character Encoding in Entities"

I know, it's not an easy read, and I can't explain it all. I'm not an expert
on these issues, it's just something I had to muddle through as best I could
for a specific project that I worked on about two years ago. If you think
the code you have now will do what you need, that's great, but make sure to
test it with characters outside the standard ASCII range if there is any
possibility that such characters may ever be included in your real-world
data.

--
Brendan Reynolds

"efandango" wrote in message
...
Funny you should mention this Brendan, because I have been having problems
with certains characters in the KML file such as these two: ' &. I had to
change the ampersand (&) character to 'and' before google earth would
accept
them. I am not familiar with encoding formats, but assumed the KML was in
UTF-8 encoding format. Here's a link to googles guide on KML where you can
see that they use this type of encoding in their header file.

http://code.google.com/apis/kml/docu...n/kml_tut.html

Below is my resultant KML file with adjusted characterss which works fine
in
Google Earth, though it would be much better if it would take the & type
characters:

My KML example:

?xml version="1.0" encoding="UTF-8"?
kml xmlns="http://earth.google.com/kml/2.0"
Document
nameRun Points 5/name
Folder
nameLocations/name
open1/open
PlacemarkdescriptionAscot Hotel/descriptionaddress11 Craven Road,
W2, London, UK /addressname11 Craven Road, W2, London,
UK/name/Placemark
/Folder
/Document
/kml

"Brendan Reynolds" wrote:


I'm glad you're making progress. I think you'll find, though, that the
resulting file is not, in fact, in UTF-8 encoding. You may find that this
causes XML parsers to reject the file when it contains characters outside
the standard ASCII range.

I'm not aware of any way to specify the encoding to be used when using
VBA
IO commands. But you can probably avoid problems by changing the
declaration
to match the actual encoding being used, rather than trying to change the
encoding to match the declaration.

On my system, when using the VBA IO commands, the resulting file is in
ISO-8859-1 encoding, so my declaration looks like this ...

Print #intFile, "?xml version='1.0' encoding ='ISO-8859-1'?"

I don't know whether VBA always uses this encoding, or whether it may
vary
between OS versions or language versions.

If I remember correctly, I believe I used a utility or sample code that I
found on the 'net to determine the actual encoding that VBA was using.
I'm
afraid, though, that it was some time ago, and I can't remember the
details.

If you really need to specify an encoding other than the one that VBA
uses
by default, I believe you can probably do that using Microsoft's XML
parser,
MSXML.DLL. I haven't actually done this, so I can't provide details, but
if
you want to look into it try searching on 'msxml' at MSDN
(http://msdn.microsoft.com)

--
Brendan Reynolds

"efandango" wrote in message
...
Brendan,

That worked.

It seems that while making changes to the code after Douglas's helpful
input, I missed out some of John's essential code snippets. Following
your
suggestion, I went back to John's code, and incorporated them into
Douglas's
latter code suggestions, and it all worked in the end. So a HUGE thank
you
to
John, Douglas and your good self for your help and guidance on this
problem.

For the record, here is the succesful code that will allow the user to
choose a Run Number for a set of addresses and run it out to a Google
Earth
KML file.

Cheers Fellas.

Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No 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.0"" encoding=""UTF-8""?"
'Print #lngFN, "?xml version=""1.0"" encoding=""""UTF-8""""?"
Print #lngFN, "kml xmlns=""http://earth.google.com/kml/2.0"""
Print #lngFN, "Document"
Print #lngFN, "nameAddress List/name"
Print #lngFN, "Folder"
Print #lngFN, "nameLocations/name"
Print #lngFN, "open1/open"

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close

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

Close #lngFN
End If
End Sub

"Brendan Reynolds" wrote:


The error message is, I believe, complaining about the value of the
variable
'lngFN'. I don't see, in the posted code, where this variable is
declared,
or where a value is assigned to it, or where the file gets opened for
output?

In other words, unless this is a global variable that is declared and
initialized elsewhere, the code seems to be missing something like
this
...

Dim lngFN As Long

lngFN = FreeFile
Open "C:\SomeFolder\SomeFile.txt" For Output As #lngFN

You'll also want to close the file when you've finished writing to it
...

Close #lngFN

--
Brendan Reynolds

"efandango" wrote in message
...
Sorry, I forgot to add that this is the line that now highlight
errors
with
''Bad file name or number (runtime error 52)

Print #lngFN, rs.Fields("KML_Address")

"efandango" wrote:

Douglas

I noticed your 2nd option 'qdf![Run No] = strRun _No'
had a space before the underscore '_No'

but it still comes back with the same error line ('Bad file name or
number
(runtime error 52) as your first option


Here is my Current Code:


Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


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

Close #lngFN
End If
End Sub


"efandango" wrote:

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the VBA
window



"Douglas J. Steele" wrote:

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"efandango" wrote in
message
...
Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the
underscore
for
the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)

  #42  
Old May 15th, 2007, 12:12 AM posted to microsoft.public.access.externaldata,microsoft.public.access.reports,microsoft.public.access
Brendan Reynolds
external usenet poster
 
Posts: 1,241
Default Creating a custom text export file (inc. header & footer) from


As I said, I'm not an XML expert. I can't explain in any great detail or
with any very high level of technical accuracy. Please consider everything
that follows to be prefaced with the words "roughly speaking" and "as I
understand it".

First, when I said 'parser' I was talking about an XML parser, not a VBA
parser. The issue is that your XML declaration (?xml version="1.0"
encoding="UTF-8"?) informs XML parsers that the file is encoded using UTF-8
encoding, when in fact it is not. This isn't a problem as long as the data
contains only characters in the standard ASCII range, but becomes an issue
if the data contains any characters that are not in the standard ASCII
range. When that happens the XML parser will attempt to interpret those
characters as UTF-8 characters and will fail.

As for escaping the "&" and "" characters, there is no need to do that
permanently within the stored data, you can do it when creating the XML. For
example, instead of ...

Print #lngFN, rs.Fields("KML_Address")

.... you could do something like the followng ...

Dim strWork As String

strWork = rs.Fields("KML_Address")
strWork = Replace(strWork, "&", "&")
strWork = Replace(strWork, "", "<")
Print #lngFN, strWork

BTW: I notice in the quoted text from my previous message the escaped
characters have been changed back to "&" and "". Probably the result of my
message being converted to HTML by Microsoft's web-based interface, I guess.
As an illustration here's the same code with spaces inserted between the
characters of the escape strings. The spaces should not form part of the
actual code, they're there just in the hope that they will prevent the
strings being converted by the web-based interface.

strWork = rs.Fields("KML_Address")
strWork = Replace(strWork, "&", "& a m p ;")
strWork = Replace(strWork, "", "& l t ;")
Print #lngFN, strWork

--
Brendan Reynolds

"efandango" wrote in message
...
I'm a little confused (and out of my depth), but are you saying that the
data
will come out via a VBA parser different to what is actually held in the
query/table?. I'm not a seasoned coder and find the arcane synatx of xml
character encoding somewhat baffling. Regarding your suggestion that I
should
replace "&" with "& for the KML/XML file, though I can understand that it
will allow for the '&' character to be viewed as '&' within google earth,
it
would look odd in the MS Access form/table.

eg:
Cable & Wireless Building

Cable & Wireless Building


So for now, I have to settle for 'Cable and Wireless Building' which is
not
an ideal state, but I don't know how to resolve it.




"Brendan Reynolds" wrote:


That's a separate issue, the & and characters have to be escaped
because
they have special meaning to the parser. See the section "2.4 Character
Data
and Markup" at the following URL ...

http://www.w3.org/TR/REC-xml/

In short, you need to replace "&" with "&" and "" with ""

The issue of encoding is described in the same article at section "4.3.3
Character Encoding in Entities"

I know, it's not an easy read, and I can't explain it all. I'm not an
expert
on these issues, it's just something I had to muddle through as best I
could
for a specific project that I worked on about two years ago. If you think
the code you have now will do what you need, that's great, but make sure
to
test it with characters outside the standard ASCII range if there is any
possibility that such characters may ever be included in your real-world
data.

--
Brendan Reynolds

"efandango" wrote in message
...
Funny you should mention this Brendan, because I have been having
problems
with certains characters in the KML file such as these two: ' &. I had
to
change the ampersand (&) character to 'and' before google earth would
accept
them. I am not familiar with encoding formats, but assumed the KML was
in
UTF-8 encoding format. Here's a link to googles guide on KML where you
can
see that they use this type of encoding in their header file.

http://code.google.com/apis/kml/docu...n/kml_tut.html

Below is my resultant KML file with adjusted characterss which works
fine
in
Google Earth, though it would be much better if it would take the &
type
characters:

My KML example:

?xml version="1.0" encoding="UTF-8"?
kml xmlns="http://earth.google.com/kml/2.0"
Document
nameRun Points 5/name
Folder
nameLocations/name
open1/open
PlacemarkdescriptionAscot Hotel/descriptionaddress11 Craven
Road,
W2, London, UK /addressname11 Craven Road, W2, London,
UK/name/Placemark
/Folder
/Document
/kml

"Brendan Reynolds" wrote:


I'm glad you're making progress. I think you'll find, though, that the
resulting file is not, in fact, in UTF-8 encoding. You may find that
this
causes XML parsers to reject the file when it contains characters
outside
the standard ASCII range.

I'm not aware of any way to specify the encoding to be used when using
VBA
IO commands. But you can probably avoid problems by changing the
declaration
to match the actual encoding being used, rather than trying to change
the
encoding to match the declaration.

On my system, when using the VBA IO commands, the resulting file is in
ISO-8859-1 encoding, so my declaration looks like this ...

Print #intFile, "?xml version='1.0' encoding ='ISO-8859-1'?"

I don't know whether VBA always uses this encoding, or whether it may
vary
between OS versions or language versions.

If I remember correctly, I believe I used a utility or sample code
that I
found on the 'net to determine the actual encoding that VBA was using.
I'm
afraid, though, that it was some time ago, and I can't remember the
details.

If you really need to specify an encoding other than the one that VBA
uses
by default, I believe you can probably do that using Microsoft's XML
parser,
MSXML.DLL. I haven't actually done this, so I can't provide details,
but
if
you want to look into it try searching on 'msxml' at MSDN
(http://msdn.microsoft.com)

--
Brendan Reynolds

"efandango" wrote in message
...
Brendan,

That worked.

It seems that while making changes to the code after Douglas's
helpful
input, I missed out some of John's essential code snippets.
Following
your
suggestion, I went back to John's code, and incorporated them into
Douglas's
latter code suggestions, and it all worked in the end. So a HUGE
thank
you
to
John, Douglas and your good self for your help and guidance on this
problem.

For the record, here is the succesful code that will allow the user
to
choose a Run Number for a set of addresses and run it out to a
Google
Earth
KML file.

Cheers Fellas.

Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No 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.0"" encoding=""UTF-8""?"
'Print #lngFN, "?xml version=""1.0"" encoding=""""UTF-8""""?"
Print #lngFN, "kml xmlns=""http://earth.google.com/kml/2.0"""
Print #lngFN, "Document"
Print #lngFN, "nameAddress List/name"
Print #lngFN, "Folder"
Print #lngFN, "nameLocations/name"
Print #lngFN, "open1/open"

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close

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

Close #lngFN
End If
End Sub

"Brendan Reynolds" wrote:


The error message is, I believe, complaining about the value of the
variable
'lngFN'. I don't see, in the posted code, where this variable is
declared,
or where a value is assigned to it, or where the file gets opened
for
output?

In other words, unless this is a global variable that is declared
and
initialized elsewhere, the code seems to be missing something like
this
...

Dim lngFN As Long

lngFN = FreeFile
Open "C:\SomeFolder\SomeFile.txt" For Output As #lngFN

You'll also want to close the file when you've finished writing to
it
...

Close #lngFN

--
Brendan Reynolds

"efandango" wrote in message
...
Sorry, I forgot to add that this is the line that now highlight
errors
with
''Bad file name or number (runtime error 52)

Print #lngFN, rs.Fields("KML_Address")

"efandango" wrote:

Douglas

I noticed your 2nd option 'qdf![Run No] = strRun _No'
had a space before the underscore '_No'

but it still comes back with the same error line ('Bad file name
or
number
(runtime error 52) as your first option


Here is my Current Code:


Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close


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

Close #lngFN
End If
End Sub


"efandango" wrote:

Douglas,

this line: qdf.Parameters("Run No") = strRun_No

comes back with 'Bad file name or number (runtime error 52)

your other line 'qdf![Run No] = strRun _No' redlines in the
VBA
window



"Douglas J. Steele" wrote:

Since your parameter doesn't have an underscore in it, try:

qdf.Parameters("Run No") = strRun_No

or

qdf![Run No] = strRun _No

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"efandango" wrote in
message
...
Douglas,

I tried both lines (seperately), but neither worked.

they both throw the following error:

'Item not found in this collection'

I am using 'Run No' as my parameter name without the
underscore
for
the
actual field, [Run_No] with the underscore.

Here is my SQL (with the parameter request)




  #43  
Old May 15th, 2007, 10:51 AM posted to microsoft.public.access.externaldata,microsoft.public.access.reports,microsoft.public.access
efandango
external usenet poster
 
Posts: 489
Default Creating a custom text export file (inc. header & footer) from

Brendan

That worked a treat. I can now see, Cable & Wireless Building in my google
maps address balloon. Though I did not need the code snippet: strWork =
Replace(strWork, "", "") I'm not sure why that particular character came
into the situation?, but if I leave it in and examine the KML file, I get
this kind of line.

PlacemarkdescriptionCable & Wireless Building/descriptionaddress120
Theobalds Road, WC1, London, UK /addressname120 Theobalds Road, WC1,
London, UK/name/Placemark

Which for some reason, loads into Google Earth ok, but fails to display any
data. Though if I rem that particular code line out, all is well within the
KML.

seems odd that the google earth parser understands the result of:

strWork = Replace(strWork, "&", "&")

but not,

strWork = Replace(strWork, "", "")

but as they say, all's well that end's well...

appreciate your help, thanks.







"Brendan Reynolds" wrote:


As I said, I'm not an XML expert. I can't explain in any great detail or
with any very high level of technical accuracy. Please consider everything
that follows to be prefaced with the words "roughly speaking" and "as I
understand it".

First, when I said 'parser' I was talking about an XML parser, not a VBA
parser. The issue is that your XML declaration (?xml version="1.0"
encoding="UTF-8"?) informs XML parsers that the file is encoded using UTF-8
encoding, when in fact it is not. This isn't a problem as long as the data
contains only characters in the standard ASCII range, but becomes an issue
if the data contains any characters that are not in the standard ASCII
range. When that happens the XML parser will attempt to interpret those
characters as UTF-8 characters and will fail.

As for escaping the "&" and "" characters, there is no need to do that
permanently within the stored data, you can do it when creating the XML. For
example, instead of ...

Print #lngFN, rs.Fields("KML_Address")

... you could do something like the followng ...

Dim strWork As String

strWork = rs.Fields("KML_Address")
strWork = Replace(strWork, "&", "&")
strWork = Replace(strWork, "", "")
Print #lngFN, strWork

BTW: I notice in the quoted text from my previous message the escaped
characters have been changed back to "&" and "". Probably the result of my
message being converted to HTML by Microsoft's web-based interface, I guess.
As an illustration here's the same code with spaces inserted between the
characters of the escape strings. The spaces should not form part of the
actual code, they're there just in the hope that they will prevent the
strings being converted by the web-based interface.

strWork = rs.Fields("KML_Address")
strWork = Replace(strWork, "&", "& a m p ;")
strWork = Replace(strWork, "", "& l t ;")
Print #lngFN, strWork

--
Brendan Reynolds

"efandango" wrote in message
...
I'm a little confused (and out of my depth), but are you saying that the
data
will come out via a VBA parser different to what is actually held in the
query/table?. I'm not a seasoned coder and find the arcane synatx of xml
character encoding somewhat baffling. Regarding your suggestion that I
should
replace "&" with "& for the KML/XML file, though I can understand that it
will allow for the '&' character to be viewed as '&' within google earth,
it
would look odd in the MS Access form/table.

eg:
Cable & Wireless Building

Cable & Wireless Building


So for now, I have to settle for 'Cable and Wireless Building' which is
not
an ideal state, but I don't know how to resolve it.




"Brendan Reynolds" wrote:


That's a separate issue, the & and characters have to be escaped
because
they have special meaning to the parser. See the section "2.4 Character
Data
and Markup" at the following URL ...

http://www.w3.org/TR/REC-xml/

In short, you need to replace "&" with "&" and "" with ""

The issue of encoding is described in the same article at section "4.3.3
Character Encoding in Entities"

I know, it's not an easy read, and I can't explain it all. I'm not an
expert
on these issues, it's just something I had to muddle through as best I
could
for a specific project that I worked on about two years ago. If you think
the code you have now will do what you need, that's great, but make sure
to
test it with characters outside the standard ASCII range if there is any
possibility that such characters may ever be included in your real-world
data.

--
Brendan Reynolds

"efandango" wrote in message
...
Funny you should mention this Brendan, because I have been having
problems
with certains characters in the KML file such as these two: ' &. I had
to
change the ampersand (&) character to 'and' before google earth would
accept
them. I am not familiar with encoding formats, but assumed the KML was
in
UTF-8 encoding format. Here's a link to googles guide on KML where you
can
see that they use this type of encoding in their header file.

http://code.google.com/apis/kml/docu...n/kml_tut.html

Below is my resultant KML file with adjusted characterss which works
fine
in
Google Earth, though it would be much better if it would take the &
type
characters:

My KML example:

?xml version="1.0" encoding="UTF-8"?
kml xmlns="http://earth.google.com/kml/2.0"
Document
nameRun Points 5/name
Folder
nameLocations/name
open1/open
PlacemarkdescriptionAscot Hotel/descriptionaddress11 Craven
Road,
W2, London, UK /addressname11 Craven Road, W2, London,
UK/name/Placemark
/Folder
/Document
/kml

"Brendan Reynolds" wrote:


I'm glad you're making progress. I think you'll find, though, that the
resulting file is not, in fact, in UTF-8 encoding. You may find that
this
causes XML parsers to reject the file when it contains characters
outside
the standard ASCII range.

I'm not aware of any way to specify the encoding to be used when using
VBA
IO commands. But you can probably avoid problems by changing the
declaration
to match the actual encoding being used, rather than trying to change
the
encoding to match the declaration.

On my system, when using the VBA IO commands, the resulting file is in
ISO-8859-1 encoding, so my declaration looks like this ...

Print #intFile, "?xml version='1.0' encoding ='ISO-8859-1'?"

I don't know whether VBA always uses this encoding, or whether it may
vary
between OS versions or language versions.

If I remember correctly, I believe I used a utility or sample code
that I
found on the 'net to determine the actual encoding that VBA was using.
I'm
afraid, though, that it was some time ago, and I can't remember the
details.

If you really need to specify an encoding other than the one that VBA
uses
by default, I believe you can probably do that using Microsoft's XML
parser,
MSXML.DLL. I haven't actually done this, so I can't provide details,
but
if
you want to look into it try searching on 'msxml' at MSDN
(http://msdn.microsoft.com)

--
Brendan Reynolds

"efandango" wrote in message
...
Brendan,

That worked.

It seems that while making changes to the code after Douglas's
helpful
input, I missed out some of John's essential code snippets.
Following
your
suggestion, I went back to John's code, and incorporated them into
Douglas's
latter code suggestions, and it all worked in the end. So a HUGE
thank
you
to
John, Douglas and your good self for your help and guidance on this
problem.

For the record, here is the succesful code that will allow the user
to
choose a Run Number for a set of addresses and run it out to a
Google
Earth
KML file.

Cheers Fellas.

Private Sub Test_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No 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.0"" encoding=""UTF-8""?"
'Print #lngFN, "?xml version=""1.0"" encoding=""""UTF-8""""?"
Print #lngFN, "kml xmlns=""http://earth.google.com/kml/2.0"""
Print #lngFN, "Document"
Print #lngFN, "nameAddress List/name"
Print #lngFN, "Folder"
Print #lngFN, "nameLocations/name"
Print #lngFN, "open1/open"

strRun_No = InputBox("Enter the Run No")
If Len(strRun_No) 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML")
qdf.Parameters("Run No") = strRun_No
qdf![Run No] = strRun_No

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Do Until rs.EOF = True
Print #lngFN, rs.Fields("KML_Address")
rs.MoveNext
Loop
rs.Close

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

Close #lngFN
End If
End Sub

"Brendan Reynolds" wrote:


The error message is, I believe, complaining about the value of the
variable
'lngFN'. I don't see, in the posted code, where this variable is
declared,
or where a value is assigned to it, or where the file gets opened
for
output?

In other words, unless this is a global variable that is declared
and
initialized elsewhere, the code seems to be missing something like
this
...

Dim lngFN As Long

lngFN = FreeFile
Open "C:\SomeFolder\SomeFile.txt" For Output As #lngFN

You'll also want to close the file when you've finished writing to
it
...

Close #lngFN

--
Brendan Reynolds

"efandango" wrote in message
...
Sorry, I forgot to add that this is the line that now highlight
errors
with
''Bad file name or number (runtime error 52)

  #44  
Old December 11th, 2007, 08:57 PM posted to microsoft.public.access.externaldata,microsoft.public.access.reports,microsoft.public.access
KP
external usenet poster
 
Posts: 82
Default Creating a custom text export file (inc. header & footer) from a q

Hi "efandango"

I am doing something very similar - would you be willing to share your
query/export specification and the module you used to generate the KLM file?
It would save me a lot of time!
Thanks for considering,


"efandango" wrote:

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
nameAddress List/name
Folder
nameLocations/name
open1/open

Output Field: [kml Address]

This is the Footer:

/Folder
/Document
/kml

  #45  
Old December 12th, 2007, 03:34 PM posted to microsoft.public.access.externaldata,microsoft.public.access.reports,microsoft.public.access
Roger Carlson
external usenet poster
 
Posts: 824
Default Creating a custom text export file (inc. header & footer) from a q

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ExportFormattedFixed.MDB" which illustrates how print a
header for an exported record. Adding a footer would be similar. I'm not
sure of the specifics of you problem, so I can't tell you how to modify the
code to match. However, if you have any questions, just let me know.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"KP" wrote in message
...
Hi "efandango"

I am doing something very similar - would you be willing to share your
query/export specification and the module you used to generate the KLM
file?
It would save me a lot of time!
Thanks for considering,


"efandango" wrote:

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
nameAddress List/name
Folder
nameLocations/name
open1/open

Output Field: [kml Address]

This is the Footer:

/Folder
/Document
/kml



  #47  
Old December 12th, 2007, 08:29 PM posted to microsoft.public.access.externaldata,microsoft.public.access.reports,microsoft.public.access
KP
external usenet poster
 
Posts: 82
Default Creating a custom text export file (inc. header & footer) from

My apologies for the double post....I'll have a look at your website Roger.
Thanks for sharing.

"KP" wrote:

Hi "efandango,"
I'm doing some similar work - would you be willing to share the SQL query
and VB script you used to create the KML file? It would save me a lot of
time...
Thanks for considering.


"efandango" wrote:

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
nameAddress List/name
Folder
nameLocations/name
open1/open

Output Field: [kml Address]

This is the Footer:

/Folder
/Document
/kml

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.