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