Combining two .xlsx worksheets in separate xlsx files into a single worksheet in a new workbook

Question

I need to accomplish this with vbscript (not vba). I cannot find any examples of how to do this. I've got vbs that does all of the major processing to the Excel files I need, but the last piece I need is simply to merge both worksheets which reside in 2 separate .xlsx files into a single worksheet into a new workbook.

I've found examples of merging 2 files into a single wb with separate sheets using vba, but I need them on the same sheet and via vbscript. It would basically be like a union of the two sheets. They both contain the same number of columns (6 columns) with the same type of data. Basically need to copy the headers from either of the spreadsheets and paste into new workbook/worksheet, then copy all of the data from each into the new workbook/sheets below the headers. Hope this makes sense. Any help is appreciated.

I'm semi close with this, this brings the two data sheets into the same NEW workbook called "merged", but need it to merge the sheets into one.

    Set objExcel = WScript.CreateObject ("Excel.Application")

objExcel.Visible = false

strFileName = "c:\excel\merged.xlsx"

 Set objWbA = objExcel.WorkBooks.open("c:\excel\wb1.xlsx")
 Set objWbB = objExcel.WorkBooks.open("c:\excel\wb2.xlsx")

Set objWorkbook = objExcel.Workbooks.Add()

 objwba.worksheets(1).copy _
 objWorkbook.worksheets(1)
 objwbb.worksheets(1).copy _
 objWorkbook.worksheets(2)

objWorkbook.SaveAs(strFileName)
objWorkbook.close

objWbA.Close True
objWbB.Close True

objExcel.Quit
Set objExcel = Nothing

==========================

Here is a solution I came up with (went with a CSV output):

Option Explicit
Dim objExcel
Dim strFilename
Dim objWbA
Dim objWbB
Dim Lastrow
Dim Lastrow1
Dim objWorkbook
Dim objSheeta
Dim objSheetb

Set objExcel = WScript.CreateObject ("Excel.Application")

objExcel.Visible = false
objExcel.displayalerts = false

strFileName = "c:\excel\merged.csv"

 Set objWbA = objExcel.WorkBooks.open("c:\excel\wb1.xlsx")

 Set objSheeta = objWbA.Sheets("wb1")

 Set objWbB = objExcel.WorkBooks.open("c:\excel\wb2.xlsx")

 Set objSheetb = objWbB.Sheets("wb2")

Set objWorkbook = objExcel.Workbooks.Add()

Const xlUp = -4162
Const xlPasteValues = -4163
Const xlPasteFormats = -4122
Const xlPasteValuesAndNumberFormats = 12

with objSheeta
Lastrow = .Cells(objSheeta.Rows.Count, 1).End(xlUp).Row
      .Range("B1:F" & Lastrow).Copy
end with

objWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

with objSheetb
Lastrow1 = .Cells(objSheetb.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      .Range("B2:F" & Lastrow1).Copy
end with

objWorkbook.Worksheets("Sheet1").Range("A" & Lastrow1).PasteSpecial xlPasteValuesAndNumberFormats

'===================================

objExcel.CutCopyMode = False
objExcel.ScreenUpdating = True

objWorkbook.SaveAs(strFileName), 6
objWorkbook.close True

objWbA.Close True
objWbB.Close True

objExcel.Quit
Set objExcel = Nothing

Answer 1

If you perform a Worksheet.Copy method and neglect to supply a destination, the worksheet will be copied to a new workbook which holds the ActiveWorkbook property. This may be the best way to start off a new workbook.

Set objExcel = WScript.CreateObject ("Excel.Application")

objExcel.Visible = False    'True for testing

strFileName = "c:\tmp\merged"   '<~~ no file extension, FileType:=51 (xlOpenXMLWorkbook) will do that

 Set objWbA = objExcel.WorkBooks.open("c:\tmp\wb1.xlsx")
 Set objWbB = objExcel.WorkBooks.open("c:\tmp\wb2.xlsx")
 rws = objWbA.Worksheets(1).Rows.Count   '<~~ 65536 or 1048576 (need this below)

 objWbA.Worksheets(1).Copy   '<~~ copy to a new workbook with one worksheet
 objWbB.Worksheets(1).Cells(1, 1).CurrentRegion.Copy _
    objExcel.ActiveWorkbook.Worksheets(1).Cells(rws, 1).End(-4162).Offset(1,0)   '-4162 is xlUp

objExcel.ActiveWorkbook.SaveAs strFileName, 51  '<~~ 51 is FileType:=xlOpenXMLWorkbook
objExcel.ActiveWorkbook.Close False   '<~~ saved on the line immediately above

objWbA.Close False   'don't save if we didn't change anything
objWbB.Close False   'don't save if we didn't change anything

objExcel.Quit
Set objExcel = Nothing

The Workbook.SaveAs method will supply the correct file extension if you provide the correct value from the XlFileFormat Enumeration for the FileType. There is no need to save the originals as they received no changes.