使用vba问题将txt批处理到excel

Sub ImportTextFile()
    Dim FilePath As String
    Dim FileContent As String
    Dim TextFile As Integer
    Dim LineArray() As String
    Dim DataArray() As String
    Dim TempArray() As String
    Dim RowCounter As Long
    Dim ColumnCounter As Integer
    
    ' 设置文本文件路径
    FilePath = "C:\path\to\your\textfile.txt"
    
    ' 打开文本文件
    TextFile = FreeFile
    Open FilePath For Input As TextFile
    
    ' 读取文本文件内容
    FileContent = Input$(LOF(TextFile), TextFile)
    
    ' 关闭文本文件
    Close TextFile
    
    ' 将文本文件内容按行分割为数组
    LineArray() = Split(FileContent, vbCrLf)
    
    ' 遍历每一行数据
    For RowCounter = 0 To UBound(LineArray)
        ' 将每一行数据按分隔符分割为数组
        TempArray() = Split(LineArray(RowCounter), ",")
        
        ' 将分割后的数据存储到DataArray数组中
        For ColumnCounter = 0 To UBound(TempArray)
            ReDim Preserve DataArray(1 To RowCounter + 1, 1 To ColumnCounter + 1)
            DataArray(RowCounter + 1, ColumnCounter + 1) = TempArray(ColumnCounter)
        Next ColumnCounter
    Next RowCounter
    
    ' 将数据写入Excel工作表
    With ThisWorkbook.Sheets("Sheet1") ' 修改为你要写入的工作表名称
        .Range("A1").Resize(UBound(DataArray, 1), UBound(DataArray, 2)).Value = DataArray
    End With
End Sub