内容发布更新时间 : 2024/5/18 9:25:56星期一 下面是文章的全部内容请认真阅读。

使用VBA合并多个Excel工作簿的几个例子

将许多个工作簿中的工作表合并到一个工作薄中,然后对数据进行统计计算,举了几种合并的案例。

Sub 合并工作簿()

Application.DisplayAlerts = False '关闭提示窗口

shes = Application.SheetsInNewWorkbook '工作簿中包含工作表数 Application.SheetsInNewWorkbook = 1 '生成的新工作簿中只有一个工作表 Set newbok = Workbooks.Add '生成新工作簿 Set newshe = newbok.Worksheets(1) '新工作表 s = 1 '从新工作表的第一行写入数据

na = Dir(\需要合并的所有工作表都要事先保存在D盘time文件夹下 Do While na <> \

Set wb = Application.Workbooks.Open(\wb.Worksheets(1).UsedRange.Copy '复制数据 newbok.Activate

Cells(s, 1).Select

ActiveSheet.Paste '执行粘贴

s = newshe.UsedRange.Rows.Count + 1

Cells(s, 1) = wb.Name '写入数据所属的工作簿名字 s = s + 1

wb.Close '关闭工作簿

na = Dir() '取下一个工作簿

Loop

Application.SheetsInNewWorkbook = shes Application.DisplayAlerts = True Range(\End Sub

///把多个工作簿中的第一个工作表中的数据合并到一个工作簿的一个工作表中

Sub Com()

Dim MyPath, MyName, AWbName Dim Wb As Workbook, WbN As String Dim G As Long Dim Num As Long

Dim BOX As String

Application.ScreenUpdating = False MyPath = ActiveWorkbook.Path

MyName = Dir(MyPath & \AWbName = ActiveWorkbook.Name Num = 0

Do While MyName <> \

If MyName <> AWbName Then

Set Wb = Workbooks.Open(MyPath & \ Num = Num + 1

With Workbooks(1).ActiveSheet

.Cells(.Range(\ For G = 1 To Wb.Sheets.Count

Wb.Sheets(G).UsedRange.Copy .Cells(.Range(\ Next

WbN = WbN & Chr(13) & Wb.Name Wb.Close False End With End If MyName = Dir Loop

Range(\

MyName = Dir Loop

Range(\

Application.ScreenUpdating = True

MsgBox \共合并了\个工作薄下的全部工作表。如下：\提示\End Sub

///把多个工作簿中所有工作表合并到一个工作表中

Sub Books2Sheets() '定义对话框变量

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'新建一个工作簿

Dim newwb As Workbook Set newwb = Workbooks.Add

With fd

If .Show = -1 Then

'定义单个文件变量

Dim vrtSelectedItem As Variant

'定义循环变量 Dim i As Integer i = 1

'开始文件检索

For Each vrtSelectedItem In .SelectedItems '打开被合并工作簿

Dim tempwb As Workbook

Set tempwb = Workbooks.Open(vrtSelectedItem)

'复制工作表

tempwb.Worksheets(1).Copy Before:=newwb.Worksheets(i)

'把新工作簿的工作表名字改成被复制工作簿文件名，这儿应用于xls文件，即Excel97-2003的文件，如果是Excel2007，需要改成xlsx

newwb.Worksheets(i).Name = VBA.Replace(tempwb.Name, \

'关闭被合并工作簿

tempwb.Close SaveChanges:=False

i = i + 1

Next vrtSelectedItem End If End With

Set fd = Nothing End Sub

///合并所有的工作簿中的第一个工作表到一个工作簿中 ///求所有工作表指定单元格的和例：=sum(sheet1:sheet8!A1)

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)