Sat 3 Jan 2009
Microsoft Excel has limited or no options regarding the export to csv feature. For example, you can not select that exported values will be included inside double quotes.
Here is a script that will do the job:
Sub subExportCSV()
On Error GoTo subexport_exit
Dim strDelimiter As String
strDelimiter = Chr(9)
Dim strQualifier As String
strQualifier = “”"”
Dim arrRng
arrRng = ActiveSheet.UsedRange.Value
Dim f As String
Dim i As Long
Dim j As Long
Dim strTemp As String
f = InputBox(”Enter a filename for saving”, , “c:\test.csv”)
If Trim(f) = “” Then Exit Sub
Open f For Output As #1
strTemp = “”
For i = 1 To UBound(arrRng, 1)
strTemp = “”
For j = 1 To UBound(arrRng, 2)
strTemp = strTemp & strQualifier & arrRng(i, j) & strQualifier & strDelimiter
Next j
Print #1, Left(strTemp, Len(strTemp) – Len(strDelimiter))
Next i
subexport_exit:
Close #1
End Sub
December 17th, 2009 at 11:26 am
[...] have a look! http://www.pctechblog.com/software/excel/exel-export-to-csv-double-quotes Last 5 posts in Knowledge BaseQuick script to export Magento categories with IDsSpeeding up the [...]
January 5th, 2010 at 9:16 am
Just tried to run this script and I received a syntax error for this line:
strQualifier = “”””
January 5th, 2010 at 9:21 am
Discovered this works without errors:
Sub CSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename(”", “CSV File (*.csv), *.csv”)
If FName False Then
ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = “”
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & “”"” & CurrCell.Value & “”"” & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) – 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub