获取任意指定路径下的文件名

​

Sub mysearch()

Dim fs, i, arr(1 To 100000)

Set fs = Application.FileSearch '设置一个搜索对象

With fs

.LookIn = "F:\" '设置搜索路径

.Filename = "*.*" '要搜索文件名和类型

.SearchSubFolders = True '是否需要搜索子文件夹

If .Execute > 0 Then '如果找不到文件

MsgBox "There were " & .FoundFiles.count & _

" file(s) found." '显示文件找不到

For i = 1 To .FoundFiles.count

'一、Workbooks.Open .FoundFiles(i)'遍历打开找到的EXCEL文件这是打开

'二、通过循环把所有搜索到的文件存入到数组中

arr(i) = .FoundFiles(i)

Next i

Sheets(1).Range("A1").Resize(.FoundFiles.count) = Application.Transpose(arr) ' '把数组内的路径和文件名放在单元格中

Else

MsgBox "There were no files found."

End If

End With

End Sub