keep text format when saving as csv file

[grapevine]

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

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

 

[AlphaFrog]

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.

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

 

[grapevine]

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

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]

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]

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]

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]

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