Save Excel file to CSV with quotation mark data delimeter?



I exported an MSACCESS 2007 based query to Excel 2007 and then within excel
save to other format and save to CSV. The problem is that the CSV file is
saved without the quotation marks. I need this data delimeter. Does anyone
has any suggestions on how this can be done? Thanks much!


You can use the macro below. change MyPath and WritefileName as required.

Sub WriteCSV()
Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","
DQuote =
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

DQuote = chr(34)

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

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = DQuote & Cells(RowCount, ColCount) & DQuote
OutputLine = OutputLine & Delimiter & _
DQuote & Cells(RowCount, ColCount) & DQuote
End If
Next ColCount
OutputLine = OutputLine & ","
tswrite.writeline OutputLine
Next RowCount


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