title: Merging multiple excel files into one
body: <p>Here's the problem, I have several excel files that are generated, each has 1 tab (but that's not to say some won't have more in the future).</p>
  
  <p>What I need to write, is a process that can open each file, and copy all of it's worksheets (tabs) into a new file.  </p>
  
  <p>In the end, that new file should contain the worksheets of all the other files.</p>
  
  <p>Currently, I have created the following to accomplish converting between excel formats.</p>
  
  <p>I'm not too sure where to go from here, I don't have any of my sources I used to create this... and am not too sure on the object model (in order to copy the tabs to a new file) or the issues I'll face with com and making sure I keep everything cleaned up.</p>
  
  <pre><code>        object excelApplication = null;
          object workbook = null;
          object workbooks = null;
  
          try
          {
              // Get the Remote Type
              var excelType = Type.GetTypeFromProgID("Excel.Application", Properties.Settings.Default.ExcelServer, true);
  
              // Instantiate the type
              excelApplication = Activator.CreateInstance(excelType);
  
              // Turn off Prompts
              excelApplication.GetType().InvokeMember(
                  "DisplayAlerts",
                  BindingFlags.SetProperty,
                  null,
                  excelApplication,
                  new Object[] { false });
  
              // Get a reference to the workbooks object
              workbooks = excelApplication.GetType().InvokeMember(
                  "Workbooks",
                  BindingFlags.GetProperty,
                  null,
                  excelApplication,
                  null);
  
              // Open the input file
              workbook = workbooks.GetType().InvokeMember(
                  "Open",
                  BindingFlags.InvokeMethod,
                  null,
                  workbooks,
                  new object[] { inputFilePath });
  
              // If overwrite is turned off, and the file exist, the save as line will throw an error
              if (File.Exists(outputFilePath) &amp;&amp; overwriteIfExists)
              {
                  File.Delete(outputFilePath);
              }
  
              // Save the workbook
              workbook.GetType().InvokeMember(
                  "SaveAs",
                  BindingFlags.InvokeMethod,
                  null,
                  workbook,
                  new object[] { outputFilePath, saveAsFileFormat, null, null, null, null, 1, null, null, null, null, null });
          }
          finally
          {
              // Cleanup all created COM objects
              if (workbook != null)
              {
                  workbook.GetType().InvokeMember(
                      "Close",
                      BindingFlags.InvokeMethod,
                      null,
                      workbook,
                      null);
                  Marshal.ReleaseComObject(workbook);
                  workbook = null;
              }
  
              if (workbooks != null)
              {
                  Marshal.ReleaseComObject(workbooks);
                  workbooks = null;
              }
  
              if (excelApplication != null)
              {
                  excelApplication.GetType().InvokeMember(
                      "Quit",
                      BindingFlags.InvokeMethod,
                      null,
                      excelApplication,
                      null);
                  Marshal.ReleaseComObject(excelApplication);
                  excelApplication = null;
              }
          }
  </code></pre>
  
  <p>EDIT: This code is nearly working, the issue is on the line that should actually perform the copy...</p>
  
  <pre><code>    public byte[] MergeFiles(FileFormat saveAsFileFormat, List&lt;byte[]&gt; inputFileBytesList)
      {
          var outputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + ".target.xls");
  
          Impersonate(
              Properties.Settings.Default.ImpersonationUser.Decrypt(),
              Properties.Settings.Default.ImpersonationDomain,
              Properties.Settings.Default.ImpersonationPassword.Decrypt()
          );
  
          var convertedFileList = new List&lt;string&gt;();
          foreach (var inputFileBytes in inputFileBytesList)
          {
              var inputFileExtension = GetExtension(inputFileBytes);
              var inputFilePath = Path.Combine(Properties.Settings.Default.WorkingFolder, Guid.NewGuid() + inputFileExtension);
              var convertedFileBytes = SaveAs(saveAsFileFormat, inputFileBytes);
              File.WriteAllBytes(inputFilePath, convertedFileBytes);
              convertedFileList.Add(inputFilePath);
          }
  
          // Target Excel File
          object targetExcelApplication = null;
          object targetWorkbook = null;
          object targetWorkbooks = null;
          object targetWorksheets = null;
          object targetWorksheet = null;
  
          try
          {
              // Get the Remote Type
              var excelType = Type.GetTypeFromProgID("Excel.Application", Properties.Settings.Default.ExcelServer, true);
  
              // Instantiate the type
              targetExcelApplication = Activator.CreateInstance(excelType);
  
              // Turn off Prompts
              targetExcelApplication.GetType().InvokeMember(
                  "DisplayAlerts",
                  BindingFlags.SetProperty,
                  null,
                  targetExcelApplication,
                  new Object[] { false });
  
              // Get a reference to the workbooks object
              targetWorkbooks = targetExcelApplication.GetType().InvokeMember(
                  "Workbooks",
                  BindingFlags.GetProperty,
                  null,
                  targetExcelApplication,
                  null);
  
              // Create a workbook to add the sheets to
              targetWorkbook = targetWorkbooks.GetType().InvokeMember(
                  "Add",
                  BindingFlags.InvokeMethod,
                  null,
                  targetWorkbooks,
                  new object[] { 1 });
  
              // Get a reference to the worksheets object
              targetWorksheets = targetWorkbook.GetType().InvokeMember(
                  "Sheets",
                  BindingFlags.GetProperty,
                  null,
                  targetExcelApplication,
                  null
                  );
  
              foreach (var inputFilePath in convertedFileList)
              {
                  // Open each File, grabbing all tabs
                  object sourceExcelApplication = null;
                  object sourceWorkbooks = null;
                  object sourceWorkbook = null;
                  object sourceWorksheets = null;
  
                  try
                  {
                      // Instantiate the type
                      sourceExcelApplication = Activator.CreateInstance(excelType);
  
                      // Turn off Prompts
                      sourceExcelApplication.GetType().InvokeMember(
                          "DisplayAlerts",
                          BindingFlags.SetProperty,
                          null,
                          sourceExcelApplication,
                          new Object[] {false});
  
                      // Get a reference to the workbooks object
                      sourceWorkbooks = sourceExcelApplication.GetType().InvokeMember(
                          "Workbooks",
                          BindingFlags.GetProperty,
                          null,
                          sourceExcelApplication,
                          null);
  
                      // Open the input file
                      sourceWorkbook = sourceWorkbooks.GetType().InvokeMember(
                          "Open",
                          BindingFlags.InvokeMethod,
                          null,
                          sourceWorkbooks,
                          new object[] {inputFilePath});
  
                      // Get a reference to the worksheets object
                      sourceWorksheets = sourceWorkbook.GetType().InvokeMember(
                          "Sheets",
                          BindingFlags.GetProperty,
                          null,
                          sourceExcelApplication,
                          null);
  
                      var sourceSheetCount = (int)(sourceWorksheets.GetType().InvokeMember(
                          "Count",
                          BindingFlags.GetProperty,
                          null,
                          sourceWorksheets,
                          null));
  
                      for (var i = 1; i &lt;= sourceSheetCount; i++)
                      {
                          var targetSheetCount = (int)(targetWorksheets.GetType().InvokeMember(
                              "Count",
                              BindingFlags.GetProperty,
                              null,
                              targetWorksheets,
                              null));
  
                          var sourceWorksheet = sourceWorksheets.GetType().InvokeMember(
                              "Item",
                              BindingFlags.GetProperty,
                              null,
                              sourceWorksheets,
                              new Object[] { i });
  
                          targetWorksheet = targetWorksheets.GetType().InvokeMember(
                              "Item",
                              BindingFlags.GetProperty,
                              null,
                              targetWorksheets,
                              new Object[] { targetSheetCount });
  
                          // TODO: Copy into target file
  
                          sourceWorksheet.GetType().InvokeMember(
                              "Copy",
                              BindingFlags.InvokeMethod,
                              null,
                              sourceWorksheet,
                              new[] { Type.Missing, targetWorksheet }
                              );
  
                          if (sourceWorksheet != null)
                          {
                              Marshal.ReleaseComObject(sourceWorksheet);
                              sourceWorksheet = null;
                          }
                      }
                  }
                  finally
                  {
                      // Cleanup all created COM objects
                      if (sourceWorksheets != null)
                      {
                          Marshal.ReleaseComObject(sourceWorksheets);
                          sourceWorksheets = null;
                      }
  
                      if (sourceWorkbook != null)
                      {
                          sourceWorkbook.GetType().InvokeMember(
                              "Close",
                              BindingFlags.InvokeMethod,
                              null,
                              sourceWorkbook,
                              null);
                          Marshal.ReleaseComObject(sourceWorkbook);
                          sourceWorkbook = null;
                      }
  
                      if (sourceWorkbooks != null)
                      {
                          Marshal.ReleaseComObject(sourceWorkbooks);
                          sourceWorkbooks = null;
                      }
  
                      if (sourceExcelApplication != null)
                      {
                          sourceExcelApplication.GetType().InvokeMember(
                              "Quit",
                              BindingFlags.InvokeMethod,
                              null,
                              sourceExcelApplication,
                              null);
                          Marshal.ReleaseComObject(sourceExcelApplication);
                          sourceExcelApplication = null;
                      }
                  }
              }
  
              // If overwrite is turned off, and the file exist, the save as line will throw an error
              if (File.Exists(outputFilePath))
              {
                  File.Delete(outputFilePath);
              }
  
              // Save the workbook
              targetWorkbook.GetType().InvokeMember(
                  "SaveAs",
                  BindingFlags.InvokeMethod,
                  null,
                  targetWorkbook,
                  new object[] { outputFilePath, saveAsFileFormat, null, null, null, null, 1, null, null, null, null, null });
          }
          finally
          {
              // Cleanup all created COM objects
              if (targetWorksheets != null)
              {
                  Marshal.ReleaseComObject(targetWorksheets);
                  targetWorksheets = null;
              }
  
              if (targetWorkbook != null)
              {
                  targetWorkbook.GetType().InvokeMember(
                      "Close",
                      BindingFlags.InvokeMethod,
                      null,
                      targetWorkbook,
                      null);
                  Marshal.ReleaseComObject(targetWorkbook);
                  targetWorkbook = null;
              }
  
              if (targetWorkbooks != null)
              {
                  Marshal.ReleaseComObject(targetWorkbooks);
                  targetWorkbooks = null;
              }
  
              if (targetExcelApplication != null)
              {
                  targetExcelApplication.GetType().InvokeMember(
                      "Quit",
                      BindingFlags.InvokeMethod,
                      null,
                      targetExcelApplication,
                      null);
                  Marshal.ReleaseComObject(targetExcelApplication);
                  targetExcelApplication = null;
              }
          }
  
          // Read target file bytes
          var resultBytes = (File.Exists(outputFilePath))
              ? File.ReadAllBytes(outputFilePath)
              : new byte[] { };
  
          // Delete working files
          if (File.Exists(outputFilePath))
              File.Delete(outputFilePath);
          foreach (var inputFilePath in convertedFileList.Where(File.Exists))
          {
              File.Delete(inputFilePath);
          }
  
          Repersonate();
  
          // Return result
          return resultBytes;
      }
  </code></pre>
  
  <p>I get the error <em>System.Runtime.InteropServices.COMException: Copy method of Worksheet class failed</em>, which doesn't help much...I don't know why it failed...</p>