'Alan dot Kaplan at VA dot gov, 3/5/14.  
'This script lets you combine separate Excel files with only one worksheet into 
'workbook with multiple sheets.  

'Based on vba code by Norie posted at 
'http://www.mrexcel.com/forum/excel-questions/298659-using-visual-basic-applications-scripts-combine-multiple-workbooks-single-worksheet-single-workbook-multiple-worksheets.html
Option Explicit
Dim wbDst 'As Workbook
Dim wbSrc 'As Workbook
Dim wsSrc 'As Worksheet
Dim MyPath 'As String
Dim strFilename 'As String
	If WScript.Arguments.Unnamed.Count = 0 Then
	MsgBox "Drag and drop the files to combine onto this script",vbOKOnly + vbExclamation,"How it works"
	WScript.Quit
End If 

const xlWorksheet = -4167 
Dim oExcelApp
On Error Resume Next 
Set oExcelApp = CreateObject("Excel.Application") 
If Err <> 0 Then 
	MsgBox "This requires Excel!",vbOKOnly + VbCritical,"Doh!"
	WScript.Quit
End If 

oExcelApp.Visible = True
oExcelApp.DisplayAlerts = False
oExcelApp.EnableEvents = False
oExcelApp.ScreenUpdating = False

Dim aFiles, file
set aFiles =  WScript.Arguments.Unnamed
Set wbDst = oExcelApp.Workbooks.Add(xlWorksheet)


For Each file In aFiles
        Set wbSrc = oExcelApp.Workbooks.Open(file)   
        Set wsSrc = wbSrc.Worksheets(1)     
        wsSrc.Copy,(wbDst.Worksheets(wbDst.Worksheets.Count))
        wbSrc.Close False
Next

wbDst.Worksheets(1).Delete
oExcelApp.DisplayAlerts = True
oExcelApp.EnableEvents = True
oExcelApp.ScreenUpdating = True