excel save as csv quotation mark

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
format,
1.without using quotations (before saving) would produce a results of no
quotations
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,
Brian
 
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
columns.


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)
Else
Outputline = Outputline & "," & CStr(colcell)
End If
Next colcell

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

Exit Sub
End Sub
 
Back
Top