title: open a password protected Excel in powershell
body: <p>I'm trying to open a password protected excel sheet in powershell and output a report on how many rows are in the sheet.</p>
  
  <p>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.</p>
  
  <p>my current script is</p>
  
  <pre><code>$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
  </code></pre>
  
  <p>This is the error I get:</p>
  
  <pre><code>Exception calling "Open" with "3" argument(s): "Open method of Workbooks class failed"
  At line:3 char:35
  +  $workbook = $excel.Workbooks.Open &lt;&lt;&lt;&lt; ($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 &lt;&lt;&lt;&lt; ("Data")
      + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull
  </code></pre>
  
  <p>If I take out the $password variable it works but I then have to enter the password manually.</p>