title: Combining two .xlsx worksheets in separate xlsx files into a single worksheet in a new workbook
body: <p>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.</p>
  
  <p>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.  </p>
  
  <p>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.  </p>
  
  <pre><code>    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
  </code></pre>
  
  <p>==========================</p>
  
  <p>Here is a solution I came up with (went with a CSV output):</p>
  
  <pre><code>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" &amp; 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" &amp; Lastrow1).Copy
  end with
  
  objWorkbook.Worksheets("Sheet1").Range("A" &amp; 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
  </code></pre>