导入文本(txt文件)的VBA代码

你们的但老师最近太忙啦,只能发一些做过的小工具给你们玩玩啦

Sub Dan()

Dim fileName$

Dim Wkb As Workbook

Dim Arr, iRow&, ArrTmp

fileName = Application.GetOpenFilename("Excel 文件 (*.txt),*.txt")

If fileName = "" Then Exit Sub

Application.ScreenUpdating = 0

Set Wkb = Workbooks.Open(fileName)

With Wkb.Sheets(1)

iRow = .Cells(.Rows.Count, 1).End(3).Row

Arr = .Range(.Cells(1, 1), Cells(1, iRow))

End With

With ThisWorkbook.Sheets(1)

.Cells.Clear

If IsArray(Arr) Then

For i = LBound(Arr) To UBound(Arr) - 1

ArrTmp = Split(Arr(i, 1), ",")

iRow = .Cells(.Rows.Count, 1).End(3).Row + 1

.Cells(iRow, 1).Resize(UBound(ArrTmp) + 1, 1) = Application.Transpose(ArrTmp)

Next

ElseIf TypeName(Arr) = "String" Then

ArrTmp = Split(Arr, ",")

iRow = .Cells(.Rows.Count, 1).End(3).Row + 1

.Cells(iRow, 1).Resize(UBound(ArrTmp) + 1, 1) = Application.Transpose(ArrTmp)

End If

.Cells(1, 1).EntireRow.Delete

End With

Wkb.Close 0

Application.ScreenUpdating = 1

Set Wkb = Nothing

End Sub

代码用法格式要求

1 txt文本文件

2 文本文件中的数据以逗号(,)进行分隔.就像下面这样

代码结果

提取文本文件中的数据,在第1列依次排列下去.就像这样