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
Synthesizing my multiple comments as an answer here:
Download EPPlus: https://www.nuget.org/packages/EPPlus/
Read some of the documentation and check their sample code: https://github.com/JanKallman/EPPlus/wiki/Getting-Started
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
User contributions licensed under CC BY-SA 3.0