费了不少时间,但总算搞定了,试一下,如果excel文件多于一行,还需要稍微修改一下.
Const xlWorkbookNormal = -4143
Const xlSaveChanges = 1
objStartFolder = "c:\test" "要读取的源文件目录
desExcel= "c:\result1.xls" "最后生成的汇总excel
Set ExcelApp = CreateObject("Excel.Application")
Set destbook = ExcelApp.Workbooks.Add "创建空文件
Set objFSO = CreateObject("Scripting.FileSystemObject")"建立filesystemobject
Set objFolder = objFSO.GetFolder(objStartFolder)"获取文件夹
Set colFiles = objFolder.Files "获得源目录下所有文件
intRow=1 "行数
For Each objFile in colFiles "依次处理文件夹中的文件
If UCase(Right(Trim(objFile.Name), 3)) ="XLS" Then "只处理xls文件
Set srcbook = ExcelApp.Workbooks.Open(objStartFolder + "\" + objFile.Name) "打开xls文件
"srcbook.Worksheets(1).Copy destbook.Worksheets(1)
srcbook.activate
intCol = 1 "列数
Do Until ExcelApp.Cells(1,intCol).Value = ""
tempdata=ExcelApp.Cells(1, intCol).Value
destbook.activate
ExcelApp.Cells(intRow, intCol).Value=tempdata
srcbook.activate
intCol = intCol + 1
Loop
srcbook.Close "关闭已经打开的xls文件
End If
intRow=intRow+1
Next
destBook.SaveAs desExcel,xlWorkbookNormal
destBook.close xlSaveChanges
ExcelApp.quit
这个方法OK
在存放文件的目录之外打开一个空的Excel文档
运行下面分宏：（注意文件目录）
Sub cfl()
Dim fs, f, f1, fc, s, x
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("e:\test\") "存放文件的目录
Set fc = f.Files
For Each f1 In fc
If Right(f1.Name, 3) = "xls" Then
x = x + 1
Workbooks.Open (f1.Path)
For i = 1 To 255
Workbooks(1).Sheets(1).Cells(x, i).Value = _
Workbooks(2).Sheets(1).Cells(1, i).Value
Next
Workbooks(2).Close savechanges:=False
End If
Next
