Click here to Skip to main content
15,916,280 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello Expert:

I have Excel reports generated by application. the format is xls.

Due to row limits, it will split the content into multiple sheets if the total number of records is bigger than 65536.

I am trying to copy all records from each sheet and merge them into one sheet then save the file in xlsx format.

Originally I copy all the cells into array then write it to the target sheet.

It works fine but when I write it into the target sheet, the datetime is showing as double.

I want to remove human interaction when doing the merge, so I switch to PasteSpecial, which keep the format, but it has one problem, it seems I can only copy and paste the first sheet. when it tries to paste the second time I receive an error.

"The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:

Click a single cell, and then paste.
Select a rectangle that's the same size and shape, and then paste."
Following is my code:

C#
app = Globals.ThisAddIn.Application;

sourceBook = app.ActiveWorkbook;

     
targetBook = app.Workbooks.Add(Type.Missing);                
targetSheet = targetBook.Worksheets.Add(Type.Missing);
targetSheet.Name = "Merge Result";

int rowCount = 0;
string targetFilePath = "";
foreach (Excel.Worksheet sheet in sourceBook.Worksheets)
{
Excel.Range workSheetRange = sheet.UsedRange;
Excel.Range startPasteCell = (Excel.Range)targetSheet.Cells[rowCount + 1, 1];
Excel.Range endPasteCell = (Excel.Range)targetSheet.Cells[rowCount + workSheetRange.Rows.Count, 
                            workSheetRange.Columns.Count];
Excel.Range pasteArea = targetSheet.get_Range(startPasteCell, endPasteCell);
                    workSheetRange.Copy(Type.Missing);
//I have tried both cell select then paste. or range paste same result.
startPasteCell.Select();
targetSheet.PasteSpecial(Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing);

//pasteArea.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                    //Type.Missing, Type.Missing);
   
rowCount += workSheetRange.Rows.Count;

}


Thank you very much
Posted
Updated 15-Oct-14 9:34am
v2
Comments
Maciej Los 15-Oct-14 16:09pm    
Follow the instruction!
chadchen 15-Oct-14 16:16pm    
Hi Maciej:

Sorry which instruction?
Can you give a link ?

Thank you
Maciej Los 15-Oct-14 16:21pm    
The instruction embeded in error message:
The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:
Click a single cell, and then paste.
Select a rectangle that's the same size and shape, and then paste.
chadchen 15-Oct-14 16:31pm    
Hi Maciej: Thanks for the update. I have tried the suggestion from instruction.

In code, I use: startPasteCell.Select(); or pasteArea.PasteSpecial.
But the result is the same.

What I noticed is: The pasteSpecial method is working ok.

Just it can not pass the 65536 limits.(whenever I try to paste the second sheet, it indicates area doesn't fit. ) the behavior is very similar to when we open xls file and try to paste to 65537 row. Since I only open a new workbook I think it should be ok. If I use array then I can pass 65537 row with no issues.
Maciej Los 15-Oct-14 16:45pm    
Change MS Excel to 2007 and higher ;) You'll be able to paste over 65536 rows ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900