Excel VBA保存工作表为文本格式时小数丢失问题咨询

Great question—let’s break this down step by step, because your hunch about regional settings is spot-on, but there are a few other likely culprits you might have missed.

First, your suspicion is correct: Regional decimal settings can cause this #

If your system’s regional settings are set to 0 decimal places, even if you manually set the decimal separator to ,, Excel will respect that "0 decimal places" rule when saving with Local:=True—especially if your cells are still formatted as numbers instead of text. This would absolutely truncate your decimal values.

But there are two other critical mistakes that are probably contributing more: #

1. You’re mixing up FileFormat values
   You mentioned using format 42 (text format) to save as .xls—but xlUnicodeText (value 42) is a plain text file format, not an Excel .xls format. Saving an Excel workbook as format 42 will convert your data to a text file, which can mangle decimal values during the conversion. For .xls files, you need to use xlExcel8 (value 56) as the FileFormat.

2. Your decimal separator setup is incomplete
   Just setting Application.DecimalSeparator = "," won’t work unless you first disable system separators. Excel will override your manual setting if Application.UseSystemSeparators = True (the default).

Here’s a fixed version of your code that addresses all these issues: #

Function SaveWorksheetAsXLSWithCommaDecimals(ByVal targetWS As Worksheet, ByVal savePath As String) As Boolean
    Dim newWorkbook As Workbook
    Dim originalDecimalSep As String
    Dim originalUseSystemSep As Boolean
    
    ' Handle errors gracefully
    On Error GoTo CleanupAndError
    
    ' Save original regional settings to restore later
    originalDecimalSep = Application.DecimalSeparator
    originalUseSystemSep = Application.UseSystemSeparators
    
    ' Disable system separators so our manual decimal setting takes effect
    Application.UseSystemSeparators = False
    Application.DecimalSeparator = ","
    
    ' Copy the worksheet to a new workbook
    targetWS.Copy
    Set newWorkbook = ActiveWorkbook
    
    ' Force all cells to text format to preserve every decimal digit
    ' This bypasses any regional decimal place restrictions
    newWorkbook.Sheets(1).Cells.NumberFormat = "@"
    
    ' Save as .xls (xlExcel8) with Local:=True to respect our comma separator
    newWorkbook.SaveAs _
        Filename:=savePath, _
        FileFormat:=xlExcel8, _
        Local:=True
    
    ' Close the new workbook (no need to save again—we already did SaveAs)
    newWorkbook.Close SaveChanges:=False
    
    ' Success!
    SaveWorksheetAsXLSWithCommaDecimals = True
    Exit Function

CleanupAndError:
    ' Always restore original settings, even if something goes wrong
    Application.UseSystemSeparators = originalUseSystemSep
    Application.DecimalSeparator = originalDecimalSep
    SaveWorksheetAsXLSWithCommaDecimals = False
    MsgBox "Error saving file: " & Err.Description, vbExclamation
End Function

Key fixes explained: #

• Restoring regional settings: We save and restore your original decimal separator settings so we don’t mess up other parts of Excel.
• Disabling system separators: Ensures our comma separator is actually used instead of being overridden by Windows settings.
• Text format for cells: Converting all cells to text (NumberFormat = "@") guarantees that every decimal digit is preserved, regardless of regional decimal place limits.
• Correct FileFormat: xlExcel8 is the proper value for legacy .xls files, avoiding the data conversion issues of using format 42.

Final check: #

If you still see truncated decimals after using this code, double-check your system’s regional settings:

1. Open Windows Settings → Time & Language → Language & Region → Additional date, time, & regional settings
2. Click "Change date, time, or number formats"
3. Go to the "Numbers" tab and verify the "Decimal places" value isn’t set to 0. Even with the text format fix, this is good to rule out.

内容的提问来源于stack exchange，提问作者Max Christian Potaß