excelvba文件合并|怎样用vba合并同一目录下所有excel文件

excelvba文件合并|怎样用vba合并同一目录下所有excel文件的第1张示图

⑴ EXCEL VBA合并内容

Sheet5.[J3] = Sheet5.Cells(4 ^ 8, 3).End(3).Offset(-0) & Sheet5.Cells(4 ^ 8, 6).End(3).Offset(-0)用&连接即可。

⑵ excel 多文件合并表单 vba 宏 问题

可以在 Wend 后多加一段For Each xSh In ThisWorkbook.Worksheets If Len(xSh.Name) > 2 Then xSh.Name = Right(xSh.Name, Len(xSh.Name) – 2) End IfNext

⑶ 用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文件

合并的代码如下: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

⑸ EXCEL VBA 文本合并问题

不要使用单引号,修正程序代码:

Sub合并()DimExtraNum1AsLong,ExtraNum2AsLong,ExtraNum3AsLongExtraNum1=ActiveSheet.Range("B1").ValueExtraNum2=ActiveSheet.Range("B2").ValueExtraNum3=ActiveSheet.Range("B3").ValueActiveSheet.Range("K5").Value=ExtraNum1&"|"&ExtraNum2&"|"&ExtraNum3ActiveSheet.Range("K6:K"&8).FormulaR1C1="=RC&""|""&RC2&""|""&RC3&""|""&RC4&""|""&RC5&""|""&RC6&""|""&RC7&""|""&RC8&""|""&RC9&""|""&RC10"EndSub

示例:

⑹ VBA- Excel 合并多个文件

建议在最前面加一个变量定义Dim sheet As Worksheet

⑺ 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 如何合并两个文件的两列合并成一列

把sheet2和sheet3合并到sheet1Sheets("Sheet2").Select 选中sheet2 Range("A2:B9").Select 选中要拷贝的范围 Selection.Copy 拷贝 Sheets("Sheet1").Select 选中sheet1 Range("A2").Select 选中要粘贴的范围。 ActiveSheet.Paste 粘贴 Sheets("Sheet3").Select 选中sheet3 Range("A2:B9").Select 选中要拷贝的范围 Selection.Copy 拷贝 Sheets("Sheet1").Select 选中sheet1 Range("C2").Select 选中要粘贴的范围 ActiveSheet.Paste 粘贴 Range("E2").Select

⑼ 怎样用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

⑽ 用vba合并excel多个文件

加上这句可以删除活动工作表第一列的空白单元格所在行ActiveSheet.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete加上这句可以删除活动工作表第一列的文本单元格所在行ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants,xlTextValues).EntireRow.Delete

未经允许不得转载:山九号 » excelvba文件合并|怎样用vba合并同一目录下所有excel文件

赞 (0)