You can work around this problem using one of the following methods depending on the version of Excel you use in your environment:
Excel 2007 or Excel 2010: Use one of the following methods:
Method 1: Use Ctrl+C and Ctrl+V combination with the following steps:
- Copy the source data by pressing Ctrl+C in the source workbook.
- In the new workbook use one of the following methods to retain source formatting:
- Right click and choose Paste Special option from the Edit Menu and then paste the data by selecting Using all source theme option.
or - After pressing Ctrl+V in the new worksheet choose Keep source formatting in Paste Options.
One caveat that you need to be aware of when using this method is that the row height and column width may not carry over to the new workbook. To keep row height and column width, you can first do
Move or Copy Sheet to the new workbook to ensure column width and row height are acceptable and then copy the content using source format again.
Method 2:Use
Move or Copy Sheet operations with the following additional steps:
After doing
Move or Copy Sheet to copy the sheet to another workbook do the following:
- Go to File menu and select Options in the left pane.
- In the Excel Options page, select Save option.
- Click Colors… button under Preserve visual appearance of the workbook.
- In the Color page, in the Copy colors from list box, select the source excel file that is exported from SQL Server Reporting Services.
- Click OK twice to close all the option windows.
You can also automate this workaround using a Macro Code that is similar to the following:
Sub Sample()
Sheets("OriginalExcelSheetExportedFromReport").Select
Sheets("OriginalExcelSheetExportedFromReport ").Copy
ActiveWorkbook.Colors = Workbooks("OriginalExcelSheetExportedFromReport.xls").Colors
End Sub
: Use
Move or Copy Sheet operations with the following additional steps:
After doing
Move or Copy sheet to copy the sheet to another workbook do the following:
- Go to Tools menu and click Options.
- Select the Color tab, and in the Copy colors from list box, select the source excel file that is exported from SQL Server Reporting Services. Click OK to close the dialog box.
