Dim CurFile As String
Dim DestWB As Workbook
Dim ws As Object
Const DirLoc As String = "C:\" (改成你实际存放文件的folder)
Application.ScreenUpdating = False
Set DestWB = Workbooks.Add(xlWorksheet)
CurFile = Dir(DirLoc & "*.xls") (.CSV,.PRN等格式类推)
Do While CurFile <> vbNullString
Dim OrigWB As Workbook
Set OrigWB = Workbooks.Open(Filename:=DirLoc & CurFile, ReadOnly:=True)
CurFile = Left(Left(CurFile, Len(CurFile) - 4), 29)
For Each ws In OrigWB.Sheets
ws.Copy After:=DestWB.Sheets(DestWB.Sheets.Count)
If OrigWB.Sheets.Count > 1 Then
DestWB.Sheets(DestWB.Sheets.Count).Name = CurFile & ws.Index
Else
DestWB.Sheets(DestWB.Sheets.Count).Name = CurFile
End If
Next
OrigWB.Close SaveChanges:=False
CurFile = Dir
Loop