VBA导入TXT到Excel时提前设置指定列为文本格式的实现方法

末尾0丢失核心是格式设置时机和列类型配置错误：

1. 执行TextToColumns文本分列时，所有列的类型参数都设为了1（常规格式），Excel会自动将识别为数字的内容转为数值类型，自动省略末尾无意义的0
2. 全局替换.的操作范围过大，且仅对单个单元格设置了格式，其余单元格仍为常规格式，替换写入内容时会再次触发Excel自动类型转换，导致内容丢失

1. 分列阶段直接指定列类型 #

TextToColumns的FieldInfo参数中，每个列配置的第二个值对应列类型：

• 1 = 常规格式（自动识别类型，会丢失末尾0）
• 2 = 文本格式（完整保留原始字符，不会自动转换类型）
  只需将需要保留原始数字字符串（如123.870这类值）的列对应配置值改为2即可。

2. 替换前批量设置目标区域格式 #

不要仅对单个单元格设置格式，在执行替换操作前，先将所有需要保留原始内容的目标区域整段设置为文本格式，再执行替换，避免触发自动类型转换。 #

Sub ImportFichierTxt()
    
    Dim ObjFSO As Object
    Dim VarFileToOpen As Variant
    Dim WkNewWk As Worksheet
    Dim ObjFileToRead As Object
    Dim LngLine As Long
    Dim StrLine As String
    Dim fnd As Variant
    Dim rplc As Variant
    Dim NoCol As Integer
    Dim NoLig As Long, DerLig As Long, Var As Variant
    Dim targetRng As Range
    
    fnd = "."
    rplc = ""
    
    Application.ScreenUpdating = False
    
    VarFileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If VarFileToOpen = False Then Exit Sub
    Set WkNewWk = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ' 读入TXT前先将A列设为文本，避免初始读入阶段就触发自动格式转换
    WkNewWk.Columns("A:A").NumberFormat = "@"
    
    Set ObjFSO = CreateObject("Scripting.FileSystemObject")
    Set ObjFileToRead = ObjFSO.OpenTextFile(VarFileToOpen, 1) 'ForReading常量值为1，避免未引用脚本库时报错
    LngLine = 1
    Do While ObjFileToRead.AtEndOfStream = False
       StrLine = ObjFileToRead.ReadLine
       WkNewWk.Cells(LngLine, 1) = StrLine
       LngLine = LngLine + 1
    Loop
    ObjFileToRead.Close
 
    ' 分列时按需配置列格式：第二个参数为2即文本格式，可根据业务需要调整个别列的类型
    WkNewWk.Columns("A:A").TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), _
        Array(59, 2), Array(80, 2), Array(92, 2), Array(105, 2), Array(123, 2), Array(134, 2), _
        Array(146, 2), Array(165, 2)), TrailingMinusNumbers:=True
    
    WkNewWk.Columns.AutoFit
     
    DerLig = WkNewWk.UsedRange.Rows.Count '直接读取已用区域行数，比拆分地址字符串更稳定
    NoCol = 2
    
    ' 定位"MOIS DE"行后，先设置目标区域格式再执行替换
    For NoLig = 1 To DerLig
        Var = WkNewWk.Cells(NoLig, NoCol).Value
        If Var = "MOIS DE" Then
            ' 按需划定替换范围，示例为"MOIS DE"下一行到已用区域末尾的所有单元格
            Set targetRng = WkNewWk.Range(WkNewWk.Cells(NoLig + 1, 1), WkNewWk.Cells(DerLig, WkNewWk.UsedRange.Columns.Count))
            targetRng.NumberFormat = "@"
            ' 仅在目标区域执行替换，避免全表替换误改其他内容
            targetRng.Replace what:=fnd, Replacement:=rplc, _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
            Exit For '找到目标行后直接退出循环，无需遍历全部行
        End If
    Next

    ' 释放对象
    Set WkNewWk = Nothing
    Set ObjFSO = Nothing
    Set ObjFileToRead = Nothing
    Set targetRng = Nothing
    
    Application.ScreenUpdating = True

End Sub

• 读入TXT、分列两个前置环节就提前设置好文本格式，从源头避免Excel自动转类型丢内容
• 替换操作限定在目标区域内执行，不会误改其他不需要处理的单元格
• 优化了最后行号的读取逻辑，兼容性更强
• 改为FSO晚绑定写法，不需要手动引用脚本运行库即可直接运行

内容的提问来源于stack exchange，提问作者globlobi2003