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:
Any thoughts? Is the code I'm using doing something sub-optimally?
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.
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