[system.reflection.assembly]

PowerShell Help
1

Hello

Pls

How works;

cls
$first = [system.reflection.assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
$seconde = [Enum]::getvalues([Microsoft.Office.Interop.Excel.XlBordersIndex]) | select @{n="Name";e={"$_"}},value__

$first
$seconde

I get this;

Type [Microsoft.Office.Interop.Excel.XlBordersIndex] introuvable.
Au caractère C:\Users\admin\AppData\Local\Temp\b7dcbd69-784a-4dd9-8a3b-bc180ba39aba.ps1:3 : 30
+ ... m]::getvalues([Microsoft.Office.Interop.Excel.XlBordersIndex]) | sele ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation : (Microsoft.Offic....XlBordersIndex:TypeName) [], RuntimeException
+ FullyQualifiedErrorId : TypeNotFound

From : PowerShell and Excel: Adding Some Formatting To Your Report | Learn Powershell | Achieve More
and: Excel cell formatting - boarders

I need :

to us :

$xlEdgeRight = 10

or

$xlEdgeLeft = 7

with

$xlColorIndexBlue = 5

I have add range 6

[Void]$range6.Borders($xlEdgeLeft,$xlDash,$xlColorIndexBlue)
Start-Sleep 2

I have replace: BorderAround to Borders

Surcharge introuvable pour « Borders » et le nombre d'arguments : « 3 »
Au caractère C:\Users\admin\AppData\Local\Temp\863a9d67-3617-4cf5-86a9-04922811bff2.ps1:63 : 1
+ [Void]$range6.Borders($xlEdgeLeft,$xlDash,$xlColorIndexBlue)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], GetValueInvocationException
+ FullyQualifiedErrorId : RuntimeException

Thanks for jour Help

Arnold

2

We need more details to understand your issue, please give some more details on what you are trying to do.

3

Hello

Thanks for your reply

This is th action to do:

function Borders-Lundi { $XRob.Cells.Item($Xrow,2).Borders.LineStyle = 1 $XRob.Cells.Item($Xrow,2).Borders.Weight = -4138 $XRob.Cells.Item($Xrow,2).Borders.Color = 3 $XRob.Cells.Item($Xrow,2).Font.Bold = $True $XRob.Cells.Item($Xrow,2).Font.Name = "Times New Roman" $XRob.Cells.Item($Xrow,2).NumberFormat = "# ##0" $XRob.Cells.Item($Xrow,2).Borders($xlEdgeLeft) ?? $XRob.Cells.Item($Xrow,2).Borders($xlEdgeRight) ?? $XRob.Cells.Item($Xrow,2).VerticalAlignment = -4108 $XRob.Cells.Item($Xrow,2).HorizontalAlignment = -4108

$XRob.Cells.Item($Xrow,3).Borders.LineStyle = 1
$XRob.Cells.Item($Xrow,3).Borders.Weight = 1
$XRob.Cells.Item($Xrow,3).Borders.Color = 3
$XRob.Cells.Item($Xrow,3).Borders.EdgeLeft

$XRob.Cells.Item($Xrow,4).Borders.LineStyle = 1
$XRob.Cells.Item($Xrow,4).Borders.Weight = 1
$XRob.Cells.Item($Xrow,4).Borders.ColorIndex = 3
$XRob.Cells.Item($Xrow,4).Borders.EdgeLeft

$XRob.Cells.Item($Xrow,5).Borders.LineStyle = 1
$XRob.Cells.Item($Xrow,5).Borders.Weight = 1
$XRob.Cells.Item($Xrow,5).Borders.ColorIndex = 3
$XRob.Cells.Item($Xrow,5).Borders.EdgeLeft

$XRob.Cells.Item($Xrow,6).Borders.LineStyle = 1
$XRob.Cells.Item($Xrow,6).Borders.Weight = 1
$XRob.Cells.Item($Xrow,6).Borders.ColorIndex = 3
$XRob.Cells.Item($Xrow,6).Borders.EdgeLeft

$XRob.Cells.Item($Xrow,7).Borders.LineStyle = 1
$XRob.Cells.Item($Xrow,7).Borders.Weight = 1
$XRob.Cells.Item($Xrow,7).Borders.ColorIndex = 3
$XRob.Cells.Item($Xrow,7).Borders.EdgeLeft

$XRob.Cells.Item($Xrow,8).Borders.LineStyle = 1
$XRob.Cells.Item($Xrow,8).Borders.Weight = 1
$XRob.Cells.Item($Xrow,8).Borders.ColorIndex = 3
$XRob.Cells.Item($Xrow,8).Borders.EdgeLeft

$XRob.Cells.Item($Xrow,9).Borders.LineStyle = 1
$XRob.Cells.Item($Xrow,9).Borders.Weight = 1
$XRob.Cells.Item($Xrow,9).Borders.ColorIndex = 3
$XRob.Cells.Item($Xrow,9).Borders.EdgeLeft

$XRob.Cells.Item($Xrow,10).Borders.LineStyle = 1
$XRob.Cells.Item($Xrow,10).Borders.Weight = 1
$XRob.Cells.Item($Xrow,10).Borders.Color = 3
$XRob.Cells.Item($Xrow,10).Borders.EdgeLeft
}


