一次检查多个Excel工作表

使用 PowersHell，您可以安装 Doug Finke 的 ImportExcel PowersHell 模块

Find-Module ImportExcel | Install-Module

（ImportExcel 帮助在这里'https://www.powersHellgallery.com/packages/ImportExcel/'）

然后您可以使用 Get-ExcelSheeTinfo 选项检查 .xLSX 和 .xlsm 文件中的隐藏工作表

$FolderPath = 'DriveLetter:\path to file'
$Files = Get-ChildItem -Path $FolderPath -Include *.xLSX,*.xlsm -File -Name
$array = @()
ForEach($File in $Files){ 
$row = Get-ExcelSheeTinfo $File 
$array = $array + $row
}
$array | Out-GridView

对于 .xls 文件，您需要先转换为 .xLSX，其中有许多工具和脚本可供免费下载。

打开一个新的Excel文件，在那里打开VBA编辑器，“插入”->“模块”，在那里粘贴以下代码：

"ACT"#

输入您的文件夹地址，而不是Sub search_for_hidden_sheet_or_cells() Dim oFSO As Object: Set oFSO = CreateObject("ScripTing.FileSystemObject") Dim f As Object Dim wb As Workbook Dim ws As Worksheet Dim c As Range: Set c = ThisWorkbook.Sheets(1).Cells(1,1) Dim r As Range For Each f In oFSO.GetFolder("C:\Temp\test").Files If f.Name Like "*.xls*" Then Set wb = Application.Workbooks.Open(f) For Each ws In wb.Worksheets If ws.Visible = xlSheetHidden Then c = "Hidden sheet """ & ws.Name & """ in wb """ & wb.Name & """" Set c = c.offset(1) ElseIf ws.Visible = xlSheetVeryHidden Then c = "Very hidden sheet """ & ws.Name & """ in wb """ & wb.Name & """" Set c = c.offset(1) Else For Each r In wS.UsedRange.Rows If r.Hidden Then c = "Hidden row " & r.Row & " in sheet """ & ws.Name & """ in wb """ & wb.Name & """" Set c = c.offset(1) End If Next For Each r In wS.UsedRange.columns If r.Hidden Then c = "Hidden col " & Split(r.Address,"$")(3) & " in sheet """ & ws.Name & """ in wb """ & wb.Name & """" Set c = c.offset(1) End If Next End If Next wb.Close End If Next End Sub 。

此代码将查找隐藏在 Excel 文件中的所有工作表、列和行。如果有很多文件，您只需等待该过程完成即可。我测试后的结果是这样的：