VBA TXT文本文件 转 Excel

昨天写到是以~尾分割符导出TXT文件，今天介绍以~分隔符导入Excel

*******************************************

*******************************************

Sub txt数据导入() '导入数据

'关闭屏幕刷新

Application.ScreenUpdating = false

Dim arry() As String, f As String, j As Long

'选择文本文件

mytxt = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "请选择文件")

If mytxt = False Then Exit Sub

'打开文本文件

Open mytxt For Binary As #1

'把TXT文件读入数组

arry = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbLf)

'关闭TXT文件

Close #1

'从第五行开始写入

y = 5

'从TXT文件第一行读到文件尾

For j = 0 To UBound(arry)

'如果数组J不为空则写入

If arry(j) <> "" Then

'定义空数组

Dim brry() As String

'把数组J以~分割符写入空数组

brry() = Split(arry(j), "~")

'定义循环

Dim nu As Integer

nu = 0

'开始写入TXT文件到单元格

For Each out In brry

Cells(y, nu + 1) = out

nu = nu + 1

Next

y = y + 1

End If

Next j

'打开屏幕刷新

Application.ScreenUpdating = True

MsgBox "TXT数据导入完成"

End Sub

*******************************************

*******************************************