none
C# with user interface, how can I copy from my excel source to another specific destination excel file? RRS feed

  • Question

  • HI i need help. If possible, i can able to select the sheet number from my excel source and copy and paste selected columns into the destination excel file. 
    Friday, January 22, 2016 8:22 AM

Answers

All replies

  • Hello Ryan,

    You can use the Copy and Paste methods of the Range and Worksheet classes. For example:

    With Worksheets("Sheet1") 
     .Range("C1:C5").Copy 
     .Range("D1:D5").PasteSpecial _ 
     Operation:=xlPasteSpecialOperationAdd 
    End With

    Friday, January 22, 2016 6:52 PM
  • hi ryan99999,

    I've ever done a similar investigation with yours. for your situation, I build a solution for you using C#, please check the following sample code:

                //load the source excel file
                Workbook workbook1 = new Workbook();
                workbook1.LoadFromFile("source.xlsx");
                //get its first worksheet
                Worksheet worksheet1 = workbook1.Worksheets[0];
                //create a new excel file
                Workbook workbook2 = new Workbook();
                //get its first worksheet
                Worksheet worksheet2 = workbook2.Worksheets[0];
                //copy colunms from 1 to 5 in the first worksheet of the source excel file to the new excel file
                int i = 1;
                int rowCount = worksheet1.Rows.Count();
                foreach(CellRange range in worksheet1.Rows[0])
                {
                    CellRange sourceRange = worksheet1.Range[range.Column, 1, range.Column, 5];
                    CellRange destRange = worksheet2.Range[i,1,i,rowCount];
                    worksheet1.Copy(sourceRange,destRange,true);
                    i++;
                }
                //save the target file
                workbook2.SaveToFile("destworksheet.xlsx");

    I hope this can help you. besides in order to simplify the codes, I used a free excel library as I'm not good at VBA, but note that it is limited to 5 worksheets. if it doesn't help, please feel free to let me know.

    Michael B




    Monday, January 25, 2016 3:23 AM
  • Hi Michael,

    i get error regarding the LoadFromFile. it says no definition. what can i do to solve this?

    Wednesday, January 27, 2016 12:51 AM
  • hi Ryan,

    Did you add the dll file from the installation folder as the reference of your project and use namespace before using the code? and LoadFromFile() requires a file path, copy the file to the Debug catalog of your project, you can use the code as I used above, otherwise, you should use the full path, like this:workbook1.LoadFromFile(@"E:\ProgramFiles\source.xlsx");

    if this does not help, show me your sample code if possible.

    Regards,

    Michael B



    • Edited by Michael Brrr Wednesday, January 27, 2016 1:38 AM
    Wednesday, January 27, 2016 1:36 AM
  • hi michael, after i tried ur code, i got errors and i continued to did some researched. sorry i used other people reference. but i still have problem. hope u can help me to solve. 

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.IO;
    using Excel = Microsoft.Office.Interop.Excel;

    namespace WindowsFormsApplication6
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void Browsebtn_Click(object sender, EventArgs e)
            {
                
                int size = -1;
                DialogResult result = openFileDialog1.ShowDialog(); // Show the dialog.
                FilePathLabel1.Text = openFileDialog1.FileName;

                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = false;
                excelApp.Workbooks.Open(FilePathLabel1.Text);

                string file1 = openFileDialog1.FileName;

                try
                {
                    string text1 = File.ReadAllText(file1);
                    size = text1.Length;
                }
                catch (IOException)
                {
                }                 
            }

           
            private void Destinationbtn_Click(object sender, EventArgs e)
            {

                int size = -1;
                DialogResult result = openFileDialog2.ShowDialog(); // Show the dialog.
                FilePathLabel2.Text = openFileDialog2.FileName;

                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = false;
                excelApp.Workbooks.Open(FilePathLabel2.Text);

                string file2 = openFileDialog2.FileName;
                
                try
                {
                    string text2 = File.ReadAllText(file2);
                    size = text2.Length;
                }
                catch (IOException)
                {
                }

            }

           
            private void Startbtn_Click(object sender, EventArgs e)
            {
                
                Excel.Application app = new Excel.Application();
                Excel.Workbook curWorkBook = null;
                Excel.Workbook destWorkbook = null;
                Excel.Worksheet workSheet = null;
                Excel.Worksheet newWorksheet = null;
                Object defaultArg = Type.Missing;       

                try
                {
                    // Copy the source sheet
                    curWorkBook = app.Workbooks.Open("file1", defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
                    workSheet = (Excel.Worksheet)curWorkBook.Sheets[1];
                    workSheet.UsedRange.Copy(defaultArg);

                    // Paste on destination sheet
                    destWorkbook = app.Workbooks.Open("file2", defaultArg, false, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
                    newWorksheet = (Excel.Worksheet)destWorkbook.Worksheets.Add(defaultArg, defaultArg, defaultArg, defaultArg);
                    newWorksheet.UsedRange._PasteSpecial(Excel.XlPasteType.xlPasteValues, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                }
                catch (Exception exc)
                {
                    System.Windows.Forms.MessageBox.Show(exc.Message);
                }
                finally
                {
                    if (curWorkBook != null)
                    {
                        curWorkBook.Save();
                        curWorkBook.Close(defaultArg, defaultArg, defaultArg);
                    }

                    if (destWorkbook != null)
                    {
                        destWorkbook.Save();
                        destWorkbook.Close(defaultArg, defaultArg, defaultArg);
                    }
                }
                app.Quit();
            }

           
        }

    }
        
        However, when i debug, it says file 1 could not be found. 

    Wednesday, January 27, 2016 3:44 AM
  • Hi Ryan,

    The new issue seems duplicate with thread below,

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/0cdb88cb-aaad-43cd-8add-19c9bb9fabd8/how-can-i-pass-my-string-from-1-private-void-to-another?forum=vsto#0cdb88cb-aaad-43cd-8add-19c9bb9fabd8

    Please check the reply in thread above to see whether it resolve this issue.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Saturday, January 30, 2016 6:30 AM
    Moderator