Export text in ""

[Melbridge]

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?

 

[Melbridge]

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.

 

[Melbridge]

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!

 

[Melbridge]

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"

 

[Dave Peterson]

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]

Thanks Dave, that works perfectly.