2

I'm trying to execute a PowerShell script that operates with Excel File, the script opens the excel and does a SaveAs of the file with a password protection. this works great on a PC with Office, but it doesn't on a server that doesn't have Office installed.

I know that there are some libraries that can be used but I don't know how to use them, here is the code of the PowerShell Script:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $false
$LoadPath = Join-Path -Path $pwd -ChildPath "TEMPLATE4WEEKS.xlsx"    #$pwd is your current working dir
$SavePath = Join-Path -Path $pwd -ChildPath "TEMPLATE4WEEKSprotected.xlsx"
$wb = $excel.Workbooks.Open($LoadPath)


$wb.SaveAs($SavePath,[Type]::Missing,"password")
$excel.Quit()

and I've read something about libraries such as EPPlus (https://archive.codeplex.com/?p=epplus) but I don't know how to use it.

if it worth mentioning, the error I get when trying to execute the PowerShell script on the server is the following:

New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed
due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:1 char:10
+ $excel = New-Object -ComObject Excel.Application
+          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : ResourceUnavailable: (:) [New-Object], COMException
+ FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand

The property 'Visible' cannot be found on this object. Verify that the property exists and can be set.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:2 char:1
+ $excel.Visible = $true
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound

The property 'DisplayAlerts' cannot be found on this object. Verify that         the property exists and can be set.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:3 char:1
+ $excel.DisplayAlerts = $false
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:6 char:1
+ $wb = $excel.Workbooks.Open($LoadPath)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:9 char:1
+ $wb.SaveAs($SavePath,[Type]::Missing,"jacobs4321")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At E:\TEMP\PasswordProtectLAST4WEEKS.ps1:10 char:1
+ $excel.Quit()
+ ~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Baldie47
  • 1,148
  • 5
  • 16
  • 45
  • If you don't know how to use EPPLUS, may I suggest you start with their "Getting Started" documentation: https://github.com/JanKallman/EPPlus/wiki/Getting-Started They have multiple examples and a Frequently Asked Question section as well. – P-L Aug 23 '19 at 12:44
  • Please look at https://stackoverflow.com/questions/35285802/password-protected-excel-download-using-epplus, this is exactly what you're asking for. – P-L Aug 23 '19 at 12:48
  • it seems to be what I need @P-L , but I don't know how to use the library, do I need to put some file somewhere to be able to use it? how do I call then using powershell? I need to create a new copy and password protect that one as I don't want to do modifications to the original file as I use it as template – Baldie47 Aug 23 '19 at 12:53
  • Please read some of the documentation, I have never used that library and I found in less than 30 seconds this information (don't want to be rude, but help yourself a bit! :) From the FAQ: How do I install this library : https://github.com/JanKallman/EPPlus/wiki/FAQ#how-do-i-install-this-library – P-L Aug 23 '19 at 12:56
  • https://www.nuget.org/packages/EPPlus/ :) – P-L Aug 23 '19 at 12:59

1 Answers1

1

Synthesizing my multiple comments as an answer here:

  1. Download EPPlus: https://www.nuget.org/packages/EPPlus/

  2. Read some of the documentation and check their sample code: https://github.com/JanKallman/EPPlus/wiki/Getting-Started

  3. Then open and save your file like this:

.

Add-Type -Path D:\PathOfExile\epplus.4.5.3.2\lib\net40\EPPlus.dll
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage("C:\Metallica\WhereverIMayRoam\hehe.xlsx")
$ExcelPackage.Workbook.Worksheets.Add("Test")
$ExcelPackage.SaveAs("C:\Metallica\WhereverYOUMayRoam\newfile.xlsx", "hohoho") # This sets the password of the xlsx to hohoho
P-L
  • 523
  • 4
  • 14
  • I understand, but I don't think this will work for me, I'm doing it from outside visual studio, with a powershell script. the server in which I have the script does not have visual studio either so I need it to work with just powershell. or am I understanding wrongly and doing it this way could work from the powershell script? (I have read the documentation, but I'm not getting this part) – Baldie47 Aug 23 '19 at 13:41
  • This is powershell btw.... Please try it! I tried it on a test machine without excel and it works. – P-L Aug 23 '19 at 13:45
  • I'm trying it but without luck, I have some errors, please let me know if you need me to update original post with the error text, here is a screenshot: https://i.imgur.com/NJeaZjz.png – Baldie47 Aug 23 '19 at 15:07
  • @gasguirre, The errors are pretty clear, these properties doesn't exists for that object. Remember that EPPlus isn't Microsoft Excel, the object model most likely differs. Use intellisense inside powershell ISE or VSCode to help you with the exposed properties/methods. You simply added the Add-Type declaration to EPPlus.dll. If you only need to save the file with a password, use my code and just change it with your paths and your password. Get ride of [Type]::Missing, open the file using ExcelPackage constructor, no need to adjust visibility and alerts... Start lean then add options. – P-L Aug 23 '19 at 15:42