data types inside the vba

Question

I have a macro that does a conversion, it brings a fixed width txt converts to excel and saved in csv. what happens is that I need the last column to be saved in txto format because it deals with contract number and when by the amount of characters excel turns into "16752+52" and in the middle of those conversions I lose this data. how do i save the data I pulled from txt as text, even when in the field only has numbers?

below my code:

If Dir("F:\arquivo") <> "" Then
    Workbooks.OpenText Filename:="F:\arquivo", _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(5, 1), Array(12, 1), Array(20, 1), Array(29, 1), Array(31, 1), Array(76, _
        1), Array(87, 1), Array(92, 1), Array(93, 1), Array(104, 1), Array(107, 1), Array(113, 1), _
        Array(125, 1), Array(145, 1)), TrailingMinusNumbers:=True
    Columns("O:O").Select
    Selection.ClearContents
    Range("Q5").Select
      ActiveWindow.SmallScroll Down:=-18
    dirCopia = "F:\conersao_ok\"
    nomeCopia = "arquivo_ok"
ActiveWorkbook.SaveAs Filename:= _
dirCopia + nomeCopia, _
FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks("arquivo_ok.csv").Close SaveChanges:=False
End If
End Sub```

Answer 1

I believe there are two ways to solve your problem.

1. In the column where data needs to be converted from number to text, you can apply the function CStr, that converts values into a cell for text within a repeat structure Do While...Loop or similar, so you can go through each cell and do the conversion.

2. Instead of converting to text, you can use the function NumberFormat and apply a custom number formatting to the specific column you want to format. The functionality of this function is similar to when formatting a direct number in the spreadsheet.

I hope it helps.