ExcelVBA获得文件夹中的指定类型的文件存入数组

ExcelVBA获得文件夹中的指定类型的文件存入数组

这是我的测试代码

Sub yhdtttt()

FileArr = fcnGetFileList(ThisWorkbook.Path, "*.xlsx")

MsgBox FileArr(1)

ActiveSheet.Range("a1").Resize(UBound(FileArr, 1), 1) = Application.Transpose(FileArr)

End Sub

'====自定义函数=======================

'使用方法：arr=fcnGetFileList(ThisWorkbook.Path, "*.xlsx")

'参数1：strPath，目录路径

'参数2：strFilter，指定要什么类型的文件如：“*.*”

'====================================

Private Function fcnGetFileList(ByVal strPath As String, Optional strFilter As String) As Variant

' 将文件列表放到数组

Dim f As String

Dim i As Integer

Dim filelist() As String

If strFilter = "" Then strFilter = "*.*"

Select Case Right(strPath, 1)

Case "\", "/"

strPath = Left(strPath, Len(strPath) - 1)

End Select

ReDim Preserve filelist(0)

f = Dir(strPath & "\" & strFilter)

Do While Len(f) > 0

ReDim Preserve filelist(i) As String

filelist(i) = f

i = i + 1

f = Dir()

Loop

If filelist(0) <> Empty Then

fcnGetFileList = filelist

Else

fcnGetFileList = False

End If

End Function

测试成功