多个excel文件内容合并成一个文件表内容
Sub 简单合并()
Dim FS, fils, fil, fol, bname, ename
Set FS = CreateObject("scripting.filesystemobject")
Set fol = FS.getfolder("D:\待合并文件\")
Set fils = fol.Files
Workbooks.Add
Dim st1, st2, st1row, st2row, wb2
Set wb1 = ActiveWorkbook
Set st1 = ActiveSheet
For Each fil In fils
bname = FS.getbasename(fil)
ename = FS.getextensionname(fil)
If InStr(1, "xlsxlsmxlsx", ename, vbTextCompare) Then
Workbooks.Open fil
Set wb2 = ActiveWorkbook
Set st2 = ActiveSheet
If st1.[a1].Value = "" Then
st1.[a1].Value = "来源表格"
st2.Range("a1:z1").Copy st1.[B1]
End If
st1row = st1.Cells(st1.Cells.Rows.Count, 1).End(xlUp).Row
st2row = st2.Cells(st2.Cells.Rows.Count, 1).End(xlUp).Row
st2.Range("A2:Z" & st2row).Copy st1.Cells(st1row + 1, 2)
st1.Range("a" & st1row + 1 & ":a" & st1row + st2row - 1).Value = bname
End If
wb2.Close
Next
End Sub