PowerShell - Excel - Select first worksheet in workbook

Question

I have a script that loops through a workbook and copies all tabs to a new workbook. For some reason, though, the first tab is always being skipped.

for ($i=1; $i -lt $myworkbook.Worksheets.Count; $i++) {
... other code
$SheetToCopy = $source.Worksheets.Items($i)
$sheetToCopy.Copy($target)
}

I've tried setting $i to 0, but then powershell complains that zero is not valid.
Tried using $workbook.sheets.item and $workbook.worksheets.item, but they seem to do the same thing

Can anyone shed some light on what is going on here?

Additional Information:

The workbooks will never have the same named tabs, for this reason i'm using index rather than name.

Are you sure it's the first tab being skipped and not the last? From what it looks like in your code it should be (i = 1; i <= worksheets.Count; i++), otherwise when it hits the last sheet with an index of say 4 and i is at 4, it's never going to copy it. Unless that's what you intended. — bamblack
– bamblack, Commented Feb 3, 2014 at 19:15
Ah, good eye. I'll go test that out, but i think you're right. I'm used to starting at 0, so it would work to do <, but in this case i need to use <= — Cade W.
– Cade W., Commented Feb 3, 2014 at 19:17
Yeah, all these non-zero index based languages are for the birds! Hope that solves it. — bamblack
– bamblack, Commented Feb 3, 2014 at 19:18
You can add that as the answer and i'll accept it. That was the problem. — Cade W.
– Cade W., Commented Feb 3, 2014 at 19:24

bamblack · Accepted Answer · 2014-02-03 19:39:33Z

2

From the code you posted it looks like it may be skipping the last sheet and not the first. It should be

(int i = 1; i <= worksheets.Count; i++) // roughly translated from C#

When you have the < sign there (instead of <=) and because you're using a non-zero based index, when it hits the last sheet i will be equal to worksheets.Count, therefore skipping it since you require it to be less than worksheets.Count

edited Feb 3, 2014 at 19:39

answered Feb 3, 2014 at 19:28

bamblack

3,7892 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PowerShell - Excel - Select first worksheet in workbook

Additional Information:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Additional Information:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related