I write the script and the part of it answer for writing received data to the excel file in xlsx.
I need to change size for some special cells in sheet and can’t find how to do it.
I need a help!
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.visible=$true
#Open excel file
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:MyOwnScritpsResultAllertButton.xlsx")
#Copy last sheet, rename it and change position.
$SelectedSourceList = $ExcelWorkBook.Worksheets.Item(3)
$SelectedSourceList.Copy($SelectedSourceList)
$ExcelActiveSheet = $ExcelWorkBook.ActiveSheet.Index
$SelectedSourceList = $ExcelWorkBook.Worksheets.Item($ExcelActiveSheet)
$SelectedSourceList.Name = "$date"
for ($i=0; $i -le ($ExcelWorkBook.Worksheets.Count -1); $i++){
$lastsheet = $ExcelWorkBook.Worksheets($ExcelWorkBook.Worksheets.Count)
$SelectedSourceList.Move([System.Reflection.Missing]::Value, $lastsheet)
}
#Add values in the cells
$ExcelWorkBook.Worksheets.Item(4)
$SelectedSourceList.Cells.Item(1, 1) = 'blablabla1'
$SelectedSourceList.Cells.Item(2, 1) = "blablabla2: $date"
$SelectedSourceList.Cells.Item(3, 1) = "blablabla3: $Responsible"
if($result.DiferenceUsers.Count -ne $null) {
foreach($obj in ($result.DiferenceUsers.Count -1)){
$SelectedSourceList.Cells.Item(27, 6) = $result.DiferenceUsers[4]
}
}