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?
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: