Preserve formatting when saving xls as csv

Question

.SaveAs fileName:="name" & ".CSV", FileFormat:=xlCSV

The above is what i am using to save an excel as csv. however, in the process i lose all my formatting. For example, decimals becomes whole numbers etc.. How can i preserve formatting when writing the above code in excel vba.

CSV is a text format. You can't have a formatted CSV file. Either you want a formatted file, or you want a *Comma-Separated Values* text file. — Mathieu Guindon, May 04 '16 at 19:21
To preserve the formatting you must save it as a .xls type file (.xls, .xlsx, .xlsm, etc). — tigeravatar, May 04 '16 at 19:32
There are many examples on this site of exporting the contents of worksheet to a .csv without use .SaveAs. — OldUgly, May 05 '16 at 05:23
I strongly suspect that your regional settings may be using a comma for a decimal place and possibly even a period for a thousands separator. If this is the case then the CSV creation is working exactly as it was designed to and you will have to make adjustments with hte correct code page. — , May 06 '16 at 17:46

score 0 · Answer 1 · edited May 23 '17 at 12:07

See this or this or this for examples of writing a .csv file instead of saving to one.

Below is code extracted from the first example ...

Sub makeCSV(theSheet As Worksheet)
Dim iFile As Long, myPath As String
Dim myArr() As Variant, outStr As String
Dim iLoop As Long, jLoop As Long

myPath = Application.ActiveWorkbook.Path
iFile = FreeFile
Open myPath & "\myCSV.csv" For Output Lock Write As #iFile

myArr = theSheet.UsedRange
For iLoop = LBound(myArr, 1) To UBound(myArr, 1)
    outStr = ""
    For jLoop = LBound(myArr, 2) To UBound(myArr, 2) - 1
        If InStr(1, myArr(iLoop, jLoop), ",") Then
            outStr = outStr & """" & myArr(iLoop, jLoop) & """" & ","
        Else
            outStr = outStr & myArr(iLoop, jLoop) & ","
        End If
    Next jLoop
    If InStr(1, myArr(iLoop, jLoop), ",") Then
        outStr = outStr & """" & myArr(iLoop, UBound(myArr, 2)) & """"
    Else
        outStr = outStr & myArr(iLoop, UBound(myArr, 2))
    End If
    Print #iFile, outStr
Next iLoop

Close iFile
Erase myArr

End Sub

modified to deal with unusual shaped ranges

Sub makeCSV(theSheet As Worksheet)
Dim iFile As Long, myPath As String
Dim myArr() As Variant, outStr As String
Dim iLoop As Long, jLoop As Long
Dim lastCol As Long

' set up output file
myPath = Application.ActiveWorkbook.Path
iFile = FreeFile
Open myPath & "\myCSV.csv" For Output Lock Write As #iFile

myArr = theSheet.UsedRange
For iLoop = LBound(myArr, 1) To UBound(myArr, 1) ' loop through all rows
    outStr = ""
    lastCol = UBound(myArr, 2) ' find last column with data in it
    For jLoop = UBound(myArr, 2) To LBound(myArr, 2) Step -1
        If myArr(iLoop, jLoop) = "" Then
            lastCol = jLoop - 1
        Else
            Exit For
        End If
    Next jLoop
    For jLoop = LBound(myArr, 2) To lastCol - 1 ' loop until second last column
        If InStr(1, myArr(iLoop, jLoop), ",") Then
            outStr = outStr & """" & myArr(iLoop, jLoop) & """" & ","
        Else
            outStr = outStr & myArr(iLoop, jLoop) & ","
        End If
    Next jLoop
    If InStr(1, myArr(iLoop, jLoop), ",") Then ' last column has no "," after it
        outStr = outStr & """" & myArr(iLoop, lastCol) & """"
    Else
        outStr = outStr & myArr(iLoop, lastCol)
    End If
    Print #iFile, outStr
Next iLoop

'clean up
Close iFile
Erase myArr

End Sub

i see commas at the end of csv file (when checking in txt format). How can i remove them? — Nick, May 06 '16 at 13:41
In my test case, I did not. If you are getting extra commas, it's because 'UsedRange' is bigger than you expected. There's techniques to clean that up, but that run the risk of omitting a cell located away from you main table. What kind of data is on the sheet you are saving? — OldUgly, May 06 '16 at 16:36

Preserve formatting when saving xls as csv

1 Answers1