首页/办公软件

vba如何打开一个文件夹及其子文件夹中所有的Excel文件?

发布于:2023-05-18 11:00:01
8640人 分享

腿腿教学网-vba如何打解一个文件夹及其子文件夹中所有的Excel文件?


步骤一:使用For Each..Next循环 

腿腿教学网-vba如何打解一个文件夹及其子文件夹中所有的Excel文件?

For Each…Next 循环可以帮助我们遍历文件夹及其子文件夹中的每个文件。如果发现是xlsx格式的文件,则将其添加到一个单独的集合中,以便后续操作。 

具体代玛如下:

Sub Check_Dir() Dim directory As String Dim fso As Object Dim fso_fldr As Object Dim fso_fldrs As Object Dim fso_file As Object Dim cls_files As New Collection 'Set directory directory = 

"C:\Documents\Excel_Files\" 'Create File System Object Set fso = CreateObject("Scripting.FileSystemObject") 'Get the folder object Set fso_fldr = fso.GetFolder(directory) 'Iterate through each file in 

the folder For Each fso_file In fso_fldr.Files If fso_file.Name Like "*.xlsx" Then cls_files.Add fso_file.Name End If Next fso_file 'Iterate through subfolders For Each fso_fldrs In fso_fldr.Subfolders 'Iterate 

through each file in the folder For Each fso_file In fso_fldrs.Files If fso_file.Name Like "*.xlsx" Then cls_files.Add fso_file.Name End If Next fso_file Next fso_fldrs 'Close File System Object fso.Close End 

Sub 

步骤二:使用Workbooks.Open打解文件 

当我们完成了上面的步骤,就可以使用Workbooks.Open来自动打解文件夹及其子文件夹中的所有文件。 

具体代玛如下: 

 Sub Open_Files() Dim directory As String Dim wb As Workbook Dim xlApp As Application Dim cls_files As New Collection Dim file As Variant 'Set current directory directory = 

"C:\Documents\Excel_Files\" 'Create File System Object Set xlApp = CreateObject("Excel.Application") 'Loop through the collection of files For Each file In cls_files Set wb = 

xlApp.Workbooks.Open(directory & file) 'Do More Stuff Next 'Clean up objects Set wb = Nothing: Set xlApp = Nothing End Sub 

以上就是VBA如何打解文件夹及其子文件夹中所有的Excel文件的详细说明,使用For Each…Next 循环、FileSystemObject对象和Workbooks.Open可以自动打解文件夹及其子文件夹中的所有Excel文件,提高工作效

率,节省大量时间。

转载请注明来源本文地址:https://m.tuituisoft/office/39094.html

上一篇:没有了 下一篇:没有了