'Unable to get the copy property of the worksheet class'

Question

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 2-Aug-22 2:32am

PaulaJoannAllen

Updated 3-Aug-22 9:25am

v4

Add a Solution

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.

Richard MacCutchan 4-Aug-22 3:22am

Well I am afraid I cannot think of any reason why this should fail. My tests worked successfully and the worksheet was copied.

The error message is suggesting that you have a definition of a Worksheet class in your code that is not the same as the Excel one. How this could come about is impossible to guess.

matblue25 3-Aug-22 13:04pm

Why square brackets instead of parentheses?
Worksheets["Totals"]

PaulaJoannAllen 3-Aug-22 14:30pm

Just something I tried, but of course, it was looking for an array. I put them back to ().

Richard MacCutchan 4-Aug-22 3:19am

The use of square brackets is correct for accessing items in an array.

Richard MacCutchan 4-Aug-22 3:19am

The use of square brackets is correct for accessing items in an array.

1 solution

Add a Solution

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

matblue25 · Accepted Answer · 2022-08-03T09:25:00

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.

'Unable to get the copy property of the worksheet class'

1 solution

Solution 1

Add your solution here

Preview 0

'Unable to get the copy property of the worksheet class'

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us