How to open protected and (error out on) un-password-protected files gracefully

Article
2019-06-12

Question

_{Wednesday, June 12, 2019 2:18 PM}

Hello, I'm sure this has been done before, but I am having a problem using powershell to open unprotected Excel files, and fail out on protected files. I am including a Word example too, which works in both cases. Here is how it plays out:

TEST 1: WORD EXAMPLE

#START WITH OPENING THE OBJECT
PS C:\>  $wordapplication = New-Object -comobject word.application
PS C:\>  $wordapplication.visible = $False
#OPEN AN UNPROTECTED DOC
PS C:\>  $document =$wordapplication.documents.open('\\LAPTP\c$\Users\..\unprotected.docx',$null,$true,$null,"BogusPassword")
#RUNS FINE!

Opens the unprotected file - GOOD!

#OPEN A PROTECTED DOC
PS C:\>  $document =$wordapplication.documents.open('\\LAPTP\c$\Users\..\protected.docx',$null,$true,$null,"BogusPassword")

#The password is incorrect. Word cannot open the document.
#(\\LAPTP\...\Document-protected.docx)
#At line:1 char:2
#+  $document =$wordapplication.documents.open('\\LAPTP\c$\Users\ ...
#+  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#    + CategoryInfo          : OperationStopped: (:) [], COMException
#    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Fails on a password-protected doc, which is what I want. I can handle that error with a try/catch. - GOOD!

TEST 2: EXCEL EXAMPLE, WITH BOGUS PASSWORD

#START WITH OPENING THE OBJECT
PS C:\> $excelapplication = New-Object -comobject excel.application
PS C:\> $excelapplication.visible = $False
#OPEN AN UNPROTECTED WORKSHEET
PS C:\> $workbook = $excelapplication.Workbooks.Open('\\LAPTP\c$\Users\..\UNprotected.xlsx',$null,$true,$null,"BogusPassword")

#Unable to get the Open property of the Workbooks class
#At line:1 char:1
#+ $workbook = $excelapplication.Workbooks.Open('\\LAPTP\c$\Users\A ...
#+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#    + CategoryInfo          : OperationStopped: (:) [], COMException
#    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

This way does not open an unprotected doc, and in turn gives an error. BAD!

#OPEN A PROTECTED WORKSHEET
PS C:\> $workbook = $excelapplication.Workbooks.Open('\\LAPTP\c$\Users\..\protected.xlsx',$null,$true,$null,"BogusPassword")

#Unable to get the Open property of the Workbooks class
#At line:1 char:1
#+ $workbook = $excelapplication.Workbooks.Open('\\LAPTP\c$\Users\A ...
#+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#    + CategoryInfo          : OperationStopped: (:) [], COMException
#    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Runs fine - gives the error I would expect. - GOOD!

TEST 3: EXCEL EXAMPLE, WITH NO BOGUS PASSWORD

#OPEN AN UNPROTECTED WORKSHEET
PS C:\> $workbook = $excelapplication.Workbooks.Open('\\LAPTP\c$\Users\..\unprotected.xlsx',$null,$true)
#RUNS FINE

Opens the unprotected doc, - GOOD!

#OPEN A PROTECTED WORKSHEET
PS C:\> $workbook = $excelapplication.Workbooks.Open('\\LAPTP\c$\Users\..\protected.xlsx',$null,$true)

...Opens doc in Excel, waits for the password, and I have to manually kill the process - VERY BAD! Then I get this error...

#Unable to get the Open property of the Workbooks class
#At line:1 char:1
#+ $workbook = $excelapplication.Workbooks.Open('\\LAPTP\c$\Users\ ...
#+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#    + CategoryInfo          : OperationStopped: (:) [], COMException
#    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

If you made it this far, thank you! So my real question is, how can I work with Excel where it would work like how Word works: give me an error on a password-protected doc, yet, open an unprotected doc? Thanks for your thoughts!

All replies (2)

_{Thursday, June 13, 2019 3:20 AM ✅Answered}

Hi,

Thanks for your question.

In MSDN workbooks.open method(excel), the fourth parameter "format" has 6 values to determine the delimiter character for the file.

Value Delimiter
1 Tabs
2 Commas
3 Spaces
4 Semicolons
5 Nothing
6 Custom character (see the Delimiter argument)

In your case, you need to use value 5. So please try to change $null to 5, I test it in my environment and works fine.

 $workbook = $excelapplication.Workbooks.Open('\\LAPTP\c$\Users\..\protected.xlsx',0,$true,5,"BogusPassword")

/en-us/office/vba/api/Excel.Workbooks.Open

Best regards,

Lee

Just do it.

_{Thursday, June 13, 2019 2:50 PM}

Specifying the format did not seem to match the problem, but that indeed is the trick - Thank you!

Share via