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

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
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?

VBA Code:
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
 
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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)
 
Upvote 0
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.

1636934345153.png
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top