keep text format when saving as csv file

grapevine

Board Regular
Joined
May 23, 2007
Messages
208
I have the following code which is part of a much larger macro which is converting a number so that the the trailing zero in the number 49.50 appears in the formula bar. This is essential as the eventual csv file links to an external printing program which only prints what is sees.

My problem is that this fix works when the file is saved in excel format, but when I save it in CSV format the number reverts to 49.5. How can I convert the original number which appears as 49.5 in the formula bar and is viewed a 00000050 in the file that the sent through. I cannot convert using the text to columns when the file is opened as the other codes in the original file need to maintain the original formatting.

Any assistance in this matter would be greatly appreciated
Kind regards

Code:
Sub mc003()
For Each ws In ActiveWorkbook.Worksheets
ws.Activate

    If ActiveSheet.Name = "MC003" Then
     rw = Range("a65536").End(xlUp).row
     
     Range("R2:R" & rw).FormulaR1C1 = "=TEXT(RC[-7],""000.00"")"
     Range("R2:R2").EntireColumn.Copy
     Range("R2:R2").EntireColumn.PasteSpecial xlPasteValues
     Range("S2:S" & rw).FormulaR1C1 = "=IF(LEFT(RC[-1],1)=""0"",REPLACE(RC[-1],1,1,""""))"
     Range("S1").Value = "GBP PRICE"
     Range("S:S").EntireColumn.Copy
     Range("K:K").EntireColumn.PasteSpecial xlPasteValues
    
     Columns("R:S").Delete
   End If
Next ws
End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
If you format the cells to display two decimal places, then the CSV file will retain that decimal precision (including trailing zeros) when saved.

This will set column K to display two decimal places.

Code:
Columns("K:K").NumberFormat = "0.00"
 

grapevine

Board Regular
Joined
May 23, 2007
Messages
208
Thank you AphaFrog, but when I reopened the csv file in excel (as it needs to be done in order that it can be read by the software) the number has reverted to 1 dp.
I added your instruction to the end of my code as shown below as when I tried this at the beginning, i still lost the trailing zero.
Any other suggestions would be greatly appreciated


Code:
Sub mc003()
For Each ws In ActiveWorkbook.Worksheets
ws.Activate

    If ActiveSheet.Name = "MC003" Then
     rw = Range("a65536").End(xlUp).row
     
     Range("R2:R" & rw).FormulaR1C1 = "=TEXT(RC[-7],""000.00"")"
     Range("R2:R2").EntireColumn.Copy
     Range("R2:R2").EntireColumn.PasteSpecial xlPasteValues
     Range("S2:S" & rw).FormulaR1C1 = "=IF(LEFT(RC[-1],1)=""0"",REPLACE(RC[-1],1,1,""""))"
     Range("S1").Value = "GBP PRICE"
     Range("S:S").EntireColumn.Copy
     Range("K:K").EntireColumn.PasteSpecial xlPasteValues
     Columns("K:K").NumberFormat = "0.00"
    
     Columns("R:S").Delete
   End If
Next ws
End Sub
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
The CSV text file has the two DP, but if you reopen it in Excel then Excel will strip the trailing zero from the text. If you format the column again in the the reopened file, then you will have the 2DP again.

If you open the CSV file in NotePad or any other text file reader, you will see the CSV file has the two DP.
 

grapevine

Board Regular
Joined
May 23, 2007
Messages
208

ADVERTISEMENT

The macro is part of a much larger macro that brings in a csv, formats it, creates delivery notes, packing slips and formats files ready printing lables. The printing files are saved as a CSV and automatically saved as as CSV. The lads then open them for printing so we are trying to avoid them reformating the column as their excel knowledge is very limited. The macro is designed to automate the whole process for them - it has been working fine up to know by the source company have changed the pricing for one item which means we have to have 2dp shown in the formula bar so that the printing machine can see and print this.

I need to find a way of keeping the data as text when the CSV is reopened.
Any help will be appreciated.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
If you are reopening the printing files in Excel, why are you saving them as a CSV files in the first place? Why not save them as an .xls file? That would preserve the formatting when you reopen them in Excel.
 

grapevine

Board Regular
Joined
May 23, 2007
Messages
208
The machine that reads the file only reads the files correctly when they are in CSV format. Unfortunately it reads the file as excel sees it so if I cannot see the zero in the formula bar the machine will not either. It is printing prices so 49.50 has to read 49.50 not 49.5 the £ is being put on by the machine.

I need a way of keeping the trailing zero in a CSV file that is reopened.
Kind regards
 

Watch MrExcel Video

Forum statistics

Threads
1,129,867
Messages
5,638,757
Members
417,051
Latest member
arthur_w

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top