Color formatting is lost after merging multiple workbooks into one

Question

I have a couple of SSRS reports (.xls files) with different Structures:

• Report 1 has 3 excel sheets inside it.
• Report 2 has 2 excel sheets inside it.
• Report 3 has 4 excel sheets inside it.

I want all these 9 excel sheets to be merged into single Excel file having different sheets, that is, my final excel file should have all these 9 sheets where I am able to achieve using the below macro code.

Path = "C:\Users\FILES\" ' This will fetch all the files form the mentioned location
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
        Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
Loop

Application.DisplayAlerts = False ' Delete an extra sheet
Worksheets("SHEET1").Delete
Application.DisplayAlerts = True

Application.DisplayAlerts = False ' This will create a new excel file and stores all the data in to it.
ThisWorkbook.CheckCompatibility = False
ThisWorkbook.SaveAs Filename:="C:\Users\merge.xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True

Application.DisplayAlerts = False ' Save and exit
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit  

The only problem I am facing is while merging the files my VBA code excluding the colors applied to a few cells in my SSRS reports.

How can I copy the excel files without losing the color format already applied?

Answer 1

Use .Copy and .PasteSpecial, usually those should be avoided, but in this case they seem to be the only solution...

Cause:
The problem occurs because both SQL Server Reporting Services 2008 and SQL Server Reporting services 2008 R2 use a customized Color Palette instead of the built-in palette that ships with Excel. The first 56 unique colors used in the report are defined in a custom palette.

Workaround:
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

Excel 2003: 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.

Source: Microsoft Support 2465477
Similiar Question: https://stackoverflow.com/a/6447743/10223558

While we're at it you could try the code below to avoid ActiveWorkbook

Do While Filename <> ""
    With Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True)
        For Each Sheet In .Sheets
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next Sheet
        .Close
    End With
    Filename = Dir()
Loop

Answer 2

I found out the issue, made a change in the code, added a single line which copies the color from the source file to the new file without any changes.

Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)  

***ActiveWorkbook.Colors = Workbooks(Filename).Colors***