none
Excel sheet copy looses formatting while copying from XLS to XLSX RRS feed

  • Question

  • Hi all,

    I have a application that copies data from an excel file  (Saved in an directory) sheet (*.XLS) to the current workbook sheet. The copy happens fine but colors and formatting go crazy.

    I also tried saving the XLS file to XLSX first and then copying it to the current worksheet but the same result.

    Is this expected or can i do something about it ?

    Also the data when copied looks crazy (all sort of unacceptable colors come up). what are the options i have so that the data copied looks ok.

    - Girija


    Girija Shankar Beuria

    Friday, April 20, 2012 12:34 AM

Answers

  • That is due to the RGB supported in xls and xlsx. I am not sure but you can try this:  when you do the range copy,  point the the Excel.version= Excel.2k7;  If it is not working that I have no other solution.  Good luck.
    Thursday, April 26, 2012 5:08 AM

All replies

  • Hi their is two ways to allow us with formating Excel Object Modle (need MS Excel)  or 3rd party tool as interface. 

    I am not sure which way you are going with: So you can find help in MSDN http://msdn.microsoft.com/en-us/library/ms178800.aspx

      Or  to See how to Copy Excel worksheet. Good luck.

    Friday, April 20, 2012 8:32 AM
  • Hi Girija,

    Any update?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, April 23, 2012 7:28 AM
    Moderator
  • Hi Green823,

    I am using Range.Copy. This is to ensure that you only copy the range that has data into the current workbook.

    Even if i just copy the worksheet it loses the formatting. the issue is while copying from 2003 Excel to 2007/2010 excel (*.xls to *.xlsx).

    - Girija


    Girija Shankar Beuria

    Since the response was no where close to addressing my question, i have unmarked the response.

    -Girija

    Wednesday, April 25, 2012 6:15 PM
  • Have you seen this article? Might it be the cause of your issue?

    http://support.microsoft.com/kb/2697462


    Kind Regards, Rich ... http://greatcirclelearning.com

    Wednesday, April 25, 2012 7:48 PM
  • Hi rich,

    In my case the code does the copy and no browsers are open as is the problem cause described in the link.

    It is something related to Excel supported colors between versions. But I wanted to know what are my options incase i cannot fully resolve this.

    - Girija


    Girija Shankar Beuria

    Wednesday, April 25, 2012 11:45 PM
  • That is due to the RGB supported in xls and xlsx. I am not sure but you can try this:  when you do the range copy,  point the the Excel.version= Excel.2k7;  If it is not working that I have no other solution.  Good luck.
    Thursday, April 26, 2012 5:08 AM
  • Hi,

    thanks for the reply. But where should i mention the version ?

    Below is my code. I am not able to figure out how to set this Excel.Version.

    - Girija

    var destinationWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
                var destinationWorksheet = (Excel.Worksheet)destinationWorkbook.Sheets[1];
                destinationWorksheet.Range["A1"].Value = "Test";
                var sourceWorkbook = Globals.ThisAddIn.Application.Workbooks.Open(@"C:\SampleTest.xls");
                var sourceWorksheet = (Excel.Worksheet)sourceWorkbook.Sheets[1];
                var sourceRange = (Excel.Range)sourceWorksheet.UsedRange;
    
    
                var destinationRange = destinationWorksheet.Range[sourceRange.Address];
                sourceRange.Copy();
                destinationRange.PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);
                
                System.Windows.Forms.Clipboard.Clear();
                sourceWorkbook.Close(false);
    		


    • Edited by Girija Beuria Thursday, July 19, 2012 6:14 AM Edited the code
    Wednesday, July 18, 2012 9:54 PM