How vbs merges multiple excel files
This article will explain in detail how to merge multiple excel files on vbs, Xiaobian thinks it is quite practical, so share it for everyone to make a reference, I hope you can gain something after reading this article.
The code is as follows:
Const xlWorkbookNormal = -4143
Const xlSaveChanges = 1
objStartFolder = "c:\test" 'Directory of source files to read
desExcel= "c:\result1.xls" 'Last generated summary excel
Set ExcelApp = CreateObject("Excel.Application")
Set destbook = ExcelApp.Workbooks.Add 'Create empty file
Set objFSO = CreateObject("Scripting.FileSystemObject")'Create filesystemobject
Set objFolder = objFSO.GetFolder(objStartFolder)'
Set colFiles = objFolder.Files 'Get all files in the source directory
intRow=1 'Row
For Each objFile in colFiles '
If UCase(Right(Trim(objFile.Name), 3)) ="XLS" Then 'process xls files only
Set srcbook = ExcelApp.Workbooks.Open(objStartFolder + "\" + objFile.Name) 'Open xls file
'srcbook.Worksheets(1).Copy destbook.Worksheets(1)
srcbook.activate
intCol = 1 'Number of columns
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 'Close an already open xls file
End If
intRow=intRow+1
Next
destBook.SaveAs desExcel,xlWorkbookNormal
destBook.close xlSaveChanges
ExcelApp.quit
This method is OK.
Open an empty Excel document outside the directory where the file is stored
Run the following macro: (note file directory)
The copy code is as follows:
Sub cfl()
Dim fs, f, f1, fc, s, x
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("e:\test\") 'Directory where files are stored
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
About "vbs how to merge multiple excel files" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.