Thanks

Arnold

4

Hello

Pls look this stuf. This is better for you.

cls

$excel = new-object -comobject Excel.Application
$workbooks = $excel.Workbooks.Add()
$worksheets = $workbooks.worksheets
$worksheet = $worksheets.Item(1)
$worksheet.Name = “Name of Worksheet”

$excel.Visible = $True

#
$xlEdgeRight = 10
$xlEdgeleft = 7

$worksheet.Cells.Item(1,1) = “This is CELL A1”
$worksheet.Cells.Item(3,4) = “This is CELL D3”

$worksheet.Cells.Item(1,1).Font.Bold = $True
$worksheet.Cells.Item(1,1).Font.Size = 22
$worksheet.Cells.Item(4,3).Font.Italic = $True
$worksheet.Cells.Item(4,3).Font.Size = 14

$range = $excel.Range("C3","C4")
$range.ColumnWidth = 50
$range.Borders.Color = 0
$range.Borders.Weight = 2
$range.Interior.ColorIndex = 37
$range.Font.Bold = $True
$range.HorizontalAlignment = 2

# $excel.Range("C10").Border($xlEdgeleft,$xlEdgeRight)
################################################################
# Échec lors de l'appel de la méthode, car [System.__ComObject] ne contient pas de méthode nommée « Border ».
# Au caractère C:\Users\admin\AppData\Local\Temp\a765e4a0-f698-47b6-9632-a8a92129a194.ps1:31 : 1
# + $excel.Range("C10").Border($xlEdgeleft,$xlEdgeRight)
# + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# + CategoryInfo : InvalidOperation : (Border:String) [], RuntimeException
# + FullyQualifiedErrorId : MethodNotFound
###############################################################

# $excel.Range("C10").Borders($xlEdgeleft,$xlEdgeRight)
################################################################
# Surcharge introuvable pour « Borders » et le nombre d'arguments : « 2 »
# Au caractère C:\Users\admin\AppData\Local\Temp\a765e4a0-f698-47b6-9632-a8a92129a194.ps1:40 : 1
# + $excel.Range("C10").Borders($xlEdgeleft,$xlEdgeRight)
# + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# + CategoryInfo : NotSpecified: (:) [], GetValueInvocationException
# + FullyQualifiedErrorId : RuntimeException
###############################################################

#
# Impossible de définir la propriété BorderAround de la classe Range !!
$range2 = $excel.Range("C6")
$range2.Borders.$xlEdgeleft
$range2.Borders.$xlEdgeRight
# $range2.Borders.LineStyle = 1
# BorderAround ??? i dont't need
$range2.Borders.Weight = -4138
# $range2.Borders.Color = 3

$xlsxPath = 'C:\……...xlsx'

Thanks

Arnold

$range | get-member
$workbooks.SaveAs($xlsxPath)
$excel.quit()
5

Open the XLS
Use the macro recorder and do the steps manually. It will write the VBA code for you, by recording your user actions as you do things.
Once done, stop the recorder and save the generated code.
Copy and paste in to the PowerShell ISE or VSCode editor and edit / convert that VBA code for use in PowerShell.

6

Hi,

thanks for your reply

I have find this way:

I m not luky :( [system.reflection.assembly]
I have use C#

“C#”

This works perfect:

$excel.Range(“C10”).Borders.item($xlEdgeRight).LineStyle = $xlContinuous

$worksheet.Range("c11:c11").Borders.item($xlEdgeleft).LineStyle = $xlContinuous

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeRight).LineStyle = $xlContinuous

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeRight).Weight = $xlMedium

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeRight).Color = 2

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeleft).LineStyle = $xlContinuous

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeleft).Weight = $xlMedium

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeleft).Color = 2

7

Hi,

thanks for your reply

I have find this way:

I m not luky 🙁 [system.reflection.assembly]

I have use C#

“C#”

This works perfect:

$excel.Range(“C10”).Borders.item($xlEdgeRight).LineStyle = $xlContinuous

$worksheet.Range(“c11:c11”).Borders.item($xlEdgeleft).LineStyle = $xlContinuous

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeRight).LineStyle = $xlContinuous

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeRight).Weight = $xlMedium

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeRight).Color = 2

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeleft).LineStyle = $xlContinuous

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeleft).Weight = $xlMedium

$worksheet.Range(“c12:c12”).Borders.item($xlEdgeleft).Color = 2


Many Thanks

Arnold