![如何将多张 Excel 文件合并为一个 Excel 文件?](https://linux22.com/image/1404080/%E5%A6%82%E4%BD%95%E5%B0%86%E5%A4%9A%E5%BC%A0%20Excel%20%E6%96%87%E4%BB%B6%E5%90%88%E5%B9%B6%E4%B8%BA%E4%B8%80%E4%B8%AA%20Excel%20%E6%96%87%E4%BB%B6%EF%BC%9F.png)
我需要将数百个 Excel 文件合并为一个 Excel 文件。每个 Excel 文件都需要合并到工作簿中各自的工作表中,并且目标工作表需要具有原始文件的名称(减去扩展名)。这可能吗?
答案1
好吧,看起来你并没有为此付出任何努力,但由于我已经编写了这些宏,因此我会将它们提供给其他正在搜索的人。这些是在 excel 2007 中编写的,是更大流程的一部分。
需要注意的是,如果任何文件名超过 31 个字符,此操作将失败,因为 Excel 对工作表名称有字符限制
将文件合并为一个,并将工作表的名称设置为文件名 -
Sub CombineWSs()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
MyPath = "C:\Documents and Settings\path\to"
Set wbDst = ThisWorkbook
strFilename = Dir(MyPath & "\*.xls", vbNormal)
If Len(strFilename) = 0 Then Exit Sub
Do Until strFilename = ""
Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
Set wsSrc = wbSrc.Worksheets(1)
wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
wbSrc.Close False
strFilename = Dir()
Loop
wbDst.Worksheets(1).Delete
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
现在遍历工作表以删除工作表名称的最后五个字符:.xlsx
Sub RenameWS()
Application.ScreenUpdating = False
Dim strName As String
Dim intLength As Integer
For Each Sheet In ActiveWorkbook.Worksheets
strName = Sheet.Name
intLength = Len(strName)
strName = Left(strName, intLength - 5)
Sheet.Name = strName
Next
Application.ScreenUpdating = True
End Sub