how to copy columns from one excel file to another?

Question

1.00/5 (1 vote)

See more:

I using excel addin application, to create function which can copy columns from one excel file to another. Here is the code so far but when I render the application, however the program outputs a blank book.xls file.

Updated:

C#

private void button1_Click(object sender, EventArgs e)
       {
           Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
           Excel.Workbook xlWorkBook;
           Excel.Workbook xlWorkBook2;

           Excel.Worksheet xlWorkSheet;
           Excel.Worksheet xlWsheet2;

           Excel.Range xlSourceRange;
           Excel.Range xlSourceRange1;

           Excel.Range xlDestRange;
           Excel.Range xlDestRange1;

           xlWorkBook = xlApp.Workbooks.Open("C:/../../../../../../../Test.xls");


           xlWorkBook2 = xlApp.Workbooks.Open("C:/../../../../../../../Book1.xls");

           //~~> Display Excel
           xlApp.Visible = true;

           //~~> Set the source worksheet
           xlWorkSheet = xlWorkBook.Sheets["Sheet1"];
           //~~> Set the destination worksheet
           xlWsheet2 = xlWorkBook2.Sheets["Sheet1"];

           //~~> Set the source range
           xlSourceRange = xlWorkSheet.Range["E15"].EntireColumn;
           xlSourceRange1 = xlWorkSheet.Range["D15"].EntireColumn;

           //~~> Set the destination range
           xlDestRange = xlWsheet2.Range["A2"];
           xlDestRange1 = xlWsheet2.Range["B2"];


           xlSourceRange.Copy(Type.Missing);

           xlDestRange.PasteSpecial(Excel.XlPasteType.xlPasteAll,
   Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);


           xlSourceRange1.Copy(Type.Missing);

           xlDestRange1.PasteSpecial(Excel.XlPasteType.xlPasteAll,
   Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);

       }

I am little unsure how I to go about tracing for errors, as I am quite novice in this area. Any further assistance would be most appreciated.

Thank you

Posted 17-Jun-15 1:54am

miss786

Updated 17-Jun-15 3:20am

v3

Add a Solution

Comments

CHill60 17-Jun-15 8:21am

All you are doing is copying ... you're not pasting what you have copied anywhere.

miss786 17-Jun-15 9:22am

Hi, Thank you for your reply. I have updated my function above with pasteSpecial property, but I am still getting a blank data output in the book1.xls file.

Any further help would be very much appreciated.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2015-06-17T04:54:00

Couple of things that might help sort this out.
The lines

C#

//~~> Set the destination range
xlDestRange = xlWsheet2.Range["A2"];
xlDestRange1 = xlWsheet2.Range["B2"];

will result in an error

Quote:
The information cannot be pasted because the Copy area and the paste area are not the same size and shape.

You need to select Row 1 if you are trying to paste an EntireColumn
I.e.

C#

xlDestRange = xlWsheet2.Range["A1"];
xlDestRange1 = xlWsheet2.Range["B1"];

The next thing to be wary of is xlPasteSpecialOperationAdd - if there are non-numeric values in your source range then the paste will fail.

Finally - I don't think it's a problem at the moment, but the path to your files is very difficult to follow - I'd advise simplifying the path OR using explicit pathnames