excel save as csv quotation mark



Hi, I am trying to save a single worksheet from .xls to .csv format. The
worksheet contains dates, strings and numbers.

I would like to open the .csv file in wordpad and have quotation around
the strings. However, the trouble arises when I saved the .xls to .csv
1.without using quotations (before saving) would produce a results of no
2.with quotations around the strings (before saving) would produce a result
of triple quotation marks.

My eventual goal is to simply have a single quotation mark around the
string, and dates (not the numbers). What can I do?

Thank you for your time,


The secret is to use CHR(34) in place of the double quotes. try this code.
Youprobabbly havve to adjust the range of cells you want to send. This code
is using the activecell current region which end at blank rows and blank

Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

WriteFileName = "quotetext.csv"

'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

MyRows = ActiveCell.CurrentRegion.Rows.Count
Set RowRange = Range(Cells(1, "A"), Cells(MyRows, "A"))

For Each rowcell In RowRange

Lastcol = Cells(rowcell.Row, Columns.Count).End(xlToLeft).Column
ColRange = Range(Cells(rowcell.Row, "A"), Cells(rowcell.Row, Lastcol))
Outline = ""
For Each colcell In ColRange
If Len(Outputline) = 0 Then
Outputline = CStr(colcell)
Outputline = Outputline & "," & CStr(colcell)
End If
Next colcell

tswrite.writeline Chr(34) & Outputline & Chr(34)
Next rowcell

Exit Sub
End Sub

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