VBA读取固定文件夹中txt内容

Sub OneTxt() '打开一个txt文件
Dim Filename As Variant, extLine&, mArr() As String
Dim i%, j%, txtpath As String
Dim txtname As Variant


ChDir ThisWorkbook.Path

txtpath = "D:\Users\addy.zhao\Desktop\computers\"



'Filename = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "请选取档案", , MultiSelect:=True)

Filename = FileList(txtpath)
j = 1

For k = 0 To UBound(Filename)

txtname = txtpath + Filename(k)
If txtname = False Then Exit Sub '最后一个选项MultiSelect等于false表示只能选择一个文件，如下面的等于true可以选择多个文件
'选择多个文件的时候是一个数组，需要循环打开，一个的时候就是字符串

With Worksheets("sheet1")
'.Cells.ClearContents '删除第一个表的内容
Open txtname For Input As #1 ' 以只读的方式打开文件，参考open方法的帮助
Do While Not EOF(1) ' 循环至文件尾。
Line Input #1, TextLine ' 读入一行数据并将其赋予某变量
mArr = Split(TextLine, ",") '按空格分开这个字符，循环赋值给单元格
For i = 0 To UBound(mArr)
.Cells(j, i + 2) = mArr(i) '此处参考split函数的帮助
Next i
.Cells(j, 1) = Filename(k) '等于文件名
j = j + 1
Loop

Close #1 ' 关闭文件。

 

End With
Next k
End Sub

Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
Dim sTemp As String, sHldr As String
If Right$(fldr, 1) <> "" Then fldr = fldr & ""
sTemp = Dir(fldr & fltr)
If sTemp = "" Then
FileList = Split("No files found", "|") '确保返回数组
Exit Function
End If
Do
sHldr = Dir
If sHldr = "" Then Exit Do
sTemp = sTemp & "|" & sHldr
Loop
FileList = Split(sTemp, "|")
End Function

转载于:https://www.cnblogs.com/flyrain/p/VBA_TXT1.html