Excel colours getting distorted when being copied from one template to another using VSTO(C#)

Question

Hi,

We are trying to generate an excel sheet using a template. Normally we would have done have a basic "Save As" to save the file but the file size came up to be too high. So, now we're working on copying the used range of the original workbook to a new workbook.

The data is getting copied, along with the validations and formulae on using the PasteSpecial command, and all data and formatting is the same, other than the cell background and the font colours. They have almost inverted.

Please suggest how could i solve this.

Thank You, Jagannath

Answer 1

Excel use colors from a palette of 56 colors (may be more in Excel 2007/2010) that can be changed using Tools/Options/Color.

You can copy colors from one workbook to another using:

Workbook1.Colors = Workbook2.Colors

By copying the palette, you will of course affect the colors of all UI elements in the target workbook, not just the range you're copying.

Answer 2

It worked this way, the propety Workbook.Colors wasn't there. The foolowing statement did the job for me

wb2.set_Colors(Missing.Value, wb.get_Colors(Missing.Value));

Thank you for the replies everyone.