I have a powershell script that loops through all worksheets of an excel file to copy columns from one worksheet to another worksheet with the same name in another file. It is working well, but I would like it to only loop through visible worksheets in the source file. How can I modify the script to accomplish this?

Param(
   $Source = “Source.xlsm”,
   $range1 = “A1:EZ1”,
   $Output = “Output.xlsx”
   
   ) #end param
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false

$Workbook = $excel.Workbooks.open($Source)
$WorkbookOutput = $excel.Workbooks.open($Output)
$Excel.displayAlerts = $false # don't prompt the user
$i = 1
foreach ($sheet in $workbook.Worksheets)

 {  
$Worksheet = $Workbook.WorkSheets.item($i)
$worksheet.activate() 
$wksname = $worksheet.name
Write-Output $wksname
$range = $WorkSheet.Range($range1).EntireColumn
$range.Copy() | out-null
#$Excel.displayAlerts = $false # don't prompt the user
$WorksheetOutput = $WorkbookOutput.WorkSheets.item($wksname)
$worksheetOutput.activate()
#$Range = $Worksheet.Range($range2)
$WorksheetOutput.Range("A1:EZ1").PasteSpecial(-4163)
$i  
Write-Output $i

}
$workbook.Save() 
$Excel.Quit()

Thanks for your help!

Kind Regards,

Smid

CodePudding user response：

foreach ($sheet in $workbook.Worksheets) {
  if ($sheet.Visible) {
    ...
  }
}

CodePudding user response：

To add a bit more explanation to my comment:

An Excel WorkSheet has a .Visibility property. Usually, such a property would be a Boolean where only $true or $false apply.
In this case however .Visibility is an [Int32] value that can have three different values, taken from the XlSheetVisibility enumeration:

As you can see, the value for a Visible worksheet is -1

Microsofts examples on this property only show VB code and there it uses
Worksheets("Sheet1").Visible = True and Worksheets("Sheet1").Visible = False to set the visibility to either -1 or 0. That works because in VB, the numeric value for True equals -1.

In PowerShell however, the numeric value for $true is not -1, but 1 instead:

[int]$true # --> 1

which means you should not set that property using $true as value to make the sheet visible, but -1 instead.

The same applies for testing if a sheet is visible or hidden:
Checking with if ($sheet.Visible){..} in this case does not check if a Boolean is $true or $false, but since the value is an integer, it checks for "if that property is zero or not".
If the sheet was set to xlSheetVeryHidden (value 2), that test will result in $true, making you think it is visible..

Finally, in your use case, simply change the foreach loop to read this:

foreach ($sheet in ($workbook.Worksheets | Where-Object { $_.Visible -eq -1 })) {
    # the rest of your code
}