[Excel VBA] How to Export to CSV While Maintaining Cell Formatting (.Text Property)

When exporting Excel data to a CSV file using VBA, have you ever experienced issues like “dates becoming unintended serial numbers” or “yen marks and comma separators disappearing”?

This happens because VBA reads the internal value (.Value) of the cell.

This article explains how to export data to a CSV file while preserving the visual appearance (formatting) of the cells, using specific code examples.

By understanding the .Text property, which is the key to this method, you can achieve the exact CSV output you desire.

Difference Between .Value and .Text

Before diving into the main topic, it is essential to understand the difference between .Value and .Text when handling cells in VBA.

• .Value: The actual raw data stored in the cell. This is the internal value used for calculations.
• .Text: The string displayed on the screen as a result of applying formatting and styles.

Original Cell Display	Value Retrieved by .Value	Value Retrieved by .Text
2025/08/09	45879	“2025/08/09”
¥15,000	15000	“¥15,000”
75%	0.75	“75%”
00123 (String)	123	“00123”

If you want the data “exactly as it looks,” you need to use the .Text property.

Completed VBA Code

Below is the VBA code that uses the .Text property to write the displayed values to a CSV file.

Sub ExportDisplayedValueToCsv()

    ' Declare variables
    Dim outputCsvPath As String
    Dim fileNum As Integer
    Dim rowArray() As Variant
    Dim exportRange As Range
    Dim i As Long
    Dim j As Long

    ' --- Settings ---
    ' Specify the cell range to export
    Set exportRange = ThisWorkbook.Worksheets("Sheet1").Range("B2:F10")
    ' --- End Settings ---

    ' Specify the path and filename for the output CSV
    outputCsvPath = ThisWorkbook.Path & "\DisplayedData.csv"
    
    ' Resize the array to hold data for one row based on the column count
    ReDim rowArray(1 To exportRange.Columns.Count)
    
    ' Get a free file number for file operations
    fileNum = FreeFile
    
    ' Open the file in Output mode
    Open outputCsvPath For Output As #fileNum
    
    ' Loop from the first row to the last row of the specified range
    For i = 1 To exportRange.Rows.Count
    
        ' Loop from the first cell to the last cell in the row
        For j = 1 To exportRange.Columns.Count
            ' Store the "displayed value" in the array using the .Text property
            rowArray(j) = exportRange.Cells(i, j).Text
        Next j
        
        ' Join the array contents with commas and write the line to the file
        Print #fileNum, Join(rowArray, ",")
        
    Next i
    
    ' Close the file
    Close #fileNum
    
    MsgBox "Exported to CSV using displayed values."

End Sub

Key Points Explanation

Here is an explanation of the critical parts of the code.

1. Preparation of Export Range and Array

Set exportRange = ThisWorkbook.Worksheets("Sheet1").Range("B2:F10")
ReDim rowArray(1 To exportRange.Columns.Count)

The range to be exported is stored in exportRange. Then, ReDim is used to reserve the size of the array (rowArray) to match the number of columns in the export range. This array temporarily holds data for one row.

2. Looping Row by Row

For i = 1 To exportRange.Rows.Count
    ' ... (Process within the row) ...
Next i

The first For loop processes the specified exportRange sequentially from the first row to the last.

3. Storing Cell Values in Array (Using .Text)

For j = 1 To exportRange.Columns.Count
    rowArray(j) = exportRange.Cells(i, j).Text
Next j

The inner For loop processes the cells of the current row (row i) from left to right.

The most important part here is exportRange.Cells(i, j).Text. As mentioned earlier, using the .Text property retrieves the “string exactly as seen” with formatting applied and stores it in the array.

4. Creating CSV Line with Join and Writing

Print #fileNum, Join(rowArray, ",")

After storing the cell values for one row, the Join function connects them with commas (,). This efficiently creates a CSV-formatted string (e.g., “Value1,Value2,Value3…”) and writes it to the file using Print #.

5. Closing the File

Close #fileNum

After writing all rows, Close ensures the file is closed properly. This saves all data and safely releases the file.

Summary

When exporting data to a file using Excel VBA, if you want to reflect the “appearance” of the cells exactly, using the .Text property instead of .Value is the solution.

This method is highly effective when you want to output data formatted in Excel—such as dates, currencies, percentages, and zero-padded numbers—as a report intended for human reading.

By being conscious of the difference between .Value and .Text, you can significantly improve the accuracy of data manipulation in VBA. Please try using them according to your needs.