Merge Excel Files into One Using VB Script

• Download MergeExcel.zip - 1 KB

Introduction

Merge Excel files into one on Excel file. The script is using VBA to import worksheets from each file into one workbook. Add file to be merged to MergeExcel.txt and double click MergeExcel.vbs to run it.

Using the Code

MergeExcel.txt - contains the list of files to be merged. Open Windows Explorer hold shift and right click on the Excel file. Select "Copy as path" and copy the Excel file path to MergeExcel.txt (each line per file path).

MergeExcel.vbs - VB Script file. Double click to run it. The script will read MergeExcel.txt located in the same folder.

MergeExcel.vbs source code:

Set fso = CreateObject("Scripting.FileSystemObject")
sFolderPath = GetFolderPath()
sFilePath = sFolderPath & "\MergeExcel.txt"

If fso.FileExists(sFilePath) = False Then
  MsgBox "Could not file configuration file: " & sFilePath
  WScript.Quit
End If

Dim oExcel: Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oExcel.DisplayAlerts = false
Set oMasterWorkbook = oExcel.Workbooks.Add()
Set oMasterSheet = oMasterWorkbook.Worksheets("Sheet1")
oMasterSheet.Name = "temp_delete"
oMasterWorkbook.Worksheets("Sheet2").Delete
oMasterWorkbook.Worksheets("Sheet3").Delete

Set oFile = fso.OpenTextFile(sFilePath, 1)   
Do until oFile.AtEndOfStream
  sFilePath = Replace(oFile.ReadLine,"""","")
  
  If fso.FileExists(sFilePath) Then
    Set oWorkBook = oExcel.Workbooks.Open(sFilePath)
    
    For Each oSheet in oWorkBook.Worksheets
      oSheet.Copy oMasterSheet
      'oSht.Move , oSheet
    Next
    
    oWorkBook.Close()
  End If
Loop
oFile.Close

oMasterSheet.Delete
MsgBox "Done"
          
Function GetFolderPath()
    Dim oFile 'As Scripting.File
    Set oFile = fso.GetFile(WScript.ScriptFullName)
    GetFolderPath = oFile.ParentFolder
End Function

History

• 24^th June, 2020: Initial version