txt文件向excel转表的VBA代码

Sub TxtToExcel()
    Dim txtFilePath As String
    Dim excelFilePath As String
    Dim txtFileContent As String
    Dim txtFileLines() As String
    Dim excelApp As Object
    Dim excelWorkbook As Object
    Dim excelWorksheet As Object
    Dim i As Long
    
    ' 设置txt文件路径和Excel文件路径
    txtFilePath = "C:\path\to\input.txt"
    excelFilePath = "C:\path\to\output.xlsx"
    
    ' 读取txt文件内容
    Open txtFilePath For Input As #1
    txtFileContent = Input$(LOF(1), 1)
    Close #1
    
    ' 将txt文件内容按行分割为数组
    txtFileLines = Split(txtFileContent, vbCrLf)
    
    ' 创建Excel应用程序对象
    Set excelApp = CreateObject("Excel.Application")
    excelApp.Visible = True
    
    ' 创建新的Excel工作簿
    Set excelWorkbook = excelApp.Workbooks.Add
    Set excelWorksheet = excelWorkbook.Worksheets(1)
    
    ' 将txt文件内容逐行写入Excel表格
    For i = 0 To UBound(txtFileLines)
        excelWorksheet.Cells(i + 1, 1).Value = txtFileLines(i)
    Next i
    
    ' 保存Excel文件
    excelWorkbook.SaveAs excelFilePath
    
    ' 关闭Excel文件和应用程序对象
    excelWorkbook.Close
    excelApp.Quit
    
    ' 释放对象变量
    Set excelWorksheet = Nothing
    Set excelWorkbook = Nothing
    Set excelApp = Nothing
    
    MsgBox "转换完成！"
End Sub