步骤一:使用For Each..Next循环
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