Shulou(Shulou.com)06/03 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.