Click here to Skip to main content
15,885,546 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to copy a worksheet from one workbook to another workbook.

I get an error
'Unable to get the Copy property of the Worksheet class', what am I doing wrong?
(forgot to give the line of code)

frmFoodPantry.theWorkbook.Worksheets["Totals"].Copy(destinationWorkbook.Worksheets[1]);

This is in
private void CopyTotalsSheet()

The code I am using is

C#
private void cmdArchiveSheets_Click(object sender, EventArgs e)
        {
            CreateArchiveFile();
            CopyTotalsSheet();
            MoveSheets();
        }


        private void CreateArchiveFile()
        {
            string strYear;

            //Select the second sheet in the Food Pantry Workbook
            frmFoodPantry.theWorkbook.Worksheets[2].Select();

            //Get the second sheet as this will be the first datasheet
            Excel.Worksheet excelSheet = frmFoodPantry.theWorkbook.ActiveSheet;

            //Get the name of the sheet
            strYear = excelSheet.Name;

            //Parse out the year
            strYear = strYear.Substring(strYear.Length - 4);

            //Create the archive file name
            strArchiveFileName = archivePath + @"Food Pantry Registry " + strYear + ".xlsx";

            //Convert the file name to an object
            Object objString = strArchiveFileName;

            string strSourceFile = (archivePath + "Food Pantry Registry Template.xlsx");
            strDestinationFile = strArchiveFileName;

            //Copy the template workbook
            sourceWorkbook = objArchive.Workbooks.Open(strSourceFile);
            sourceWorkbook.SaveAs(strDestinationFile);
            sourceWorkbook.Close();
            destinationWorkbook = objArchive.Workbooks.Open(strDestinationFile, misValue, false);

        }
        private void CopyTotalsSheet()
        {
            
            Excel.Sheets sheets = frmFoodPantry.theWorkbook.Worksheets;
            Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);

            //Unprotect the Totals sheet
            worksheet.Unprotect();

           // worksheet.Copy(destinationWorkbook.Worksheets[1]);

            frmFoodPantry.theWorkbook.Worksheets.Select(1);
            //destinationWorkbook.Worksheets.Select(1);
            //destinationWorkbook.Worksheets.Select("Totals");
            //destinationWorkbook.Worksheets["Totals"].Copy(frmFoodPantry.theWorkbook.Worksheets[1]);
            //frmFoodPantry.theWorkbook.Worksheets[1].Copy(destinationWorkbook.Worksheets[1]);
            frmFoodPantry.theWorkbook.Worksheets["Totals"].Copy(destinationWorkbook.Worksheets[1]);
        }


What I have tried:

 //Unprotect the Totals sheet
 worksheet.Unprotect();

// worksheet.Copy(destinationWorkbook.Worksheets[1]);

 frmFoodPantry.theWorkbook.Worksheets.Select(1);
 //destinationWorkbook.Worksheets.Select(1);
 //destinationWorkbook.Worksheets.Select("Totals");
 //destinationWorkbook.Worksheets["Totals"].Copy(frmFoodPantry.theWorkbook.Worksheets[1]);
 //frmFoodPantry.theWorkbook.Worksheets[1].Copy(destinationWorkbook.Worksheets[1]);
 frmFoodPantry.theWorkbook.Worksheets["Totals"].Copy(destinationWorkbook.Worksheets[1]);
Posted
Updated 3-Aug-22 9:25am
v4
Comments
[no name] 2-Aug-22 12:34pm    
The "language" of Excel is VBA.
Richard MacCutchan 3-Aug-22 8:34am    
Yes but the questioner is using C#.
Richard MacCutchan 3-Aug-22 8:40am    
I have checked the documentation and cannot see any reason why that error message occurs. I can only suggest you use the debugger to check exactly what object is returned from frmFoodPantry.theWorkbook.Worksheets["Totals"]. And why you are using statements like:
Excel.Sheets sheets = frmFoodPantry.theWorkbook.Worksheets;

What exactly is frmFoodPantry and what properties does it contain?
Richard MacCutchan 3-Aug-22 9:01am    
And for completeness I just tested a simple Copy operation which worked correctly. Not exactly the same as your code but as close as I could get it.
PaulaJoannAllen 3-Aug-22 14:28pm    
frmFoodPantry is the main program, and theWorkbook is assigned on the open statement.

1 solution

Per the VBA documentation Excel Worksheet Copy, the source and destination have to use the same Excel application instantiation. It looks like you're using the "objArchive" instantiation for the destination and the "frmFoodPantry" instantiation for the source. I think if you changed objArchive to frmFoodPantry when you create the destinationWorkbook object, it would work.
Note that this is only documented in the VBA object model (as referenced). The C# Excel Interop documentation doesn't mention it but I strongly suspect that's what's causing your problem.
 
Share this answer
 
v2
Comments
PaulaJoannAllen 3-Aug-22 17:15pm    
Tried, and it still does not work. It was worth a try.
matblue25 3-Aug-22 22:23pm    
Run it in debug and check the value of destinationWorkbook.Worksheets(1) inside CopyTotalsSheet(). You may need to set it in that method. From the code you supplied, I can’t tell where destinationWorkbook is declared and whether it’s still pointing to the open destination file in the CopyTotalsSheet method.

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