Exporting a sheet to CSV causes a lot of numeric values to be saved as non-numeric text(?)

d0rian

Active Member
Joined
May 30, 2015
Messages
261
I have a macro-enabled workbook (call it "A.xlsm") with columns formatted in visually appealing ways: e.g. dates in a Date format, some number columns in Accounting format so that zeros appear as "-".
The workbook runs a bunch of formulas, and then exports a defined range to a separate CSV file ("B.csv") using the VBA below, which I then import into a different macro-enabled workbook ("C.xlsm") using Excel's Data Connection function.

The problem is that the "middle-man" B.csv file is saving some of the numeric values from A.xlsm in weird, hardcoded, non-numeric ways, such that they cause problems when imported into C.xlsm. For example:
  • It's saving all of the zero values (which are in Accounting format in A.xlsm) as text dashes ("-") in the CSV...so that when they're re-imported in C.xlsm, they're not recognized as numeric values at all but rather as just dashes.
  • Similarly, it's saving dates from A.xlsm file as text representations. E.g. today's date (April 4 2020) is 43925 in "Number" format, but since I have the date column in A.xlsm formatted as Dates, it's displayed as "4/4/20". But when that gets exported to the CSV, the cell value is saved as text: "4/4/20", and thus it's not recognized as a date (or even a number at all) when re-imported into C.xlsm.
I'm kinda surprised that it behaves this way, since I thought that the chosen number "formatting" of a cell was basically just aesthetic, and that the underlying value in the cell was treated the same by Excel regardless of the user's chosen formatting. But it seems like exporting a range to a CSV file "breaks" that basic behavior and converts numeric values to non-numeric text (at least when the export is done using the code below.)

Any thoughts? Is the code I'm using doing something sub-optimally?

VBA Code:
Sub Send_2_CSV()
    
    Dim MyPath As String
    Dim MyFileName As String
    MyPath = Environ("USERPROFILE") & "\files\"
    MyFileName = "ALL_K_EXPORT.csv"
    
    Application.ScreenUpdating = False
       
    Range(Range("grab_4_CSV").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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

CephasOz

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I know the value for FileFormat can be a few different values for CSV files: xlCSV, xlCSVMSDOS, xlCSVUTF8, xlCSVWindows. Do any of those give a different result?

And I've not seen the double Range that you have on the copy operation before. Do you get a different result if you use Range("grab_4_CSV").Copy? Perhaps also changing the paste operation to .Sheets(1).Cells(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone?

And your code looks good.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
FWIW, I like saving data in Excel workbooks. Then the values and formats are compatible, and there are no surprises with the CSV conversions.
 

CephasOz

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Jon makes a good point
I like saving data in Excel workbooks. Then the values and formats are compatible, and there are no surprises with the CSV conversions.
Is there a necessary reason for using CSVs, or is that just the method that was thought of at the time?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,318
Messages
5,595,454
Members
413,992
Latest member
CSEGUIN1973

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
Top