Click here to Skip to main content
14,970,403 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 ;)
chadchen 15-Oct-14 16:33pm
   
But the array will not keep the numeric formatting for date and time. Is there a way to expand the PasteSpecial over 65536 rows?
Thank you very much for the help
Maciej Los 15-Oct-14 16:41pm
   
If you use MS Excel up to 2003 version, the limit of rows is 65536 ;(
chadchen 15-Oct-14 17:06pm
   
Hi Maciej:
Yes, we do have that problem.
That's why I want to write this addin to save the result into xlsx format.
In this way it can keep 1M records.
If I output all cells value into array then merge array after that write to excel sheet. It can pass 65536 rows with no issue.
The problem is only happen with pastspecial.
The excel report contains date and time column when export to Array all the value stored in Double. I want the formatting which pastespecial works perfectly just can't pass 65536 rows.
Strange problem.
Maciej Los 15-Oct-14 18:32pm
   
Please, use 'Reply' widget to post comment, unless you want to finish discussion.
Let's get back to the discussion...
Try to paste only values and then paste formatting. It might help you to resolve problem.

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