VBA代码如下,详见附件
Sub test()
Dim mPath As String, fA As String, mAry(1 To 1000), k As Integer, i As Integer, wb As Workbook
On Error Resume Next
If Workbooks.Count > 1 Then MsgBox "关闭其他工作簿!": Exit Sub
MsgBox "选择子
文件夹所在的父目录!"
'----------设置父文件夹路径-----
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 0 Then
MsgBox "你没有选择任何文件夹!"
Else
mPath = .SelectedItems(1)
End If
End With
fA = Dir(mPath & "\*", vbDirectory) '开始收集子目录名称
k = 0
Do While fA <> ""
If fA <> "." And fA <> ".." Then
If (GetAttr(mPath & "\" & fA) And vbDirectory) = vbDirectory Then
k = k + 1
mAry(k) = fA
End If
End If
fA = Dir
Loop
'--------------遍历各子目录--------
Application.DisplayAlerts = False
For i = 1 To k
fA = Dir(mPath & "\" & mAry(i) & "\*.xls*")
Do While fA <> ""
If fA <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(mPath & "\" & mAry(i) & "\" & fA, , False) '打开
wb.Save '保存
wb.Close True '关闭
End If
fA = Dir
Loop
Next i
Application.DisplayAlerts = True
MsgBox "处理完成!"
End Sub
追问已经可以实现我预期的功能了,非常感谢。
但现在每次都需要选择”选择子文件夹所在的父目录!"
能不能直接改成指定的路径,如D:\Project overview 2015
这样我就不需要每次执行都选择一次?
追答Sub test()
Dim mPath As String, fA As String, mAry(1 To 1000), k As Integer, i As Integer, wb As Workbook
On Error Resume Next
If Workbooks.Count > 1 Then MsgBox "关闭其他工作簿!": Exit Sub
'----------设置父文件夹路径-----
mPath = "D:\Project overview 2015"
fA = Dir(mPath & "\*", vbDirectory) '开始收集子目录名称
k = 0
Do While fA <> ""
If fA <> "." And fA <> ".." Then
If (GetAttr(mPath & "\" & fA) And vbDirectory) = vbDirectory Then
k = k + 1
mAry(k) = fA
End If
End If
fA = Dir
Loop
'--------------遍历各子目录--------
Application.DisplayAlerts = False
For i = 1 To k
fA = Dir(mPath & "\" & mAry(i) & "\*.xls*")
Do While fA <> ""
If fA <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(mPath & "\" & mAry(i) & "\" & fA, , False) '打开
wb.Save '保存
wb.Close True '关闭
End If
fA = Dir
Loop
Next i
Application.DisplayAlerts = True
MsgBox "处理完成!"
End Sub