Problem to scan all sheets

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
Hi,
Does 2nd line below scan through all Worksheet within Excel file? If not, how to adjust it to scan through all Worksheets, except one specific sheet with a given name?
                    Excel.Worksheet Worksheet0;
                        ...
                        foreach (Excel.Worksheet sheet in Book0.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == Worksheet0))

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2013

Commented:
First of all, you need to compare name with name:
                    Excel.Worksheet Worksheet0;
                        ...
                        foreach (Excel.Worksheet sheet in Book0.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == Worksheet0.Name))

Open in new window

The code above will select one worksheet with the name equal to Worksheet0.Name.
If you want to select worksheets with the name not equal to Worksheet0.Name then use != operator:
                    Excel.Worksheet Worksheet0;
                        ...
                        foreach (Excel.Worksheet sheet in Book0.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name != Worksheet0.Name))

Open in new window

HuaMin ChenProblem resolver

Author

Commented:
Many thanks. How to adjust last line below, as I want to paste to the sheet with name - "Sheet Overall"?
                        foreach (Excel.Worksheet sheet in Book0.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name != "Sheet Overall"))
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Sheets sheetData = Book0.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                            Excel.Range usedRange = sheet.UsedRange;

                            string cellReference = "^^^^" + sheet.Name + "^^^^";

                            foreach (Excel.Range row in usedRange.Rows)
                            {
                                RowID++;
                                row.Copy();
                                Worksheet0.Cells[RowID, 1].PasteSpecial(-4104);
                        

Open in new window

Top Expert 2013
Commented:
Use == operator:
                        foreach (Excel.Worksheet sheet in Book0.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet Overall"))

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial