如何修改VBA导入代码实现Excel自动识别文本中的数字与日期

问题背景 #

现有VBA代码导入逗号分隔的TXT文件时，所有内容均被识别为普通文本，需手动转换数字和日期格式。要求用单段代码完成导入+自动格式识别，不使用Power Query，无需分步执行。

修改后的VBA代码 #

Sub ImportTextWithAutoFormat()
    Dim filePath As Variant
    Dim fileFilter As String
    Dim importRange As Range
    Dim allText As String
    
    fileFilter = "Text Files (*.txt),*.txt"
    filePath = Application.GetOpenFilename(fileFilter)
    
    If filePath = False Then
        MsgBox "未选择文件。"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    ' 设定导入起始位置为当前活动单元格
    Set importRange = ActiveCell
    
    ' 一次性读取整个TXT文件内容
    Open filePath For Input As #1
    allText = Input$(LOF(1), #1)
    Close #1
    
    ' 将文本写入起始单元格
    importRange.Value = allText
    
    ' 调用文本分列功能自动识别格式，逗号作为分隔符
    importRange.TextToColumns _
        Destination:=importRange, _
        DataType:=xlDelimited, _
        Comma:=True, _
        FieldInfo:=Array(Array(1, xlGeneralFormat)) ' xlGeneralFormat自动识别数字、日期、文本
    
    Application.ScreenUpdating = True
End Sub

关键改动说明 #

1. 高效读取文件：一次性读取整个TXT内容，替代逐行读取，提升处理速度
2. 自动格式识别：使用Excel内置的TextToColumns方法，指定逗号为分隔符，xlGeneralFormat参数会让Excel自动判断每个字段的类型：
   • 数字文本自动转换为数值格式
   • 符合日期格式的文本自动转换为日期格式
   • 普通文本保持原样
3. 单段代码完成：导入与格式识别合并为一个流程，无需额外操作

自定义日期格式（可选） #

如果TXT中日期为特定格式（如日-月-年），可修改FieldInfo参数指定对应列的格式。例如，指定第3列按日-月-年识别：

FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlGeneralFormat), Array(3, xlDMYFormat))

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