用什麼工具能把多個excel合併成一個excel
- 2022-09-07
合併方法如下:
1。需要把多個excel表都放在同一個資料夾裡面,並在這個資料夾裡面新建一個excel。
2。用microsoft excel開啟新建的excel表,並右鍵單擊sheet1,找到“檢視程式碼”,單擊進去。進去之後就看到了宏計算介面。
3。然後把下面這些宏計算的程式碼複製進去,然後找到工具欄上面的“執行”下的“執行子過程/使用者窗體”,程式碼如下,
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(“B65536”)。End(xlUp)。Row + 2, 1) = Left(MyName, Len(MyName) - 4)
For G = 1 To Sheets。Count
Wb。Sheets(G)。UsedRange。Copy 。Cells(。Range(“B65536”)。End(xlUp)。Row + 1, 1)
Next
WbN = WbN & Chr(13) & Wb。Name
Wb。Close False
End With
End If
MyName = Dir
Loop
Range(“B1”)。Select
Application。ScreenUpdating = True
MsgBox “共合併了” & Num & “個工作薄下的全部工作表。如下:” & Chr(13) & WbN, vbInformation, “提示”
End Sub
4。執行之後,等待10秒針左右,等執行完畢,就是合併完成之後,會有提示,點確定就可以了。檢視合併後的資料,有5000多行,就是同一個資料夾裡面17個excel表資料合併後的結果。
不追求格式,只是機械的複製我寫了小東西,可以試試。
https://pan。baidu。com/s/1FwvDkRS2eFgODJgfYX1HGA
https://download。csdn。net/download/liuxueliuxue/10803602
新建一個工作表,命名後儲存到和與合併的100個檔案同一個檔案資料夾,摁 alt + f11,雙擊工程資源管理器裡面的sheet1(sheet1),在右側的程式碼區貼上如下程式碼。執行。等候一會就ok了。
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
程式碼是網上找的