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

【引用位置】 https://blog.csdn.net/pashine/article/details/42100237

'-------------------------------------------

'获取某文件夹下的所有Excel文件

'-------------------------------------------

Sub getExcelFile(sFolderPath As String)

On Error Resume Next

Dim f As String

Dim file() As String

Dim x

k = 1

ReDim file(1)

file(1) = sFolderPath &""

End Sub

'-------------------------------------------

'获取某文件夹下的所有文件和子扰者好目录下的文件

'-------------------------------------------

Sub getAllFile(sFolderPath As String)

'Columns(1).Delete

On Error Resume Next

Dim f As String

Dim file() As String

Dim i, k, x

x = 1

i = 1

k = 1

ReDim 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 + 1

Loop

'-- 获得所有子目录下的所有文件

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

Loop

Next

End Sub

这段代码是读取一个文件夹下的所有文件,也可以根据扩展名筛选其它格式的.'有春轮了文件名,就是打开文件,获得每个文件的SHEET名字.然后写到你想要的地方Sub Macro1()Dim myDialog As FileDialog, oFile As Object, strName As String, n As IntegerDim FSO As Object, myFolder As Object, myFiles As Object ,Dim fn$Set myDialog = Application.FileDialog(msoFileDialogFolderPicker)n = 1With myDialogIf .Show <-1 Then Exit SubSet FSO = CreateObject(Scripting.FileSystemObject) '这是文件夹选择,点选到你存放文件的那个Set myFolder = FSO.GetFolder(.InitialFileName)Set myFiles = myFolder.FilesFor Each oFile In myFilesstrName = UCase(oFile.Name)strName = VBA.Right(strName, 3)If strName = xls Or strName = XLS Then '这是扩展名选择'下面就可接着写打开文件读取数据再写入的语句了,如下:fn = myFolder &\ &oFile.NameWorkbooks.Open Filename:=fnWorksheets(1).Select '假设你读取SHEET1的数据RANGE_ = Range(A2:F50) '需要数据的区域,自己修改Windows(外部表格数据自动导入.xls).Activate '这个是新表的文件名,自己修改下扒举信Worksheets(n).Select '打开第几个答耐文件就选择SHEET几,如果没有可用ADD代码添加

---