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. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from
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) SELECT [Points with Run No].Run_No, [Points with Run No].Run_Point_Address_A AS Address, [Points with Run No].Run_Point_Venue_A AS Venue, "Placemarkdescription" & [Venue] & "/descriptionaddress" & [address] & ", London, UK /addressname" & [address] & "/name/Placemark" AS KML_Address FROM [Points with Run No] WHERE ((([Points with Run No].Run_No)=[Run No])); "Douglas J. Steele" wrote: Sorry, my fault. I switched what I was going to tell you part way through, and missed a correction. It should have been: qdf.Parameters("Run_No") = strRun_No (or you could use qdf![Run_No] = strRun_No) However, given the error you're getting, your parameter obviously isn't named "Run_No". What's the actual SQL of your query? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "efandango" wrote in message ... Douglas, I pasted your code (with subtle changes to the 'rs' references...: 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") = [Run_No] ' or qdf![Run_No] = [Run_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 When I run it, the dialog box for the user Run No pops up, but then I hit OK, i get this error message: Run-time error '3265': Item not found in this collection. and the vba highlights this line in the editor: qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No] I tried that line to just say: qdf![Run_No] = [Run_No] but still the same error message. "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 |
#32
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from
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) SELECT [Points with Run No].Run_No, [Points with Run No].Run_Point_Address_A AS Address, [Points with Run No].Run_Point_Venue_A AS Venue, "Placemarkdescription" & [Venue] & "/descriptionaddress" & [address] & ", London, UK /addressname" & [address] & "/name/Placemark" AS KML_Address FROM [Points with Run No] WHERE ((([Points with Run No].Run_No)=[Run No])); "Douglas J. Steele" wrote: Sorry, my fault. I switched what I was going to tell you part way through, and missed a correction. It should have been: qdf.Parameters("Run_No") = strRun_No (or you could use qdf![Run_No] = strRun_No) However, given the error you're getting, your parameter obviously isn't named "Run_No". What's the actual SQL of your query? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "efandango" wrote in message ... Douglas, I pasted your code (with subtle changes to the 'rs' references...: 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") = [Run_No] ' or qdf![Run_No] = [Run_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 When I run it, the dialog box for the user Run No pops up, but then I hit OK, i get this error message: Run-time error '3265': Item not found in this collection. and the vba highlights this line in the editor: qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No] I tried that line to just say: qdf![Run_No] = [Run_No] but still the same error message. "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 |
#33
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from
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) SELECT [Points with Run No].Run_No, [Points with Run No].Run_Point_Address_A AS Address, [Points with Run No].Run_Point_Venue_A AS Venue, "Placemarkdescription" & [Venue] & "/descriptionaddress" & [address] & ", London, UK /addressname" & [address] & "/name/Placemark" AS KML_Address FROM [Points with Run No] WHERE ((([Points with Run No].Run_No)=[Run No])); "Douglas J. Steele" wrote: Sorry, my fault. I switched what I was going to tell you part way through, and missed a correction. It should have been: qdf.Parameters("Run_No") = strRun_No (or you could use qdf![Run_No] = strRun_No) However, given the error you're getting, your parameter obviously isn't named "Run_No". What's the actual SQL of your query? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "efandango" wrote in message ... Douglas, I pasted your code (with subtle changes to the 'rs' references...: 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") = [Run_No] ' or qdf![Run_No] = [Run_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 When I run it, the dialog box for the user Run No pops up, but then I hit OK, i get this error message: Run-time error '3265': Item not found in this collection. and the vba highlights this line in the editor: qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No] I tried that line to just say: qdf![Run_No] = [Run_No] but still the same error message. "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 |
#34
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from a q
Another tweak?...
Does anyone know how I can get the 'Run no' based on the user input to appear inside the kml ouput field This is the current code line: Print #lngFN, "nameRun Points/name" & strRun_No Which gives this ouput in the KML file: nameRun Points/name67 The problem is that it puts the Run No (67) outside of the KML name title; but what I really need is for the Run No to be incorporated Inside the 'name' field, like this: nameRun Points 67/name "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 |
#35
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from
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) SELECT [Points with Run No].Run_No, [Points with Run No].Run_Point_Address_A AS Address, [Points with Run No].Run_Point_Venue_A AS Venue, "Placemarkdescription" & [Venue] & "/descriptionaddress" & [address] & ", London, UK /addressname" & [address] & "/name/Placemark" AS KML_Address FROM [Points with Run No] WHERE ((([Points with Run No].Run_No)=[Run No])); "Douglas J. Steele" wrote: Sorry, my fault. I switched what I was going to tell you part way through, and missed a correction. It should have been: qdf.Parameters("Run_No") = strRun_No (or you could use qdf![Run_No] = strRun_No) However, given the error you're getting, your parameter obviously isn't named "Run_No". What's the actual SQL of your query? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "efandango" wrote in message ... Douglas, I pasted your code (with subtle changes to the 'rs' references...: 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") = [Run_No] ' or qdf![Run_No] = [Run_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 When I run it, the dialog box for the user Run No pops up, but then I hit OK, i get this error message: Run-time error '3265': Item not found in this collection. and the vba highlights this line in the editor: qdf.Parameters("Run_No") = [Run_No] ' or qdf![Run_No] = [Run_No] I tried that line to just say: qdf![Run_No] = [Run_No] but still the same error message. "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 |
#36
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from a q
Print #lngFN, "nameRun Points " & strRun_No & "/name"
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "efandango" wrote in message ... Another tweak?... Does anyone know how I can get the 'Run no' based on the user input to appear inside the kml ouput field This is the current code line: Print #lngFN, "nameRun Points/name" & strRun_No Which gives this ouput in the KML file: nameRun Points/name67 The problem is that it puts the Run No (67) outside of the KML name title; but what I really need is for the Run No to be incorporated Inside the 'name' field, like this: nameRun Points 67/name "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 |
#37
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from
Douglas,
Thanks very much for this. where can I look to find out more about mixing user defined/ficked strings with variable data/fields. The Access help files are not very good for this kind of thing (not enough examples) "Douglas J. Steele" wrote: Print #lngFN, "nameRun Points " & strRun_No & "/name" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "efandango" wrote in message ... Another tweak?... Does anyone know how I can get the 'Run no' based on the user input to appear inside the kml ouput field This is the current code line: Print #lngFN, "nameRun Points/name" & strRun_No Which gives this ouput in the KML file: nameRun Points/name67 The problem is that it puts the Run No (67) outside of the KML name title; but what I really need is for the Run No to be incorporated Inside the 'name' field, like this: nameRun Points 67/name "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 |
#38
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from
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) SELECT [Points with Run No].Run_No, [Points with Run No].Run_Point_Address_A AS Address, [Points with Run No].Run_Point_Venue_A AS Venue, "Placemarkdescription" & [Venue] & "/descriptionaddress" & [address] & ", London, UK /addressname" & [address] & "/name/Placemark" AS KML_Address FROM [Points with Run No] WHERE ((([Points with Run No].Run_No)=[Run No])); "Douglas J. Steele" wrote: Sorry, my fault. I switched what I was going to tell you part way through, and missed a correction. It should have been: qdf.Parameters("Run_No") = strRun_No (or you could use qdf![Run_No] = strRun_No) However, given the error you're getting, your parameter obviously isn't named "Run_No". What's the actual SQL of your query? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "efandango" wrote in message ... Douglas, I pasted your code (with subtle changes to the 'rs' references...: 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") = [Run_No] ' or qdf![Run_No] = [Run_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" |
#39
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from
If I recall correctly, there's a chapter about this in the Access
Developer's Handbook. See http://www.developershandbook.com/ for details about this indispensible resource. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "efandango" wrote in message ... Douglas, Thanks very much for this. where can I look to find out more about mixing user defined/ficked strings with variable data/fields. The Access help files are not very good for this kind of thing (not enough examples) "Douglas J. Steele" wrote: Print #lngFN, "nameRun Points " & strRun_No & "/name" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "efandango" wrote in message ... Another tweak?... Does anyone know how I can get the 'Run no' based on the user input to appear inside the kml ouput field This is the current code line: Print #lngFN, "nameRun Points/name" & strRun_No Which gives this ouput in the KML file: nameRun Points/name67 The problem is that it puts the Run No (67) outside of the KML name title; but what I really need is for the Run No to be incorporated Inside the 'name' field, like this: nameRun Points 67/name "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 |
#40
|
|||
|
|||
Creating a custom text export file (inc. header & footer) from
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) SELECT [Points with Run No].Run_No, [Points with Run No].Run_Point_Address_A AS Address, [Points with Run No].Run_Point_Venue_A AS Venue, "Placemarkdescription" & [Venue] & "/descriptionaddress" & [address] & ", London, UK /addressname" & [address] & "/name/Placemark" AS KML_Address FROM [Points with Run No] WHERE ((([Points with Run No].Run_No)=[Run No])); "Douglas J. Steele" wrote: Sorry, my fault. I switched what I was going to tell you part way through, and missed a correction. It should have been: qdf.Parameters("Run_No") = strRun_No (or you could use qdf![Run_No] = strRun_No) However, given the error you're getting, your parameter obviously isn't named "Run_No". What's the actual SQL of your query? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "efandango" wrote in message ... Douglas, I pasted your code (with subtle changes to the 'rs' references...: 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") = [Run_No] ' or qdf![Run_No] = [Run_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" |
Thread Tools | |
Display Modes | |
|
|