My VBA that exports a range to CSV is adding 1 leading and 1 trailing space to text cells(??)

[d0rian]

EDIT: standby, I may have partially solved the mystery (seems that this might be caused if the source range has text cells that are formatted as ACCOUNTING format; this makes no sense as to why extra spaces would be added to an exported CSV file though...)

I've used the code below in many of my workbooks to export a specified range to a stand-alone CSV file. It's always worked fine, but for a reason I don't understand, in my latest file, the output CSV file has one leading and one trailing space added to every cell that contains a text value.
So for example, if a cell has value "C" in the source file, the exported CSV output has " C " (i.e. a trailing space before and after the text value.)

Those spaces are definitely not in the cells that serve as the source range. But they are in the CSV output file. This same code has never done this when I've used it previously. I'm perplexed -- anyone know what could be going on?

Sub Export_to_CSV()
   
    Dim MyPath As String
    Dim MyFileName As String
    MyPath = Environ("USERPROFILE") & "\Dropbox\files\"
    MyFileName = "output.csv"
   
    Application.ScreenUpdating = False
      
    Range(Range("range_to_export").Value).Copy
   
    With Workbooks.Add(xlWBATWorksheet)
        .Sheets(1).Paste
        Application.CutCopyMode = False
        Application.DisplayAlerts = False
        .SaveAs FileName:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
        Application.DisplayAlerts = True
        .Close False
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

 

[d0rian]

Yeah, OK, per the bolded EDIT I added to the top of the OP, this seems to be happening because I had my source range set as ACCOUNTING format (even columns that only contained text.)
This begs the question of why that would result in exported CSV having extra leading and trailing spaces; I have noticed that when I use the ACCOUNTING format, it does change the visual alignment of numerical values in cells. But I can think of no reason why it would result in 2 extra spaces magically being added to cell values in an exported CSV file(!?) After all, when I click into the cells in the source file, there are no leading/trailing spaces, even when they're ACCOUNTING-formatted. So no rationale for spaces to magically appear in an exported CSV(?) Oh well.

(Excel 2007 fwiw)

 

[Peter_SSs]

Accounting is obviously designed for numerical values. With Accounting format, negative numbers are enclosed in parentheses so the realignment you see is so that both positive and negative numbers align correctly with their decimal points and digits - as shown in E1 & E2 below. To do the realignment Excel needs to 'reserve' space for the parentheses on both left and right of the value.
That format remains the same even if you choose to enter text in the cell - as you can see in rows 3:7 below
E3 is formatted as Accounting and E4 not so. E5 & E6 are formatted right-aligned with E5 Accounting and E6 not.
In all cases the Accounting reserves that space for parentheses and that is why your csv is getting them.