merge all excel worksheets in workbook files into a new workbook with a single worksheet.

excel-merge-sheet.js

/**
 * copyright: gholk 2022 (http://gholk.github.io/contact.html)
 * license: gplv3
 * install: copy this file to explorer.exe shell:sendto
 * require: Excel.Application and wscript or cscript
 * description: merge all excel worksheets in workbook files into a new workbook with a single worksheet.
 * usage:
 *   - right click on xls(x) file(s) and select this script in send-to sub-menu
 *   - or run this script with cscript and pass xls files as arguments
 *   - or drag and drop files onto this script
 */

function getArgv(i) {
	var wa = WScript.Arguments
	if (i < 0) return wa.Count()
	else if (i == 0) return WScript.ScriptFullName
	else return wa(i-1)
}

var libExcel = {
	WScript: WScript,
	init: function () {
		this.fso = this.WScript.CreateObject(
			'Scripting.FileSystemObject'
		)
		// this.shell = this.WScript.CreateObject('Wscript.Shell')
		this.cwd = this.fso.GetAbsolutePathName('.')
		this.boundary = this.generateRandom(16)
		this.excel = this.WScript.CreateObject('Excel.Application')
		this.excel.Visible = true
	},
	generateRandom: function (length) {
		var s = ''
		while (s.length < length) {
			s += String(Math.random()).slice(2)
		}
		return s.slice(0, length)
	},
	open: function (path) {
		if (path.charAt(0) != '\\' && !/^[a-z]:\\/i.test(path)) {
			path = this.fso.BuildPath(this.cwd, path)
		}
		return this.excel.Workbooks.Open(path)
	},
	walkSheet: function (callback, book) {
		var sheets = book.Worksheets
		for (var i=1; i<=sheets.Count; i++) {
			var sheet = sheets(i)
			var name = sheet.Name
			callback.call(this, sheet, name, book)
		}
	},
	xlCSVUTF8: 62,
	xlCSV: 6,
	alert: function (s) {
		this.WScript.Echo(s)
	},
	mergeSheet: function (sourceList, to) {
		var rowCursor = this.excel.ActiveCell.Row
		for (var i=0; i<sourceList.length; i++) {
			var sheet = sourceList[i]
			to.Cells(rowCursor, 1).Value = 'source'
			var fullpath = sheet.Parent.Path + '\\' + sheet.Parent.Name
			to.Cells(rowCursor, 2).Value = fullpath
			to.Cells(rowCursor, 3).Value = sheet.Name
			to.Cells(rowCursor, 4).Value = 'boundary=' + this.boundary
			if (!sheet.Visible) {
				to.Cells(rowCursor, 5).Value = 'visible=false'
			}
			rowCursor++
			
			var usedRange = sheet.UsedRange
			var h = usedRange.Rows.Count
			var w = usedRange.Columns.Count
			to.Range(
				to.Cells(rowCursor, 1),
				to.Cells(rowCursor+h-1, w)
			).Value = usedRange.Value
			rowCursor += h+1
			to.Cells(rowCursor, 1).Activate()
		}
	},
	fileExist: function (path) {
		try {
			this.fso.GetFile(path)
		}
		catch (error) {
			return false
		}
		return true
	},
	getOutPutDir: function () {
		if (this.WScript.FullName.match(/WScript(\.\w+)?$/i)) {
			// run from context menu send-to
			return getArgv(1).replace(/\\[^\\]*?$/, '')
		}
		else return this.cwd
	},
	run: function () {
		var count = getArgv(-1)
		var bookNew = this.excel.Workbooks.Add()
		var to = bookNew.Worksheets(1)
		for (var i=1; i<=count; i++) {
			var path = getArgv(i)
			var book = this.open(path)
			to.Activate()
			var sheetList = []
			this.walkSheet(function (sheet) {
				sheetList.push(sheet)
			}, book)
			this.mergeSheet(sheetList, to)
			book.Close()
		}
		var savePath = this.getOutPutDir() + '\\merge.xlsx'
		if (!this.fileExist(savePath)) {
			bookNew.SaveAs(savePath)
			this.alert('finished')
		}
		else this.alert('finished but not saved. file already exist')
	}
}

function repl() {
	while (true) {
		var line = WScript.StdIn.ReadLine()
		if (line == '.exit') break
		var result
		try {
			result = eval(line)
		}
		catch (error) {
			result = error.message
		}
		libExcel.alert(String(result))
	}
}

libExcel.init()
libExcel.run()