问题描述
我正在尝试在Powershell中打开一个受密码保护的Excel工作表,并输出有关工作表中有多少行的报告.
I'm trying to open a password protected excel sheet in powershell and output a report on how many rows are in the sheet.
如果工作表不受密码保护,则脚本可以正常工作,但是如果设置了密码,我似乎无法获得强大的功能来打开它.
the script works absolutely fine if the sheet isn't password protected but I can't seem to get powershell to open it if there is a password set.
我当前的脚本是
$Report = "S:\Business Support\excel tests\MI Tool - Live.csv"
$path = "S:\Business Support\excel tests"
[Array]$Results = $null
$excelSheets = Get-Childitem -Path $path -Include "MI Tool - Live.xlsm" -Recurse
$excel = New-Object -comobject Excel.Application
$excel.visible = $false
$password = "blablabla"
$updatelinks = 0
foreach($excelSheet in $excelSheets)
{
$workbook = $excel.Workbooks.Open($excelSheet,$updatelinks,$password)
$rowCount = $null
$worksheet = $workbook.sheets.item("Data")
$rowMax = ($worksheet.usedRange.rows).count
$rowCount += $rowMax
$Results += New-Object Psobject -Property @{
"File Name"=$excelSheet.Name
"Row Count"=$rowCount}
$excelSheet.Name
$workbook.Sheets.count
$rowCount
}
$excel.quit()
Stop-Process -Name EXCEL -Force
$Results | select "File Name","Row Count" | Export-Csv $Report -NoTypeInformation
这是我得到的错误:
Exception calling "Open" with "3" argument(s): "Open method of Workbooks class failed"
At line:3 char:35
+ $workbook = $excel.Workbooks.Open <<<< ($excelSheet,$updatelinks,$password)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation
You cannot call a method on a null-valued expression.
At line:5 char:37
+ $worksheet = $workbook.sheets.item <<<< ("Data")
+ CategoryInfo : InvalidOperation: (item:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
如果我删除$ password变量,它可以工作,但是我必须手动输入密码.
If I take out the $password variable it works but I then have to enter the password manually.
推荐答案
您的打开超载不正确.密码是第五个变量.看看MSDN来看看
Your Open overload is incorrect. The password is the 5th variable. Have a look at MSDN to see
所以我相信您需要先填充ReadOnly和Format.您必须填充这些值.
So you would need to populate ReadOnly and Format first I believe. You would have to populate those values.
$excel.Workbooks.open($path,0,0,5,$password)
请查看MSDN,以了解值在2,3和4位置所代表的含义.
Look at the MSDN to understand what the values represent in the 2,3 and 4 positions.
这篇关于在Powershell中打开受密码保护的Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!