VBA获取某文件夹下所有文件和子文件目录的文件

来源：互联网发布：ntfs for mac有什么用编辑：程序博客网时间：2024/05/16 11:10

<strong>VBA获取某文件夹下所有文件，或子文件目录的文件</strong>

'-------------------------------------------'获取某文件夹下的所有Excel文件'-------------------------------------------Sub getExcelFile(sFolderPath As String)On Error Resume NextDim f As StringDim file() As StringDim xk = 1ReDim file(1)file(1) = sFolderPath & "\"    f = Dir(file(1) & "*.xlsx")     '通配符*.*表示所有文件，*.xlsx Excel文件    Do Until f = ""       'Range("a" & x) = f       Range("a" & x).Hyperlinks.Add Anchor:=Range("a" & x), Address:=file(i) & f, TextToDisplay:=f        x = x + 1        f = Dir    LoopEnd Sub'-------------------------------------------'获取某文件夹下的所有文件和子目录下的文件'-------------------------------------------Sub getAllFile(sFolderPath As String)'Columns(1).DeleteOn Error Resume NextDim f As StringDim file() As StringDim i, k, xx = 1i = 1k = 1ReDim file(1 To i)file(1) = sFolderPath & "\"'-- 获得所有子目录Do Until i > k    f = Dir(file(i), vbDirectory)        Do Until f = ""            If InStr(f, ".") = 0 Then                k = k + 1                ReDim Preserve file(1 To k)                file(k) = file(i) & f & "\"            End If            f = Dir        Loop    i = i + 1Loop'-- 获得所有子目录下的所有文件For i = 1 To k    f = Dir(file(i) & "*.*")    '通配符*.*表示所有文件，*.xlsx Excel文件    Do Until f = ""       'Range("a" & x) = f       Range("a" & x).Hyperlinks.Add Anchor:=Range("a" & x), Address:=file(i) & f, TextToDisplay:=f        x = x + 1        f = Dir    LoopNextEnd Sub

1 0