VBA code to copy sheet and save it as csv loses precision, even in integers!

[Denise Draper]

I am trying to save each sheet of an xlsm file into its own CSV. In order not to mess up the current workbook, I decided to do this by copying the cells of each sheet to a new workbook then saving that workbook (which has only one sheet) as csv. The relevant subroutine is this:

Sub Save_Sheet_As_CSV(sheetname As String, filename As String)
    ' Make a CSV file for sheet in the same directory as the current workbook
    ' To save a single sheet without affecting the current workbook, we make a new
    ' workbook, copy the sheet to it, save that workbook, then close it.
    
    Application.ScreenUpdating = False

    Dim dir As String
    Dim sht As Worksheet
    Dim tmpbook As Workbook
    
    dir = ActiveWorkbook.Path
    Set sht = Sheets(sheetname)
    Set tmpbook = Workbooks.Add
    outname = dir & Application.PathSeparator & filename
    
    ' Activate the original sheet to make sure any activation macros have run (Detailed Results does some)
    sht.Activate
    ' Copy over the Cells (not the Sheet) so that we don't get any of that pesky macro stuff.
    sht.Cells.Copy
    tmpbook.Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues

    ' Save the temporary workbook, suppressing "Do you want to overwrite" dialog
    Application.DisplayAlerts = False
    tmpbook.SaveAs filename:=outname, FileFormat:=xlCSVUTF8
    Application.DisplayAlerts = True

    ' Bye bye
    tmpbook.Close
    Application.ScreenUpdating = True
End Sub

This seemed to work fine, but on closer inspection, data are being truncated. Look at the two examples below: the first is from the original spreadsheet, the second from the CSV I created.
This seems absolutely crazy to me; I can't believe there would be a bug of this magnitude so clearly I am doing something wrong, but I don't have the faintest idea what.
BTW, if I do just "save as CSV" from Excel, the result is correct. And changing the output format in the code above to xlWorkbookDefault also produces the correct results.
What could be happening here?

Original table:

46,661.08	-$163,536,860,805.70	-$163,536,860,805.70
47,015.70	-$164,779,719,348.63	-$328,316,580,154.33
47,373.24	-$166,032,845,670.56	-$494,349,425,824.90
47,734.17	-$167,297,822,434.09	-$661,647,248,258.99
35,601.03	-$124,773,809,310.84	-$786,421,057,569.82
24,279.23	-$85,093,391,185.97	-$871,514,448,755.80
13,709.49	-$48,048,753,414.48	-$919,563,202,170.28
3,836.00	-$13,444,324,915.73	-$933,007,527,086.01
5,393.06	$18,901,495,033.52	-$914,106,032,052.49

When saved as CSV and re-opened:

46,661.08	163,537,000,000.00	163,537,000,000.00
47,015.70	164,780,000,000.00	328,317,000,000.00
47,373.24	166,033,000,000.00	494,349,000,000.00
47,734.17	167,298,000,000.00	661,647,000,000.00
35,601.03	124,774,000,000.00	786,421,000,000.00
24,279.23	85,093,391,186.00	871,514,000,000.00
13,709.49	48,048,753,414.00	919,563,000,000.00
3,836.00	13,444,324,916.00	933,008,000,000.00
5,393.06	18,901,495,034.00	914,106,000,000.00

 

[kennypete]

Change the line

    tmpbook.Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues

to

    tmpbook.Sheets(1).Cells.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

...and it should work. Pasting the values pastes a number larger than can be handled whereas the xlPasteFormulasAndNumberFormats ensures your formatting is retained (i.e. saved to the .csv as text/quoted cells).

 

[kennypete]

Change the line

    tmpbook.Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues

to

    tmpbook.Sheets(1).Cells.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

...and it should work. Pasting the values pastes a number larger than can be handled whereas the xlPasteFormulasAndNumberFormats ensures your formatting is retained (i.e. saved to the .csv as text/quoted cells).

Actually it may be xlPasteValuesAndNumberFormats you need

 

[Denise Draper]

That did it! Thank you so much. I didn't think the number formats would affect the saved value, but I can see that it wrote them out in exponential format, and that was the problem.