How to show (or prevent hiding) Excel worksheets created from PowerShell

user_3217145 0
Viewed 764

I have a script that does a lot of Excel processing. After making two worksheets, I then make three more and populate them with data. However, when I open that Workbook, sheets are in the order of 5, 4, 3, 1, 2

Open the spreadsheet

$ExcelObject=New-Object -ComObject Excel.Application
$ExcelWorkbook=$ExcelObject.WorkBooks.Open("c:\Output.xlsx")

Make a worksheet

$ActiveWorksheet=$ExcelWorkbook.WorkSheets.Add()
$ActiveWorksheet.Activate()
$ActiveWorksheet.Visible=$True
$ActiveWorksheet=$ExcelWorkbook.WorkSheets.item("$WorksheetName")

It looks like the three last worksheets don't exist until you click the left worksheet navigation arrow three times to make them appear.

If I record a macro, the VBA code to make them visible is three instances of

ActiveWindow.ScrollWorkbookTabs Sheets:=-1

There is the xlFirst enumeration, but I can't seem to find the right syntax to get that working.

Why are they not showing? Is there a Powershell way to make them visible or to scroll them to show before saving?

vba excel powershell
2 Answers

Are you looking to just select them in the UI, or do you want them in order? If are just looking to see them in the UI,

$ActiveWorksheet.Select()

If you want them in order, change your Add()s. 

$ActiveWorksheet=$ExcelWorkbook.WorkSheets.Add([System.Reflection.Missing]::Value,$ExcelWorkbook.Worksheets.Item($ExcelWorkbook.Worksheets.count))

should add a worksheet to the right hand side.

user_60793850

You can always add the sheets to an array and then sort them. That might solve your problem.

Let me know if this helps.

# Create array and add sheet names.
$wsNames = New-Object System.Collections.ArrayList
foreach ($worksheet in $ExcelWorkbook.Worksheets) {
    $wsNames.Add($worksheet.Name)
}

# Order Worksheets
$wsNames.Sort()
for ($i = 0; $i -lt $wsNames.Count - 1; $i++) {
    $wsToMoveName = $wsNames[$i]
    $wsToMove = $ExcelWorkbook.Worksheets.Item($wsToMoveName)
    $wsAfter = $ExcelWorkbook.Worksheets.Item($i + 1)
    $wsToMove.Move($wsAfter)
}
user_59107420
Related