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

[d0rian]

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?

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

 

[CephasOz]

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]

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]

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?