For Each Excel Sheet w/Hidden Sheets

[Bill_Richardson]

Is there a way to get “For Each Excel Sheet” (Modern Integrations) to completely ignore hidden sheets? At least with v2.11.4, it iterates through the loop for the total number of times for all sheets (hidden and not hidden); however, CurrentSheet.Name only populates with names of non-hidden sheets. The result is that the same non-hidden sheet is processed two or more times in the loop if hidden sheets are present. It seems that CurrentSheet.Name takes on the value of the nearest non-hidden sheet.

For example if we have a workbook with these three sheets:

[Hiden Sheet]
[Data Sheet]
[Another Data Sheet]
[Another Hidden Sheet]

For Each Excel Sheet will iterate four times through the loop.

Results:
04/25/2022 16:04:09 => [Info] ExcelForEachSheetWithHiddenSheets execution started
04/25/2022 16:04:09 => [Info] Audit: Using Excel File: C:\B\Hidden.xlsx
04/25/2022 16:04:10 => [Info] 1. sheet=Data Sheet
04/25/2022 16:04:10 => [Info] 2. sheet=Data Sheet
04/25/2022 16:04:10 => [Info] 3. sheet=Another Data Sheet
04/25/2022 16:04:10 => [Info] 4. sheet=Another Data Sheet

Workaround: Include logic to keep track of prior sheet name within the loop and only process the sheet if CurrentSheet.Name <> priorSheetName. This works, but it would be better if the Modern Excel activities had more control with hidden sheets.

[Yoichi]

Hi,

It seems a bug of ForEachExcelSheet.

In Excel.Activities package 2.12.3 bundled in 22.4.0CE, it works well.(Hidden sheets are skipped)
So can you try to update to the latest version (2.12.3 or 2.12.2-preview)?

Regards,

[Bill_Richardson]

Thanks @Yoichi ,
Good to know it’s fixed in a later version. I don’t seem to have access to the versions you mention–when I do Manage Packages, 2.11.4 is the latest one that I can choose. I will give it a try when those later version become available.

[Yoichi]

Hi,

FYI, if you want to try preview version, Include Prerelease option of MangePackages might help you. In my 21.10 enterprise environment, I cannot get 2.12.3 stable, too. I hope it will be released as enterprise soon.

Regards,

[Bill_Richardson]

@Yoichi ,
Finally had time to try 2.12.2 with my Enterprise 21.10.6 studio. It works–hidden sheets are not visible to the loop.