Came across
this useful list whilst reading The Underground - this one is a real timesaver as they have compiled it all in one easy to read place.
$Excel
=
new-object
-comobject
excel.application
$ExcelWordBook
=
$Excel
.Workbooks.Add()
$ExcelWorkSheet
=
$ExcelWordBook
.Worksheets.Add()
$Excel
.Visible =
$true
$ExcelPath
=
'C:\KM_Main.xlsx'
$Excel
=
New-Object
-ComObject
Excel.Application
$Excel
.Visible =
$false
$ExcelWordBook
=
$Excel
.Workbooks.Open(
$ExcelPath
)
$ExcelWorkSheet
=
$Excel
.WorkSheets.item("Sheet 1")
$Excel
.DisplayAlerts =
$false
$Excel
.ScreenUpdating =
$false
$Excel
.Visible =
$false
$Excel
.UserControl =
$false
$Excel
.Interactive =
$false
$Excel
.Save()
$Excel
.Quit()
function
Release-
Ref
(
$ref
) {
(
[System.Runtime.InteropServices.Marshal]
::ReleaseComObject(
[System.__ComObject]
$ref
)
-gt
0)
[System.GC]
::Collect()
[System.GC]
::WaitForPendingFinalizers()
}
Release-
Ref
(
$ExcelWorkSheet
)
Release-
Ref
(
$ExcelWordBook
)
Release-
Ref
(
$Excel
)
$ExcelWorkSheet
=
$Excel
.WorkSheets.item("Sheet 2")
$ExcelWorkSheet
.Cells.Item(1,1).Value2 = "New Value"
$ExcelWorkSheet
.Cells.Item(1,1).Text
[void]
$ExcelWorkSheet
.Cells.Item(1,1).EntireColumn.Delete()
[void]
$ExcelWorkSheet
.Cells.Item(1,1).EntireRow.Delete()
$ExcelWorkSheet
.UsedRange.columns.count
$ExcelWorkSheet
.UsedRange.rows.count
$table
=
$ExcelWorkSheet
.ListObjects | where DisplayName
-EQ
"User_Table"
$table
.Sort.SortFields.clear()
$table
.Sort.SortFields.add(
$table
.Range.Columns.Item(1) )
$table
.Sort.apply()
$tableRange
=
$ExcelWorkSheet
.UsedRange
$tableRange
.ClearFormats()
$listObject
=
$ExcelWorkSheet
.ListObjects.Add(
[Microsoft.Office.Interop.Excel.XlListObjectSourceType]
::xlSrcRange,
$ExcelWorkSheet
.UsedRange,
$null
,
[Microsoft.Office.Interop.Excel.XlYesNoGuess]
::xlYes,
$null
)
$listObject
.Name = "User Table"
$listObject
.TableStyle = "TableStyleLight10"
$ExcelWorkSheet
.UsedRange.Columns.Autofit() |
Out-Null
$ExcelWorkSheet
.columns.item(
$formatcolNum
).NumberFormat = "yyyy/mm/dd"
$ExcelWorkSheet
.Cells.Item(1,1).Font.Bold=
$True
$r
=
$ExcelWorkSheet
.Range("A2")
[void]
$ExcelWorkSheet
.Hyperlinks.Add(
$r
,
$link
)
$ExcelWorkSheet
.Range("D2").AddComment("Autor Name: `rThis is my comment")
$image
= "C:\test\Pictures\Kittys\gotyou.jpg"
$ExcelWorkSheet
.Range("C1").AddComment()
$ExcelWorkSheet
.Range("d3").Comment.Shape.Fill.UserPicture(
$image
)
$ExcelWorkSheet
.Range("D3").Comment.Shape.Left = 100
$ExcelWorkSheet
.Range("D3").Comment.Shape.Top = 100
$ExcelWorkSheet
.Range("D3").Comment.Shape.Width = 100
$ExcelWorkSheet
.Range("D3").Comment.Shape.Height = 100
$comments
=
$ExcelWorkSheet
.comments
foreach
(
$c
in
$comments
) {
$c
.Visible = 1
}
$formula
= "=8*8"
$ExcelWorkSheet
.Cells.Item(1,1).Formula =
$formula