Export text in ""


M

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?
 
Ad

Advertisements

M

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.
 
M

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!
 
M

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"
 
D

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
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top