vba小组件-打开多个文件并进行操作的壳子

Option Explicit
Sub test()
Dim arr()
Dim wb As Workbook

arr = Application.GetOpenFilename("Excel文件,*.xls*", 2, , , True)      //关于GetOpenFilename的用法可以再看看这个，Application.GetOpenFilename 方法 (Excel) | Microsoft Docs

If arr(1) <> "False" Then '如果打开的第一个文件不为False，证明用户有选东西，继续执行下一步。
For i = LBound(arr) To UBound(arr) '打开每个文件'从数组的下标循环到数组的上标
Set wb = Workbooks.Open(arr(i)) '.....









wb.Close '关闭文件
Next
End If

End Sub

 

一个是完整案例

 

 

Sub test()
Dim str()
Dim i As Integer
Dim wb, wb1 As Workbook
Dim sht As Worksheet

On Error Resume Next '这一句上课时候没加，加上以后防止点了取消发生的错误
Set wb1 = ActiveWorkbook
Set sht1 = ActiveSheet
On Error Resume Next
str = Application.GetOpenFilename("Excel数据文件,*.xls*", , , , True)

For i = LBound(str) To UBound(str)
Set wb = Workbooks.Open(str(i))
For Each sht In wb.Sheets
sht.Copy after:=wb1.Sheets(wb1.Sheets.Count)
wb1.Sheets(wb1.Sheets.Count).Name = Split(wb.Name, ".")(0) & sht.Name
Next
wb.Close
Next

End Sub