2

I have an Excel file that I am opening, applying a password to it, and then saving it using PowerShell. I am getting the following error:

Exception calling "SaveAs" with "3" argument(s): "Cannot save as that name. Document was opened as read-only." At C:\PasswordProtectExcelFiles.ps1:38 char:45
+ $a = $wb.SaveAs("$($FilePath)",$xlNormal,"$($Password)")
+ ~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation

I have searched a lot but nothing has resolved my issue. Here are some questions I refered to already: Powershell - SaveAs function when file already exists and How to Remove ReadOnly Attribute on File Using PowerShell?.

My code:

param([string]$FilePath, [string]$Password )
$xl = new-object -comobject excel.application
$xl.Visible = $True
$xl.DisplayAlerts = $False

$wb = $xl.Workbooks.Open("$($FilePath)")

$a = $wb.SaveAs("$($FilePath)",$xlNormal,"$($Password)")

$a = $xl.Quit()

$a = Release-Ref($wb)
$a = Release-Ref($xl)

I have tried these codes after the Workbooks.Open statement to see if it will save the read-only file, and it worked, but then when I closed and reopened the code it stopped working:

Code1:
$file = Get-Item "$($FilePath)"
if ($file.IsReadOnly -eq $true)
{
    $file.IsReadOnly = $false
}

Code2:
Set-ItemProperty "$($FilePath)" -name IsReadOnly -value $false

Actually, the file is not read only but the folder is and I am unable to check out the box that says read only. Same as this problem: https://social.technet.microsoft.com/Forums/windowsserver/en-US/f7ec4fc5-3bbe-4fd0-a8ca-c4ead75b010c/unable-to-removeclear-readonly-attribute-from-folder-in-windows-server-2008

Community
  • 1
  • 1
Stephanie
  • 496
  • 7
  • 26

1 Answers1

4

According to the documentation for the Open() method, the third argument allows you to specify whether to open the file in read-only mode.

Set it to $false:

$wb = $xl.Workbooks.Open("$($FilePath)", 0, $false)
Mathias R. Jessen
  • 157,619
  • 12
  • 148
  • 206
  • Thank you for your answer but its still not working. It worked at first but when I replaced the file, it gave me the same read-only error again. After I was done with my testing, I wanted to test with the original template so I replaced the file with original template and ran SSIS package. When I opened the file, there was no password. So I ran the code frm PoSh and the same read-only error appeared. I want a code where it SAVES a read-only file. Looks like everything else is just opening the file as not read only but saving with the same name is a problem. Saving with different name works. – Stephanie Apr 18 '17 at 20:52
  • This seems like an overwrite issue. Cannot overwrite after applying password to it – Stephanie Apr 18 '17 at 22:07
  • Have you considered saving to a temp file/another filename with the password, then delete the original and rename the temp file? – Mathias R. Jessen Apr 18 '17 at 23:13
  • @Stephanie Check if you properly terminate the Excel application. It's possible that you don't, and the leftover process still holds to the file, causing your subsequent open requests to not allow writing. – Vesper Apr 19 '17 at 13:57
  • @MathiasR.Jessen exactly what my way around was. I saved it into a different location, delete the original file and moved the file with passw to original file location. I moved on with this method but my developer mind is still curios as to why I can't do it. I feel like there is a way and I might be missing something, – Stephanie Apr 19 '17 at 18:13
  • @Vesper Please explain! How can I check if I properly terminated the Excel app? – Stephanie Apr 19 '17 at 18:14
  • If after your script has run for the first time you check processes and find `excel.exe` in there, you haven't properly terminated your Excel. You would need to call `$xl.quit()` probably with parameters that indicate Excel to not save the data. – Vesper Apr 20 '17 at 03:37
  • Oh, I am calling that in my code. I open the workbook, save as with a password, and then quit it or you are saying that even after I quite, there's a possibility excel hasn't terminated properly? – Stephanie Apr 20 '17 at 15:31