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

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

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

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

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

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
.