Long story short... I have a script that uses RESTful API to pull configuration information from an appliance. This script is relatively long, but the length is irrelevant.
The issue I'm having is that when I designed the script I wrote it in PS 5.1, everything worked fine in PS5.1. Now, trying to use the script in PS7 I'm encountering an issue with formatting the output.
I currently use COM objects to export to Excel and not the popular Import-Excel module. I wanted it to be standalone and note require an anything additional. Also, I didn't know that module existed when I wrote the script, and I didn't feel like redesigning the entire script.
Once I initialize the Excel workbook I move the active cell around and insert data as needed. All this works properly. The problem come in when trying to add cell borders.
If you test in PS5.1, you see it works perfectly, and in 7 you get an error about the border weight.
Aside from telling me to redesign the script and use Import-Excel, any help would be appreciated.
Here's a quick sample batch of commands to demonstrate. Obviously, this will require Excel to be installed on your system.
$Excel = new-object -comobject excel.application
$Excel.Visible = $true
$ExcelWorkBook = $Excel.Workbooks.Add()
$WorkSheetIndex = 1
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item($WorkSheetIndex)
$ExcelWorkSheet.Cells.Item(1, 1) = "Test Data"
#Formatting shortcuts
$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$objRange = $ExcelWorkSheet.UsedRange
$objRange.Borders.LineStyle = $lineStyle::xlNone
$objRange.Borders.weight = $borderWeight::xlThin
$ExcelWorkSheet.Cells.Item(1, 1).Font.Size = 16
$ExcelWorkSheet.Cells.Item(1, 1).Font.Bold = $True
[void] $objRange.EntireColumn.Autofit()
PowerShell 7 Error:
OperationStopped: C:\Work\Internal\GIT\Appliance_Administration\Excel_Test.ps1:15
Line |
15 | $objRange.Borders.weight = $borderWeight::xlThin
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Unable to set the Weight property of the Borders class
Expanding on comments, the core problem is that the expression "microsoft.office.interop.excel.xlBorderWeight" -as [type] is evaluating to $null in PowerShell Core.
As a result, $borderWeight is $null and this line:
$objRange.Borders.weight = $borderWeight::xlThin
is equivalent to
$objRange.Borders.weight = $null
By contrast, in Windows PowerShell the expression "microsoft.office.interop.excel.xlBorderWeight" -as [type] returns a reference to the xlBorderWeight type:
PS> $borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
PS> $borderweight
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True XlBorderWeight System.Enum
This is because Windows PowerShell searches the GAC for assemblies, but PowerShell Core doesn't:
In PowerShell 6 and higher, there is no GAC
A quick and dirty fix is to use the constant values for the enums - e.g.
$XlBorderWeight_xlThin = 2;
...
$objRange.Borders.weight = $XlBorderWeight_xlThin
But note you've got the same problem with $lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type], and with any other types defined in the Office Primary Interop Assemblies.
As an aside, it looks like $objRange.Borders.LineStyle = $null doesn't result in an error whereas $objRange.Borders.weight = $null does - \o/.
Alternatively, you can manually load the assemblies from the GAC yourself in PowerShell Core:
Add-Type -Path "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll"
If you want to find the magic value for this path you can use Windows PowerShell 5.1:
PS> ("microsoft.office.interop.excel.xlLineStyle" -as [type]).Assembly.Location
C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll
You'll need to use the hard-coded path in PowerShell Core though.
That's not quite the whole story though because once the types are loaded you'll still get the same error, but for a different reason.
$objRange.Borders.weight is a Variant value - see:
PS> $objRange.Borders | gm
TypeName: System.__ComObject#{00020854-0000-0000-c000-000000000046}
Name MemberType Definition
---- ---------- ----------
... snip ...
Weight Property Variant Weight () {get} {set}
Windows PowerShell seems to be happy to convert a xlBorderWeight enum value to a Variant, but PowerShell Core doesn't so you'll have to do something like this instead to help it along by casting the enum to an int:
$objRange.Borders.weight = [int]$borderWeight::xlThin
Once you've done all of that your code should finally work...