Best way to create a macro for creating a formatted text file.

[evoad]

Hi,

I've been trying to find the best way to convert a csv into a formatted text file. The requirements for the text file are specific and I need to manipulate some of the data fields before creating the file. My excel skills stopped at 2003 so its a bit rusty!

My first hurdle is rounding a number, it could be any from 2 decimal places to 6. eg 412.239987, 1562.24, 12799.97433 etc to 2 decimal places, but removing the decimal point itself and adding in leading 0 to make the total length 12, so 412.239987 would become 000000041224, 1562.24 would be come 000000156224 and 12799.97433 would be 000001279997 and so on.
I've been playing with


Sub RTemp()

Columns("G:G").Select
Selection.ColumnWidth = 12
Selection.NumberFormat = "000000000000"
Dim myLastRow As Long

For Each Cell In [G:G]
If Cell = "" Then Exit Sub
Cell.Value = WorksheetFunction.Round(Cell.Value, 2)
Next Cell

myLastRow = Cells(Rows.Count, "G").End(xlUp).Row
Cells(myLastRow + 1, "G") = 100
Cells(myLastRow + 1, "G").Copy
Range("G1:G" & myLastRow).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Cells(myLastRow + 1, "G").ClearContents


End Sub

Setting column width works, but the rounding seems round the whole number, and misses the .00 at the end.

This will be used to do monthly reports into our main business application, so creating a macro was my first thought but happy to given alternative advice.

Thanks!

 

[James006]

Hi,
You could test

=TEXT(INT(A2)&((A2-INT(A2))*100),"000000000000")