Export text in ""
I want export (as a csv file) some data to create a TomTom POI file.
I have Latitude, Longitude, Name I need to export the Name column in " " eg: 53.5,-0.8,"Name of location" Rather than: 53.5,-0.8,Name of location Any way to do this? -- Regards, Dean |
Export text in ""
Thought I'd worked this out but it didn't work :-(
Applied a custom number format to the column of \"@\" This displays " " round the text but doesn't export them to the .csv file. -- Regards, Dean "Melbridge" wrote: I want export (as a csv file) some data to create a TomTom POI file. I have Latitude, Longitude, Name I need to export the Name column in " " eg: 53.5,-0.8,"Name of location" Rather than: 53.5,-0.8,Name of location Any way to do this? -- Regards, Dean |
Export text in ""
Tried this again and it sort of works but it is exporting tripple " round the
text! eg I want 004 to be exported as "004" but it is being exported as """004""" Very confusing! -- Regards, Dean "Melbridge" wrote: Thought I'd worked this out but it didn't work :-( Applied a custom number format to the column of \"@\" This displays " " round the text but doesn't export them to the .csv file. -- Regards, Dean "Melbridge" wrote: I want export (as a csv file) some data to create a TomTom POI file. I have Latitude, Longitude, Name I need to export the Name column in " " eg: 53.5,-0.8,"Name of location" Rather than: 53.5,-0.8,Name of location Any way to do this? -- Regards, Dean |
Export text in ""
Found this code here http://www.mcgimpsey.com/excel/textfiles.html
Public Sub OutputQuotedCSV() Const QSTR As String = """" Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "File1.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, 256).End(xlToLeft)) sOut = sOut & "," & QSTR & _ Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum End Sub However this exports all the data in quotes. Can this be changed so it only exports column C in quotes and not columns A and B eg: 53.5,-0.8,"004" -- Regards, Dean "Melbridge" wrote: Tried this again and it sort of works but it is exporting tripple " round the text! eg I want 004 to be exported as "004" but it is being exported as """004""" Very confusing! -- Regards, Dean "Melbridge" wrote: Thought I'd worked this out but it didn't work :-( Applied a custom number format to the column of \"@\" This displays " " round the text but doesn't export them to the .csv file. -- Regards, Dean "Melbridge" wrote: I want export (as a csv file) some data to create a TomTom POI file. I have Latitude, Longitude, Name I need to export the Name column in " " eg: 53.5,-0.8,"Name of location" Rather than: 53.5,-0.8,Name of location Any way to do this? -- Regards, Dean |
Export text in ""
JE's code makes sure that fields that contain double quotes are fixed.
It looks like you don't care if that's included. If that's true... Option Explicit Public Sub OutputQuotedCSV() Const QSTR As String = """" Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "File1.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row).Cells With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, 256).End(xlToLeft)) If myField.Column = 3 Then 'include the quote: sOut = sOut & "," & QSTR & myField.Text & QSTR Else 'omit the quote sOut = sOut & "," & myField.Text End If Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum End Sub Melbridge wrote: Found this code here http://www.mcgimpsey.com/excel/textfiles.html Public Sub OutputQuotedCSV() Const QSTR As String = """" Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "File1.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, 256).End(xlToLeft)) sOut = sOut & "," & QSTR & _ Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum End Sub However this exports all the data in quotes. Can this be changed so it only exports column C in quotes and not columns A and B eg: 53.5,-0.8,"004" -- Regards, Dean "Melbridge" wrote: Tried this again and it sort of works but it is exporting tripple " round the text! eg I want 004 to be exported as "004" but it is being exported as """004""" Very confusing! -- Regards, Dean "Melbridge" wrote: Thought I'd worked this out but it didn't work :-( Applied a custom number format to the column of \"@\" This displays " " round the text but doesn't export them to the .csv file. -- Regards, Dean "Melbridge" wrote: I want export (as a csv file) some data to create a TomTom POI file. I have Latitude, Longitude, Name I need to export the Name column in " " eg: 53.5,-0.8,"Name of location" Rather than: 53.5,-0.8,Name of location Any way to do this? -- Regards, Dean -- Dave Peterson |
Export text in ""
Thanks Dave, that works perfectly.
-- Regards, Dean "Dave Peterson" wrote: JE's code makes sure that fields that contain double quotes are fixed. It looks like you don't care if that's included. If that's true... Option Explicit Public Sub OutputQuotedCSV() Const QSTR As String = """" Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "File1.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row).Cells With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, 256).End(xlToLeft)) If myField.Column = 3 Then 'include the quote: sOut = sOut & "," & QSTR & myField.Text & QSTR Else 'omit the quote sOut = sOut & "," & myField.Text End If Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum End Sub Melbridge wrote: Found this code here http://www.mcgimpsey.com/excel/textfiles.html Public Sub OutputQuotedCSV() Const QSTR As String = """" Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "File1.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, 256).End(xlToLeft)) sOut = sOut & "," & QSTR & _ Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum End Sub However this exports all the data in quotes. Can this be changed so it only exports column C in quotes and not columns A and B eg: 53.5,-0.8,"004" -- Regards, Dean "Melbridge" wrote: Tried this again and it sort of works but it is exporting tripple " round the text! eg I want 004 to be exported as "004" but it is being exported as """004""" Very confusing! -- Regards, Dean "Melbridge" wrote: Thought I'd worked this out but it didn't work :-( Applied a custom number format to the column of \"@\" This displays " " round the text but doesn't export them to the .csv file. -- Regards, Dean "Melbridge" wrote: I want export (as a csv file) some data to create a TomTom POI file. I have Latitude, Longitude, Name I need to export the Name column in " " eg: 53.5,-0.8,"Name of location" Rather than: 53.5,-0.8,Name of location Any way to do this? -- Regards, Dean -- Dave Peterson . |
All times are GMT +1. The time now is 08:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com