費(fèi)了不少時(shí)間,但總算搞定了,試一下,如果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 '創(chuàng)建空文件

Set objFSO = CreateObject("Scripting.FileSystemObject")'建立filesystemobject

Set objFolder = objFSO.GetFolder(objStartFolder)'獲取文件夾

Set colFiles = objFolder.Files '獲得源目錄下所有文件

intRow=1 '行數(shù)

For Each objFile in colFiles '依次處理文件夾中的文件

If UCase(Right(Trim(objFile.Name), 3)) ="XLS" Then '只處理xls文件

Set srcbook = ExcelApp.Workbooks.Open(objStartFolder + "\" + objFile.Name) '打開(kāi)xls文件

'srcbook.Worksheets(1).Copy destbook.Worksheets(1)

srcbook.activate

intCol = 1 '列數(shù)

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 '關(guān)閉已經(jīng)打開(kāi)的xls文件

End If

intRow=intRow+1

Next

destBook.SaveAs desExcel,xlWorkbookNormal

destBook.close xlSaveChanges

ExcelApp.quit

這個(gè)方法OK

在存放文件的目錄之外打開(kāi)一個(gè)空的Excel文檔

運(yùn)行下面分宏：（注意文件目錄）

代碼如下:

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

End Sub