0

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

Improve this question
2
  • Why do you want to lose accuracy? Commented Feb 11, 2016 at 14:09
  • Thank you for your help. The macro was created so that importing into another system would be easier. That system only accepts csv and in this case throws an error when the total column has 4 decimal places. Commented Feb 11, 2016 at 22:34

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Any idea why the total column only shows two decimal places when the $ symbol is attached in the formatting? I have the column formatted as currency but the only way I can get the csv to have two decimals is to add the $ symbol. The reason I ask is because the software that I'm importing into doesn't allow special characters.
There are several ways to show two decimal places. Suggest you do a little light reading on number formats.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.