Copying Excel worksheets using COM Interop loses cell colors

I have tried various methods of merging worksheets using COM Interop.

Here's what I have ended up with (unnecessary code omitted for brevity):

    public bool CombineWorkBooks(string exportFilePath, IEnumerable<string> filesToMergeFrom, bool deleteRawFiles)
    {
        var success = false;

        Excel.Application xlApp = null;
        Excel.Workbook mergeToWorkbook = null;
        Excel.Workbooks mergeToWorkbooks = null;
        Excel.Sheets mergeToWorksheets = null;
        Excel.Worksheet defaultWorksheet = null;

        try
        {
            if (filesToMergeFrom == null)
            {
                return false;
            }

            xlApp = new Excel.Application
                    {
                        DisplayAlerts = false,
                        Visible = false,
                        CutCopyMode = Excel.XlCutCopyMode.xlCopy
                    };

            mergeToWorkbooks = xlApp.Workbooks;
            mergeToWorkbook = mergeToWorkbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            mergeToWorksheets = mergeToWorkbook.Worksheets;

            // Get the reference for the [first] empty default worksheet

            if (mergeToWorksheets.Count > 0)
            {
                defaultWorksheet = mergeToWorksheets[1] as Excel.Worksheet;
            }

            if (defaultWorksheet == null)
            {
                return false;
            }

            var reportSheetIndex = 1;
            var fileMergeCount = 0;

            foreach (var mergeFromFilename in filesToMergeFrom)
            {
                fileMergeCount++;

                Excel.Workbook sourceWorkbook = null;
                Excel.Sheets childSheets = null;

                // Make sure file is still there...

                if (!File.Exists(mergeFromFilename))
                {
                    continue;
                }

                try
                {
                    // Open source file

                    sourceWorkbook = mergeToWorkbooks.Open(mergeFromFilename,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing,
                                                           Type.Missing);

                    childSheets = sourceWorkbook.Worksheets;

                    if (childSheets != null)
                    {
                        var sheetsCopiedFromSource = 0;

                        // childSheets.Copy(defaultWorksheet, Type.Missing);

                        for (int iChildSheet = 1; iChildSheet <= childSheets.Count; iChildSheet++)
                        {
                            Excel.Worksheet sourceWorksheet = null;

                            try
                            {
                                sourceWorksheet = childSheets[iChildSheet] as Excel.Worksheet;

                                if (sourceWorksheet != null)
                                {
                                    string name = string.Format(baseSheetName, reportSheetIndex.ToString("D4", new CultureInfo(CultureInfo.CurrentCulture.Name)));

                                    // Assigning the worksheet name

                                    sourceWorksheet.Name = Truncate(name, 31); // only 31 char max

                                    // Copy the worksheet before the default sheet

                                    sourceWorksheet.Copy(defaultWorksheet, Type.Missing);

                                    reportSheetIndex++;
                                    sheetsCopiedFromSource++;
                                }
                            }
                            finally
                            {
                                disposeCOMObject(sourceWorksheet);
                            }
                        }

                        // Close the childbook - for some reason, calling close below may cause an 
                        // exception -> System.Runtime.InteropServices.COMException (0x80010108): The object invoked has disconnected from its clients.

                        try
                        {
                            sourceWorkbook.Close(false, Type.Missing, Type.Missing);
                        }
                        catch (COMException comException)
                        {
                            if (comException.ErrorCode != 0x80010108)
                            {
                                throw;
                            }

                            _messages.Add("Caught COMException, discarding it.");
                        }

                        _messages.Add(string.Format("Successfully copied {0} worksheets from report file: '{1}'", sheetsCopiedFromSource, mergeFromFilename));
                    }
                }
                catch (Exception ex)
                {
                    _messages.Add(string.Format("An error occurred processing file '{0}'.", mergeFromFilename));

                }
                finally
                {
                    disposeCOMObject(childSheets);
                    disposeCOMObject(sourceWorkbook);
                }
            }

            // Delete the empty default worksheet

            defaultWorksheet.Delete();

            // Select the first sheet.

            if (mergeToWorksheets.Count > 0)
            {
                Excel.Worksheet firstSheet = null;

                try
                {
                    firstSheet = mergeToWorksheets[1] as Excel.Worksheet;

                    if (firstSheet != null)
                    {
                        firstSheet.Select(Type.Missing);
                        firstSheet.Range["A1"].Select();
                    }
                }
                finally
                {
                    disposeCOMObject(firstSheet);
                }
            }

            // Determine the file extension

            var fileExt = GetExcelExtension();

            if (string.IsNullOrEmpty(Path.GetExtension(exportFilePath)))
            {
                exportFilePath = string.Format("{0}.{1}", exportFilePath, fileExt);
            }
            else if (Path.GetExtension(exportFilePath) != string.Format(".{0}", fileExt))
            {
                exportFilePath = Path.ChangeExtension(exportFilePath, fileExt);
            }

            _mergeOutputFile = exportFilePath;

            // Save the merged output

            mergeToWorkbook.SaveAs(exportFilePath,
                                   Excel.XlFileFormat.xlWorkbookDefault,
                                   Type.Missing,
                                   Type.Missing,
                                   Type.Missing,
                                   Type.Missing,
                                   Excel.XlSaveAsAccessMode.xlExclusive,
                                   Type.Missing,
                                   Type.Missing,
                                   Type.Missing,
                                   Type.Missing,
                                   Type.Missing);

            // Close file

            mergeToWorkbooks.Close();
            xlApp.DisplayAlerts = true;

            success = true;
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
        finally
        {
            disposeCOMObject(defaultWorksheet);
            disposeCOMObject(mergeToWorksheets);
            disposeCOMObject(mergeToWorkbook);
            disposeCOMObject(mergeToWorkbooks);

            // Quit Excel.

            if (xlApp != null)
            {
                xlApp.Quit();

                disposeCOMObject(xlApp);
            }

            // Only delete source files upon success...

            if (success &&
                deleteRawFiles)
            {
                deleteTemporaryFiles(filesToMergeFrom);
            }

            // Clean up remaining resources

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    }

Now - this works all well and good. BUT... the merge file has the cell/row interior colors all messed up (usually blacks and purples - something a 3 year old might prefer). The source worksheets have some rows with highlighted colors or background (interior) colors other than white.

If I add the following code, I can reset all the cell colors in the merged file to white, but I've not managed with any success to preserve the colors of the original source sheets.

            // Reset the background colour in the cells to white. For some reason the worksheet copy
            // operation screws up the cell backgrounds - pinks, purples and other business-like 
            // colours.

            foreach (Excel.Worksheet targetWorksheet in mergeToWorkbook.Worksheets)
            {
                var usedRange = targetWorksheet.UsedRange;

                usedRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
            }

I ran into the same problem, and found the following Microsoft support note: http://support.microsoft.com/kb/2465477 They mention here a few workarounds. For me the following solution worked: just set the new workbook's color scheme to the old workbook's color scheme, like this:

mergeToWorkbook.Colors = sourceWorkbook.Colors;