⑴ VBA EXCEL合并工作表
好像没有说清楚……到底是合并:一个工作簿的多个工作表;还是多个工作簿的所有工作表(每个工作簿都含有多个工作表)?
⑵ 怎样用vba合并同一目录下所有excel文件
试试下面的代码:
SubMacro1()DimMyPath$,MyName$,shAsWorksheet,m&,wAsWorksheetFunction,wbAsWorkbookApplication.ScreenUpdating=FalseApplication.DisplayAlerts=FalseSetw=WorksheetFunctionMyPath=ThisWorkbook.Path&"采集多数据"MyName=Dir(MyPath&"*.xls*")[a1].CurrentRegion.Offset(1).ClearContentsDoWhileMyName<>""WithGetObject(MyPath&MyName)With.Sheets(1)Ifw.CountA(.UsedRange.Offset(1))Thenm=m+1Ifm=1ThenSetwb=Workbooks.Add(xlWBATWorksheet)Setsh=wb.ActiveSheet.[a1].CurrentRegion.Copysh.[a1]Else.[a1].CurrentRegion.Offset(1).Copysh.[a65536].End(xlUp).Offset(1)EndIfEndIfEndWith.CloseFalseEndWithMyName=DirLoopwb.SaveAsFilename:=ThisWorkbook.Path&"采集多数据20130422.xls",FileFormat:=xlExcel8wb.CloseApplication.ScreenUpdating=TrueMsgBox"ok"EndSub
⑶ 用Excel VBA如何合并同一文件夹下的所有工作簿的第一张工作表
您好!
答案如下,请参考。
比如,下图为公司各部门员工的KPI信息,我们要把它们汇总到同一工作簿中。
此方案主要功能:
⓵单击“合并所有工作簿”命令按钮,系统自动实现合并功能;
⓶执行命令后,系统自动弹出一“提示”对话框,显示合并的工作簿信息;
⓷当部门员工的信息有更改时,再次单击按钮,汇总数据随时刷新。
实现以上功能的代码如下:
Sub CombineWbs()
Dim bt As Range, r As Long, c As Long
r = 1
c = 7
Dim wt As Worksheet
Set wt = ThisWorkbook.Worksheets(1)
wt.Rows(r + 1 & ":1048576").ClearContents
Application.ScreenUpdating = False
Dim FileName As String, sht As Worksheet, wb As Workbook, WbN As String
Dim Erow As Long, fn As String, arr As Variant, Num As Long
FileName = Dir(ThisWorkbook.Path & "*.xlsx")
Num = 0
Do While FileName <> ""
If FileName <> ThisWorkbook.Name Then
Erow = wt.Range("A1").CurrentRegion.Rows.Count + 1
fn = ThisWorkbook.Path & "" & FileName
Set wb = GetObject(fn)
Set sht = wb.Worksheets(1)
Num = Num + 1
arr = sht.Range(sht.Cells(r + 1, "A"), sht.Cells(1048576, "B").End(xlUp).Offset(0, 7))
wt.Cells(Erow, "A").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
WbN = WbN & Chr(13) & wb.Name
wb.Close False
End If
FileName = Dir
Loop
Application.ScreenUpdating = True
MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下:" & Chr(13) & WbN, vbInformation, "提示"
End Sub
希望以上的方法可以帮助到你。谢谢!
⑷ 如何用vba合并多个excel文件到一个sheet里
新建一个工作薄,将所有EXCEL表的文件名填到A列中,复制下面VBA代码到工作薄中,保存为excel.xls后执行Subxls()Application.ScreenUpdating=False'Arr数组由所有EXCEL文件名组成arr=[A1:A10]Fori=1ToUBound(arr)Workbooks.Open"文件夹路径\"&arr(i,1)Workbooks(arr(i)(1)).ActivateForj=1ToActiveWorkbook.Sheets.CountSheets(j).CopyAfter:=Workbooks("excel.xls").Sheets(Workbooks("excel.xls").Sheets.Count)NextWorkbooks(arr(i,1)).CloseFalseNextEndSub
⑸ VBA- Excel 合并多个文件
建议在最前面加一个变量定义Dim sheet As Worksheet
⑹ 一个文件夹中有若干EXCEL,合并多个excel文件,同时文件名称改为列名称,求vba代码,
很简单,但是要求要具体一点:1、合并后的表:第1列是取某张表的第1列,还是所有表的第1列的并集?2、文件夹中的Excel,都是.xls格式的文件吗? 3、合并后的Excel文件保存在哪?
⑺ VBA合并多个结构相同的Excel文件
Sub 合并当前目录下所有工作簿的全部工作表() 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 & "\" & "*.xls") AWbName = ActiveWorkbook.Name Num = 0 Do While MyName <> "" If MyName <> AWbName Then Set Wb = Workbooks.Open(MyPath & "\" & MyName) Num = Num + 1 With Workbooks(1).ActiveSheet .Cells(.Range("A65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) – 4) For G = 1 To Sheets.Count Wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1) Next WbN = WbN & Chr(13) & Wb.Name Wb.Close False End With End If MyName = Dir Loop Range("A1").Select Application.ScreenUpdating = True MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下:" & Chr(13) & WbN, vbInformation, "提示" End Sub
⑻ 怎样合并两个EXCEL文件 vba
打开文件1打开文件2遍历文件2中的sheet,至文件1合并完成save
⑼ vba怎么合并不同的excel文件
合并的代码如下:Sub 合并表格() Dim mypath As String Dim f As String Dim ribao As Workbook Application.ScreenUpdating = False mypath = ThisWorkbook.Path & "/xxx/" f = Dir(ThisWorkbook.Path & "xxx/*.xlsx") Do Workbooks.Open (mypath & f) With ActiveWorkbook .Sheets(1).Move after:=ThisWorkbook.Sheets(Sheets.Count) End With f = Dir Loop Until Len(f) = 0 Application.ScreenUpdating = TrueEnd Sub
未经允许不得转载:山九号 » vba合并excel文件|怎样用vba合并同一目录下所有excel文件