Save Excel file to CSV with quotation mark data delimeter?

[Eric]

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!

 

[Joel]

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
Else
OutputLine = OutputLine & Delimiter & _
DQuote & Cells(RowCount, ColCount) & DQuote
End If
Next ColCount
OutputLine = OutputLine & ","
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub

 

[Eric]

Thanks much, it works juts fine - that is exactly what I needed.