CODEWITHSUNDEEP
vbaexcelcsv
JLSmith2118
JLSmith2118

Reputation: 23

Exported CSV Formatting Issues

I have a macro that Macro Man created for me that exports an excel table by range to a csv. When I run the macro the formatting of the csv is not what I am looking for. For instance, the last column in the spreadsheet is a money value (100.25), but when I convert it I get a value like 100.2542. Is there anywhere in the macro I can insert a command allowing me to correct or change the formatting to the correct format? Thanks for any help in advance.

 Sub MacroMan()

ChDrive "P:" '// <~~ change current drive to P:\
Dim copyRng As Excel.Range
Dim ThisWB  As Excel.Workbook
Dim OtherWB As Excel.Workbook
Dim sName   As String

'// set reference to the 'Master' workbook
Set ThisWB = ActiveWorkbook

'// assign selected range to 'copyRng'
Set copyRng = Application.InputBox(Prompt:="Select range to convert   to         CSV", Type:=8)

'// If the user selected a range, then proceed with rest of code:
 If Not copyRng Is Nothing Then
'// Create a new workbook with 1 sheet.
Set OtherWB = Workbooks.Add(1)

'// Get A1, then expand this 'selection' to the same size as copyRng. 
'// Then assign the value of copyRng to this area (similar to copy/paste)
OtherWB.Sheets(1).Range("A1").Resize(copyRng.Rows.Count,                  copyRng.Columns.Count).Value = copyRng.Value

'// Get save name for CSV file.
sName = Application.GetSaveAsFilename(FileFilter:="CSV files (*.csv),    *.csv")

'// If the user entered a save name then proceed:
If Not LCase(sName) = "false" Then
    '// Turn off alerts
    Application.DisplayAlerts = False
    '// Save the 'copy' workbook as a CSV file
    OtherWB.SaveAs sName, xlCSV
    '// Close the 'copy' workbook
    OtherWB.Close
    '// Turn alerts back on
    Application.DisplayAlerts = True
End If

'// Make the 'Master' workbook the active workbook again
ThisWB.Activate

MsgBox "Conversion complete", vbInformation

End If

End Sub

Upvotes: 0

Views: 181

Answers (1)

user4039065
user4039065

Reputation:

I do not personally recommend this action. Currency is typically stored and calculated to four decimal places when displayed to two decimal places. This is to ensure accuracy with tax calculation, multiplication against quantities expressed as mixed numbers and the totaling of a long column of numbers in a complicated formula. Truncating off values to two decimal places will inevitably get you 'losing a penny' somewhere.

With that said, that is what you want to do so here is how you do it.

'// If the user entered a save name then proceed:
If Not LCase(sName) = "false" Then
    '// Turn off alerts
    Application.DisplayAlerts = False
    ' set Precision As Displayed' - permanently truncates off numbers to the displayed decimal places
    OtherWB.PrecisionAsDisplayed = True
    '// Save the 'copy' workbook as a CSV file
    OtherWB.SaveAs sName, xlCSV
    ' turn off Precision As Displayed' - numbers have been permanently changed; you don't get back the lost decimal places
    OtherWB.PrecisionAsDisplayed = False
    '// Close the 'copy' workbook
    OtherWB.Close
    '// Turn alerts back on
    Application.DisplayAlerts = True
End If

Upvotes: 1

Related Questions