用VBA合并Excel工作簿.docxVIP

用VBA合并Excel工作簿 有许多实现Excel工作簿合并的方法,在《将多个工作簿中的数据合并到一个工作簿》中介绍过 例如,需要将多个Excel工作簿中的工作表合并到一个工作簿。这里假设需要合并的工作簿在“D:\\示例 Sub CombineWorkbooks( Dim strFileName As String Dim wb As Workbook Dim ws As Object 包含工作簿的文件夹,可根据实际修改 Const strFileDir As String = \示例\\数据记录\\\Application.ScreenUpdating = False Set wb = Workbooks.Add(xlWorksheet strFileName = Dir(strFileDir \Do While strFileName vbNullString Dim wbOrig As Workbook Set wbOrig = Workbooks.Open(Filename:=strFileDir strFileName, ReadOnly:=True strFileName = Left(Left(strFileName, Len(strFileName - 4, 29 For Each ws In wbOrig.Sheets ws.Copy After:=wb.Sheets(wb.Sheets.Count If wbOrig.Sheets.Count 1 Then wb.Sheets(wb.Sheets.Count.Name = strFileName ws.Index Else wb.Sheets(wb.Sheets.Count.Name = strFileName End If Next wbOrig.Close SaveChanges:=False strFileName = Dir Loop Application.DisplayAlerts = False wb.Sheets(1.Delete Application.DisplayAlerts = True Application.ScreenUpdating = True Set wb = Nothing End Sub 示例文档下载: 下面是合并多个Excel工作簿的另一种情形,也是《Excel VBA实战技巧精粹》中 有四个工作簿,分别为:汇总工作簿.xls、一月.xls、二月.xls、三月.xls,其中一月.xls、二月.xls 在“汇总工作簿.xls”中打开VBE,并输入下列代码: Sub ConsolidateWorkbook( Dim RangeArray( As String Dim bk As Workbook Dim sht As Worksheet Dim WbCount As Integer WbCount = Workbooks.Count ReDim RangeArray(1 To WbCount - 1 For Each bk In Workbooks 在所有工作簿中循环 If Not bk Is ThisWorkbook Then非代码所在工作簿 Set sht = bk.Worksheets(1 引用工作簿的第一个工作表 i = i + 1 RangeArray(i = \ sht.Range(\Next Worksheets(1.Range(\RangeArray, xlSum, True, True End Sub 运行上述代码前,必须打开所有的工作簿。运行代码后,将自动汇总所有工作表中的值并将相应 示例文档下载: 下面是汇总多个工作簿的又一种情形,也是一名网友提出的问题:在同一文件夹中有多个工作簿,其中 Sub UnionWorksheets( Application.ScreenUpdating = False Dim lj As String Dim dirname As String Dim nm As String lj = ActiveWorkbook.Path nm = ActiveWorkbook.Name dirname = Dir(lj \Cells.Clear Do While dirname \If dirname nm Then Workbooks.Open Filename:=lj \Wo