Able to move all sheets except for the one that goes on the end

Question

0.00/5 (No votes)

See more:

I need to move all the sheets in the food pantry archive file to reverse the order. I can move all the sheets except the end sheet, which is the first sheet to move. The Totals sheet, which is the first sheet, does not get moved.

C#

private void MoveArchiveDataSheets()
        {
            // Open the archive workbbook.
            theArchiveFile = frmFoodPantry.objApp.Workbooks.Open(strArchiveFileName);
            // The work is done so save the archive workbook.
            //See how many sheets there are including the totals sheet
            int numOfSheets = theArchiveFile.Sheets.Count;
            // Set the counter for the sheet to move before.
            int moveBefore = numOfSheets;

            // Select the first data sheet.
            theArchiveFile.Worksheets[2].Select();

            // Move the first data sheet to the end of the workbook
            ((Excel.Worksheet)frmFoodPantry.objApp.ActiveSheet).Move(
                frmFoodPantry.objApp.Worksheets[numOfSheets]);
            //            (theArchiveFile.ActiveSheet).Move(theArchiveFile.Worksheets[moveBefore]);
            //(theArchiveFile.ActiveSheet).Move(frmFoodPantry.objApp.Worksheets[moveBefore]);
            //frmFoodPantry.objApp.Sheets[2].Move(theArchiveFile.Sheets[numOfSheets]);
            //theArchiveFile.ActiveSheet.MoveToEnd(theArchiveFile.Sheets[numOfSheets]);
            //(frmFoodPantry.objApp.ActiveSheet).Move(frmFoodPantry.objApp.Worksheets[moveBefore]);
            //Loop through all the data sheets and move them
            for (int i = 2; i < numOfSheets; i++)
            {
                moveBefore-- ;
                // Select the next data sheet to move (it will always be sheet 2).
                theArchiveFile.Worksheets[2].Select();
                // Move the next data sheet to it's proper month sequence
                //(frmFoodPantry.objApp.ActiveSheet).Move(frmFoodPantry.objApp.Worksheets[moveBefore]);
                theArchiveFile.Worksheets[2].Move(theArchiveFile.Worksheets[moveBefore]);
            }
            theArchiveFile.Save();
            // The archive workbook has bee saved, and we don't need it open anmore, so close it.
            theArchiveFile.Close();

        }

What I have tried:

C#

((Excel.Worksheet)frmFoodPantry.objApp.ActiveSheet).Move(
                frmFoodPantry.objApp.Worksheets[numOfSheets]);
            //            (theArchiveFile.ActiveSheet).Move(theArchiveFile.Worksheets[moveBefore]);
            //(theArchiveFile.ActiveSheet).Move(frmFoodPantry.objApp.Worksheets[moveBefore]);
            //frmFoodPantry.objApp.Sheets[2].Move(theArchiveFile.Sheets[numOfSheets]);
            //theArchiveFile.ActiveSheet.MoveToEnd(theArchiveFile.Sheets[numOfSheets]);
            //(frmFoodPantry.objApp.ActiveSheet).Move(frmFoodPantry.objApp.Worksheets[moveBefore]);
            //Loop through all the data sheets and move them

Posted 12-Aug-22 7:57am

PaulaJoannAllen

Updated 12-Aug-22 8:43am

v3

Add a Solution

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)

0x01AA · Accepted Answer · 2022-08-12T08:41:00

You need to capture the first sheet at the beginning and then move the other sheets - beginning with the most right - before that sheet.
This works for me:

private void buttonSheetReverseOrder_Click(object sender, EventArgs e)
{
    // Excel Application
    Microsoft.Office.Interop.Excel.Application _excel = new _Excel.Application();

    // No User Intervention Dialogues
    _excel.DisplayAlerts = false;

    // Open workbook
    Workbook workbook = _excel.Workbooks.Open(@"c:\temp\cp.excel\ExcelReverseSheetOrder.xlsx");


    // Reverse Seet Order
    Worksheet firstSheet = workbook.Sheets[1];
    for (int ix= workbook.Sheets.Count; ix > 1; ix--)
    {
        workbook.Sheets[workbook.Sheets.Count].Move(firstSheet);
    }

    // SaveAs the workbook. SaveAs makes test more easy ;)
    workbook.SaveAs(@"c:\temp\cp.excel\ExcelMovedSheets.xlsx");

    // Close
    workbook.Close();
}

[Edit]
In case we have the sheets 'Total', 'Dec' ..... 'Jan' and we need them to reorder to 'Total', 'Jan' ..... 'Dec' we can do it this way:

private void buttonSheetReverseOrder_Click(object sender, EventArgs e)
{
    // Excel Application
    Microsoft.Office.Interop.Excel.Application _excel = new _Excel.Application();

    // No User Intervention Dialogues
    _excel.DisplayAlerts = false;

    // Open workbook
    Workbook workbook = _excel.Workbooks.Open(@"c:\temp\cp.excel\ExcelReverseSheetOrder.xlsx");


    // Reverse Seet Order
    Worksheet firstSheet = workbook.Sheets[2];
    for (int ix = 13; ix > 2; ix--)
    {
        workbook.Sheets[workbook.Sheets.Count].Move(firstSheet);
    }

    // SaveAs the workbook
    workbook.SaveAs(@"c:\temp\cp.excel\ExcelMovedSheets.xlsx");

    // Close
    workbook.Close();
}